#32_140728_TUNING_TUNING BASIC

# TUNING BASIC

문제2. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN 인 사원들을 제외하고 출력하고 직업별 토탈월급이 4000 이상인 것만 출력하고 직업별 토탈월급이 높은 것부터 출력하시오

SELECT job, sum(sal) sumsal

FROM EMP

WHERE job <> 'SALESMAN'

GROUP BY job

HAVING SUM(sal) >= 4000

ORDER BY sumsal desc;

l having 절은 그룹함수로 조건을 줄 때만 사용하고 job <> 'SALESMAN'과 같은 일반적인 조건은where 절에 사용한다

- 일반적인 조건을 having 절에 쓰면 성능이 느려진다

l Buffer cache와 Shared pool 을 flush 시키는 명령어

SQL> alter system flush buffer_cache; SQL> alter system flush shared_pool;

  

l 왜 튜닝 확인시 flush 시켜야하는가?

- SQL의 튜닝 전후를 정확하게 비교하기 위해서

ed flush.sql (위의 flush 명령어를 입력) SQL> @flush.sql SQL> 튜닝전 SQL SQL> @flush.sql SQL> 튜닝후 SQL

l select 문의 처리과정

1.     parsing : SQL을 기계여로 바꾸는 과정

- parsing 단계에서 얻어지는 결과물 3가지

: SQL문

: 실행계획

: Parse tree

- 위 3가지 결과물이 shared pool 메모리 영역에 올라간다

- 다음번에 똑 같은 문장이 들어오면 parsing 과정을 생략하기 위해서

2.     execute

• OPTIMIZER

- 옵티마이져가 정확한 실행계획을 만들어내려면 테이블의 통계정보가 필요하다

1)     emp 100 테이블에 대해서 통계정보 수집 명령어

- SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP100');

2)     emp100 테이블의 통계정보 만들어졌는지 확인

SELECT table_name, last_analyzed

FROM user_tables

WHERE table_name='EMP100';

3)     테이블 통계정보가 있는데도 불구하고 옵티마이져가 정확한 실행계획을 생성하지 않는다면 어떻게 해야 되는가?

- 힌트를 통해서 실행계획을 제어하면 된다

- /*+ 힌트 */ : 나의 수행한 쿼리를 수행하는 방법을 지시한다

- 예 : select /*+ full(emp100) */ * from emp100

where ename='scott99';

l 옵티마이저가 실행계획을 생성할 때 영향을 주는 요소

1.     힌트

2.     테이블 통계정보 생성 유무

3.     옵타마이저에게 영향을 주는 오라클 파라미터

n 옵티마이져 파라미터

• CURSOR_SHARING: SIMILAR, EXACT, FORCE • DB_FILE_MULTIBLOCK_READ_COUNT • PGA_AGGREGATE_TARGET • STAR_TRANSFORMATION_ENABLED • RESULT_CACHE_MODE: MANUAL, FORCE • RESULT_CACHE_MAX_SIZE • RESULT_CACHE_MAX_RESULT • RESULT_CACHE_REMOTE_EXPIRATION • OPTIMIZER_INDEX_CACHING • OPTIMIZER_INDEX_COST_ADJ - full table scan 과 index scan 중에 둘중에 하나의 실행계획을 선택하려 했을 때 인덱스를 엑세스할 때 드는 비용을 % 로 표현한 파라미터이다.(default는 100으로 설정) - SQL> show parameter optimizer_index_cost_adj • OPTIMIZER_FEATURES_ENABLED • OPTIMIZER_MODE: ALL_ROWS, FIRST_ROWS, FIRST_ROWS_n • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES • OPTIMIZER_USE_SQL_PLAN_BASELINES • OPTIMIZER_DYNAMIC_SAMPLING • OPTIMIZER_USE_INVISIBLE_INDEXES • OPTIMIZER_USE_PENDING_STATISTICS

n 옵티마이져 속이기 위한 소스

