[교육] SQL초보에서실전전문가까지 ORACLE - 11

• 옵티마이저의 원리와 이해

SQL> show parameter opt

.....

optimizer_mode             integer             ALL_ROW

alter session set optimizer_mode=rule;

set autotrace on;

select * from emp;

Statistics

----------------------------------------------------------

          0  recursive calls         두 번 이상 실행하면 메모리에 데이터가 있어서 0 이 될 수 있다

          0  db block gets

         10  consistent gets        가져온 Block의 수

          0  physical reads

          0  redo size

2052  bytes sent via SQL*Net to client

      407  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         31  rows processed

• 통계정보 생성 혹은 갱신하기

SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS

FROM USER_TABLES

WHERE TABLE_NAME='TEMP';

TABLE_NAME                     LAST_ANALYZED   NUM_ROWS

------------------------------ ------------- ----------

TEMP                                                   

1 row selected.

--// 통계정보 만드는 명령어

ANALYZE TABLE TEMP COMPUTE STATISTICS;

Table analyzed.

SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS

FROM USER_TABLES

WHERE TABLE_NAME='TEMP';

TABLE_NAME                     LAST_ANALYZED   NUM_ROWS

------------------------------ ------------- ----------

TEMP                           30-8-12                0

1 row selected.

--// 통계정보 삭제하는 명령어

ANALYZE TABLE TEMP DELETE STATISTICS;  

SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS

FROM USER_TABLES

WHERE TABLE_NAME='TEMP';

TABLE_NAME                     LAST_ANALYZED   NUM_ROWS

------------------------------ ------------- ----------

TEMP                                                   

1 row selected.

통계정보 갱신은 3개월 단위로 하는 것을 권고 하나 실제 필드에서는 거의 따르지 않는다고 함.

• CBO의  작동원리

1. 실행계획 중 cost가 가장 적은 실행계획을 선택
2. 컬럼별 데이터 분포에 대한 통계정보(=히스토그램) 사용
3. 복잡한 관계표현에서 때로 잘못된 실행계획을 수립할 수도 있는데 이런 경우 힌트를 사용하여 수정하도록 함
4. 오브젝트에 대한 통계정보를 기준으로 실행계획을 작성하므로 통계정보가 제대로 생성되어 있지 않은경우, 응용프로그램의 성능에 악영향을 미치게 되므로 주의
5. Cost engine은 I/O cost, Network cost, CPU cost 등도 고려하도록 설계되어 있음
6. ※ 통계정보 : 테이블의 데이터 건수, 평균 실이, 컬럼 별 distinct 값의 수, 인덱스 leaf node의 depth 등이 저장

• 쿼리의 이행 (자동변환)

NAME LIKE 'KTMIN' -> NAME = 'KTMIN' (가변길이일 경우만)

• 뷰의 병합 (자동으로 효율적인 WHERE 절을 만들어준다)

/*+MERGE*/ /*+NO_MERGE*/ 힌트를 사용해서 병합을 할 것인지 안할 것인지  컨트롤 할 수 있다. 

• SQL SERVER에서 통계정보 보기

DBCC SHOW_STATISTICS(테이블명, INDEX명);

SELECT STATS_DATE(테이블명, INDEX명);