본문 바로가기

빅데이터과정/SQL TUNING

#39_140806_TUNING_SQL 재작성 # SQL 재작성 문제104. 아래의 SQL을 재작성하시오(아래처럼 조인을 안하고도 똑 같은 결과가 나오도록 하시오) # 튜닝 전select decode(no,1,deptno,2, null), sum(sal) from emp e, (select rownum no from dual connect by level <=2 ) d group by decode(no,1,deptno,2, null) order by decode(no,1,deptno,2, null);# 튜닝 후SELECT deptno, SUM(sal)from EMPGROUP BY ROLLUP(deptno); 문제105. 아래의 SQL을 재작성 하시오(조인을 안하고도 똑 같은 결과가 나오도록 하시오) # 튜닝 전select empno, ename,sa.. 더보기
#39_140806_TUNING_TUNING ADVISOR # TUNING ADVISOR l SQL 프로파일 : SQL은 그대로 두고 실행계획만 최적의 실행계획으로 변경하는 기능 문제100. 튜닝전 SQL을 SQL tuning advisor 에게 맡겼을 때 결과 select /*+ optimizer_features_enable('10.2.0.3') merge(v) */ ename, sal, substr( totalsal,1,10) maxsal, substr(totalsal,11,10) minsal, substr(totalsal,21,10) sumsal from (SELECT ename, sal, (SELECT rpad(MAX(sal),10,' ') || rpad(MIN(sal),10,' ') || rpad(SUM(sal),10,' ') FROM EMP ) as .. 더보기
#37_140805_TUNING_VIEW JOIN # VIEW JOIN 문제93. 이름과 월급과 부서위치를 출력하는 view를 생성하고 이 view를 salgrade 테이블을 조인해서 이름과 월급과 부서위치와 급여등급(grade)를 출력하시오 CREATE VIEW emp5000ASSELECT e.ename, e.sal, d.locFROM EMP e, DEPT dWHERE e.deptno=d.deptno; SELECT /*+ no_merge(v) */ e.ename, e.sal, e.loc, s.gradeFROM EMP5000 e, SALGRADE sWHERE sal BETWEEN losal AND hisal;- view를 해체하지 않도록 no_merge 힌트 사용- 실행계획에 view가 나오지 않는다면 view를 해체하지 않는 것이다- 그러나 아래와 같.. 더보기
#37_140805_TUNING_옵티마이저 힌트 총정리 # 옵티마이저 힌트 총정리 l 인덱스 관련 힌트 관련 힌트인덱스 방법indexindex range scanindexindex unique scanindex_fsindex full scanindex_ffsindex fast full scanand_equalindex merge scanindex_combineindex bitmap merge scanindex_descindex descending scanindex_ascindex ascending scanindex_ssindex skip scanno_indexindex를 사용 못하게 l 조인에 관련된 힌트 - 조인 순서 관련 힌트조인 순서leadinglaeding 힌트 안에 테이블 순서orderedfrom 절의 테이블 순서swap_join_inputshash.. 더보기
#37_140805_TUNING_바인드 변수와 실행계획 # 바인드 변수와 실행계획 1. 바인드 변수를 사용할 때 주의할 사항 - SQL> select empno, ename, sal, from emp where empno = 7788;- 위 쿼리 수행 단계1) parsing > SQL > 기계어: 실행계획, SQL, Parse tree 3가지가 shared pool 캐쉬가 된다: 다음번에 똑 같은 문장이 들어오면 parsing 과정을 생략하기 위해서 수행2) execute3) fetch 2. 바인드 변수 사용 SQL> select empno, ename, sal from emp where empno = 7788;SQL> select empno, ename, sal from emp where empno = 7902- 위의 2문장은 다른 문장으로 인식한다SQL>.. 더보기
#37_140805_TUNING_실행계획과 통계정보 # 실행계획과 통계정보 l 10g 버전부터는 매일 밤 10시에 자동으로 DB의 모든 테이블에 대해서 통계정보가 자동으로 수집- SQL> select cline_name, status from dba_autotask_clinet’- auto optimizer stats collection : 테이블 통계정보 확인- auto space advisor : 빈공간을 확보하는 작업- sql tuning advisor : 자동으로 SQL 튜닝 l 현업에서 테이블통계정보를 안하는 이유- 대용량 테이블은 수집시간이 오래걸리기 때문에- 새로 수집한 통계정보로 인해서 실행계획이 달라져서 기존에 잘돌던 SQL이 느려짐을 우려해서 n 통계정보 잠그는 명령어 DBMS_STATS.LOCK_TABLE_STATS('OE','ORDE.. 더보기
#37_140805_TUNING_HISTOGRAM # HISTOGRAM l 히스토그램이란? - 데이터의 분포도의 편차가 심한 emp100 테이블의 ename의 경우에는 히스토그램을 생성해야 한다. 그래야 실행계획이 정확해진다- 통계정보 수집 : sampling 할 data를 담을 상자(버켓)를 1개- 히스토그램을 수집 : sampling 할 data를 담을 상자(버켓)를 여러 개- 중복된 데이터가 많은 경우를 표준편차가 크다고 표현하는데 이런 경우에는 버켓이 여러 개가 필요해서 히스토그램을 수집해야 한다- 반면에 emp 테이블 같은 경우는 표준편차가 작다고 표현하는데 이런 경우는 버켓이 한 개로 충분하여 통계정보를 수집해야 한다 l 히스토그램 테스트 1. exec dbms_stats.delete_table_stats('SCOTT','EMP100'); 2.. 더보기
#37_140805_TUNING_DYNAMIC SAMPLING # DYNAMIC SAMPLING 옵티마이저 동적 표본 추출 작업 • 샘플링은 컴파일 시 수행됩니다. • 동적 표본 추출 기능을 사용하여 질의를 수행하는 경우 다음과같은 결과가 나타납니다.– 데이터 표본 추출을 위해 반복적인 SQL 문이 실행됩니다.– 샘플링된 블록 수는 OPTIMIZER_DYNAMIC_SAMPLING 초기화파라미터에 따라 다릅니다. • 동적 샘플링 동안 선택성을 확인하기 위해 표본에 술어가적용됩니다. • 다음 경우에 동적 표본 추출을 사용합니다.– 표본 추출 시간이 실행 시간의 극히 일부에 불과한 경우– 질의가 여러 번 실행되는 경우– 더 좋은 계획을 찾을 수 있다고 판단되는 경우 SELECT * FROM EMP100 WHERE ename='scott1'; SELECT * FROM EM.. 더보기
#37_140805_TUNING_MODEL 절 # MODEL 절 select empno, ename,year2,salfrom emp700modelpartition by (empno, ename)- 기준이 되는 컬럼dimension by (year2)- 예상 연도에 대한 컬럼measures (sal)- 새로 계산이 되는 컬럼 rules ( sal[1983] = sal[1982] * (sal[1982]/sal[1981]) )order by year2,ename; 점심문제62. SQL model 절을 이용해서(게스글 299번) OECD년간 근로시간의 2014년도 데이터를 예상하시오 SELECT * FROM ( SELECT nation, SUBSTR(year,6) AS year, CNT FROM LAB_TIME unpivot (cnt FOR year IN .. 더보기
#36_140804_TUNING_STAR JOIN # STAR JOIN - 노란색 테이블은 아주 큰 대용량 테이블- 파란색 테이블이 아주 작은 테이블- 파란색 테이블끼리 연결고리가 없어서 연결고리 없이 조인 한다- 파란색이 크기가 작은 테이블들인데 sales의 거대한 테이블과 hash나 nl 각각 조인을 하려면 시간을 많이 소비하게 된다- 그렇기 때문에 크기가 작은 테이블들을 먼저 조인하고 맨 나중에 가장 큰 테이블인 sales 를 조인하면 속도를 높일 수 있다 # dept ---- emp ---- salgradeSELECT e.ename, d.loc, e.sal, s.gradeFROM EMP e, DEPT d, SALGRADE sWHERE e.deptno = d.deptnoAND e.sal BETWEEN s.losal AND s. hisal; SELE.. 더보기
#36_140804_TUNING_UNION TUINING # UNION TUINING 문제76. 직업, 직업별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈월급도 출력하시오 # 튜닝 전SELECT job, SUM(sal)FROM EMPGROUP BY jobUNION ALLSELECT NULL AS job, SUM(sal)FROM EMP;db block gets 0consistent gets 53physical reads 0# 튜닝 후SELECT job, SUM(sal)FROM EMPGROUP BY ROLLUP(job); db block gets 0consistent gets 46physical reads 0 문제77. 부서번호, 부서번호별 인원수를 출력하는데 맨 아래쪽에 전체 인원수도 출력하시오 # 튜닝 전SELECT deptno, COUNT(*)FROM EMP.. 더보기
#35_140801_TUNING_JOIN TUNING2 # JOIN TUNING l HASH JOIN RIGHT SEMI- hash join을 하는데 오른쪽에 있는 쿼리부터 조인시키겠다는 의미이다 문제. 아래의 실행계획을 hash join right semi로 만드시오 SELECT /*+ qb_name(main) */ enameFROM EMPWHERE empno IN (SELECT /*+ qb_name(sub) hash_sj swap_join_inputs(emp@sub) */ mgr FROM emp); - hash join은 main과 sub의 순서를 바꾸기 위해서는 swap_join_inputs 문장을 써줘야 한다- 그래서 괄호안에 테이블 이름을 쓰면 되지만 위 같은 경우에는 괄호안에 emp@ 이후에 query block name의 이름을 써주면 된다- 만.. 더보기
#34_140731_TUNING_JOIN TUNING # JOIN TUNING nested loop joinhash joinsort merge join실행계획 위쪽 테이블driving tablehash tabledriving table실행계획 아래쪽 테이블driven tableprobe tabledriven table l join tuning 할때 중요한 점 1. 조인 순서- ordered- leading 2. 조인 방법- use_nl- use_hash- use_merge 3. 주의사항- 대용량 테이블을 조인할 떄는 hash join 이나 sort merge join 이 유리하다- 그래서 hash join 이나 sort merge join 은 주로 index scan 보다는 full table scan이 더 유리하다- 적은양의 테이블을 join 할 떄는 n.. 더보기
#34_140730_TUNING_OPERATOR # OPERATOR INLIST ITERATOR l in 연산자를 사용했을 때의 SQL 성능을 높이는 방법 - inlist를 이용해서 쿼리를 작성할 경우에 index scan이 나눠지지 않고 잘 수행되면 튜닝할 필요가 없다 - 그러나 아래의 그림처럼 index가 나눠지는 경우에는 concat 힌트를 이용해서 하나로 합쳐줄 필요가 있다/*+ QB_NAME(main) use_concat(@main 8) */ 문제40. 부서번호에 인덱스를 생성하고 부서번호가 10번 20번인 사원들의 이름과 월급과 직업과 부서번호를 출력하시오 CREATE INDEX emp_deptno ON EMP(deptno); # 튜닝 전# 현재 방식SELECT ename, sal, jobFROM EMPWHERE deptno IN (10,2.. 더보기
#34_140730_TUNING_SORTING OPERATION # SORTING OPERATION sort를 일으키는 SQL 1. order by- 인덱스를 이용해서 sort를 회피 2. 그룹함수 : max, min, avg, sum- 인덱스를 이용해서 sort를 피함 3. 집합 연산자 : minus, union, intersect- union all 을 사용 또는 SQL 변경를 사용해서 sort를 피함 4. 조인방법 : ort merge join- 다른 조인방법으로 유도하여 sort를 피함 5. 인덱스 생성시 : create index ….- 신속하게 인덱스를 생성되게 하여 sort를 피함 문제31. 사원이름과 월급을 출력하는데 월급이 낮은 사원부터 높은 사원 순으로 출력하시오 # 튜닝 전SELECT ename, salFROM EMPORDER BY sal asc;.. 더보기
#34_140730_TUNING_INVISIBLE INDEX # INVISIBLE INDEX n 수행 예제SQL> demobld.sqlSQL> create index emp_sal on emp(sal);SQL> alter index emp_sal invisible;SQL> select index_name,visibility from user_indexes;INDEX_NAME VISIBILIT------------------------------ ---------EMP_SAL VISIBLESALES500_RN VISIBLESALES500_CHANNEL_PROFIT VISIBLESALES500_CHANNEL_ID VISIBLESALES500_AMOUNT_SOLD VISIBLE………………………………………………………- invisible 유무 확인SQL> select /*.. 더보기
#33_140729_TUNING_INDEX MONITORING # INDEX MONITORING l 인덱스가 많아지면 테이블에 insert 속도가 떨어진다- insert 하는 순간 각각의 index를 다시 구성해야 하기 때문에 느려진다 l 인덱스가 많아지면 공간도 많이 차지한다 l 인덱스 사용 여부 모니터링 하는 방법 SQL> @demobld. sql SQL> create index emp_sal on emp(sal); SQL > alter index emp_sal monitoring usage; SQL> select index_name, used, monitoringfrom v$object_usage; SQL> select /*+ index(emp emp_sal) */ ename, sal, job from emp where sal =3000; SQL> select.. 더보기
#33_140729_TUNING_INDEX SCAN # INDEX SCAN 점심문제14. 이름, 월급, 부서번호, 자기가 속한 부서번호별 평균월급을 출력하시오(아래의SQL을 각각 출력해서 튜닝전후가 비교가 되도록 레포팅) 분석함수 : listagg, max(sal) over () …. 1. 분석함수 이용2. 분석함수 이용하지 않은 것 # 튜닝 전SELECT ename, sal, deptno, AVG(sal) OVER(PARTITION BY deptno)FROM EMP;db block gets 0consistent gets 56physical reads 0# 튜닝 후SELECT e.ename, e.sal, e.deptno, f.averFROM EMP e, (SELECT deptno, AVG(sal) aver FROM EMP GROUP BY deptno) f.. 더보기
#32_140728_TUNING_옵티마이져 연산자 # 옵티마이져 연산자 l data를 엑세스 하는 방법 FULL TABLE SCAN l full table scan (힌트 : /*+ full(테이블명) */ ) 1. high water mark까지 스캔하는 방법 2. full table scan이 유리한 이유: 테이블에서 엑세스 하고자 하는 데이터가 많을 때 3. full table scan을 할 수 밖에 없는 경우1) 인덱스가 없을 때2) 테이블의 통계정보를 수집할 때3) 인덱스를 생성할 때4) full 힌트를 생성할 때 l full table scan을 빠르게 하는 방법 2가지 1. full table scan에 관련된 오라클 파라미터의 값을 조정한다- db_file_multiblock_read_count : 이 파라미터는 full table sca.. 더보기
#32_140728_TUNING_TUNING BASIC # TUNING BASIC 문제2. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN 인 사원들을 제외하고 출력하고 직업별 토탈월급이 4000 이상인 것만 출력하고 직업별 토탈월급이 높은 것부터 출력하시오 SELECT job, sum(sal) sumsalFROM EMPWHERE job 'SALESMAN'GROUP BY jobHAVING SUM(sal) >= 4000ORDER BY sumsal desc; l having 절은 그룹함수로 조건을 줄 때만 사용하고 job 'SALESMAN'과 같은 일반적인 조건은where 절에 사용한다- 일반적인 조건을 having 절에 쓰면 성능이 느려진다 l Buffer cache와 Shared pool 을 flush 시키는 명령어SQL> alter system .. 더보기

티스토리툴바