connect scott/tiger create table sales100  as  select * from sh.sales; create table products100 as  select * from sh.products; create table  times100 as  select * from sh.times; connect / as sysdba exec dbms_stats.set_table_stats( -    OWNNAME=>'SCOTT', -    TABNAME=>'EMP', -    NUMROWS=>1,-     numblks=>1); exec dbms_stats.set_table_stats( -    OWNNAME=>'SCOTT', -    TABNAME=>'SALES100', -    NUMROWS=>1,-     numblks=>1); exec dbms_stats.set_table_stats( -    OWNNAME=>'SCOTT', -    TABNAME=>'PRODUCTS100', -    NUMROWS=>1,-     numblks=>1); exec dbms_stats.set_table_stats( -    OWNNAME=>'SCOTT', -    TABNAME=>'TIMES100', -    NUMROWS=>1,-     numblks=>1); exec dbms_stats.set_table_stats( -     OWNNAME=>'SCOTT', -    TABNAME=>'SALGRADE', -    NUMROWS=>1,-     numblks=>1); exec dbms_stats.set_table_stats( -    OWNNAME=>'SCOTT', -    TABNAME=>'BONUS', -    NUMROWS=>1000); alter system set db_file_multiblock_read_count=128; alter system set optimizer_dynamic_sampling=0; alter system set optimizer_index_cost_adj= 10; shutdown  immediate startup

n EMP100 테이블

CREATE table emp100 (empno NUMBER(10), ename VARCHAR2(10)); BEGIN  for i IN 1 .. 9999 LOOP   INSERT INTO EMP100 VALUES(i,'scott99');  END LOOP;  INSERT INTO EMP100 VALUES(10000,'scott1'); END; /

l 옵티마이저를 정상 수행을 못하도록 한 후 아래 쿼리를 조회하면 본래는 full table scan을 해야 하지만 index scan을 하는것을 확인할 수 있다(optimizer_index_cost_adj가10 일때)

SQL> select * from emp100 where ename = 'scott99 ';

l optimizer_index_cost_adj 를 다시 100으로 변경하고 DB를 내렸다가 올린 후 아래의SQL 실행계획을 확인해보면 아래와 같이 full table scan을 하는 것을 확인할 수 있다

SQL> select * from emp100 where ename = 'scott99 ';

SQL> alter system set optimizer_index_cost_adj=100 scope=spfile; SQL> shutdown immediate SQL> startup

• 변형기

- 옵티마이져가 SQL을 성능이 좋은 실행계획이 나오도록 SQL을 변형하는 기능

문제4. 직업이 SALESMAN 또는 ANALYST인 사원들의 이름과 월급과 직업을 출력하시오

SELECT ename, sal, job FROM EMP

WHERE job IN ('SALESMAN','ANALYST');

l 위 그림을 보면 job IN ('SALESMAN','ANALYST'); ~~> job='SALESMAN' OR job='ANALYST';로 변경했다는 것을 확인 할 수 있는데 이것은 변형기에 의해서 바뀐것이다

l 위 예제는 내가 작성한 SQL은 서브쿼리였는데 오라클의 변형기가 수행하는 과정에서 조인으로 변경한 경우

문제5. 서브쿼리로 작성해서 문제를 해결하는데 부서테이블에서 부서번호와 부서위치를 출력하는데 부서 테이블에 있는 부서번호에 대한 것만 출력하시오.

SELECT deptno, loc

FROM DEPT

WHERE deptno in (SELECT deptno FROM emp);

l 위 그림을 보면 변형기에 의해서 hash join 을 자체적으로 수행하는 것을 확인할 수 있다

문제6. 위의 SQL을 조인으로 변경하여 수행하시오

SELECT DISTINCT d.deptno, d.loc

FROM EMP e, DEPT d

WHERE d.deptno = e.deptno;

l subquery를 조인문장으로 바꾼 이후의 실행계획 확인

n 쿼리 변형기에서 내가 수행한 SQL을 그대로 수행하게 하고 쿼리를 변경하지 못하게 하는 힌트

- /*+ no_query_transformation */

SQL> SELECT /*+ no_query_transformation */ deptno, loc FROM DEPT WHERE deptno in (SELECT deptno FROM emp);