Leeyebin의 블로그
[2017.03.27~2017.03.30]데이터베이스 분석 및 개선을 통한 성능 확보 교육 2/4 본문
4.4.2 동일한 문장이 아닌 경우
(대,소문자 등) 동일한 SQL문을 작성하지 못한다면 각 문장은 PARSING을 각각 수행하기 때문에 불필요한 메모리 공간의 낭비 및 성능저하 현상이 발생될 수 있음.
1)CURSOR_SHARING = EXACT
2개의 문장에 사용된 SQL문이 모두 동일한 조건, WHERE절 조건에 정의된 상수가 동일해야 만 파싱 정보를 공유함
SQL> ALTER SYSTEM SET CURSOR_SHARING=EXACT; 시스템이 변경되었습니다. SQL> SELECT * FROM DEPT WHERE DEPTNO = 30; DEPTNO DNAME LOC ---------- -------------- -------------- 30 SALES CHICAGO SQL> SELECT * FROM DEPT WHERE DEPTNO = 40; DEPTNO DNAME LOC ---------- -------------- -------------- 40 OPERATIONS BOSTON //실행된 2개의 문장에 대한 파싱 정보가 각각 생성된다.
2)CURSOR_SHARING = SIMILAR
2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유함. 단, WHERE 조건에 정의된 바인드 변수가 다른 값을 가지고 있더라도 공유할 수 있음.
SQL> ALTER SYSTEM SET CURSOR_SHARING = SIMILAR; 시스템이 변경되었습니다. SQL> SELECT * FROM DEPT WHERE DEPTNO = 10; DEPTNO DNAME LOC ---------- -------------- -------------- 10 ACCOUNTING NEW YORK SQL> SELECT * FROM DEPT WHERE DEPTNO = 20; DEPTNO DNAME LOC ---------- -------------- -------------- 20 RESEARCH DALLAS SQL> SELECT * FROM DEPT WHERE DEPTNO = 50; 선택된 레코드가 없습니다. 실행된 3개의 문장 중에 10번 부서에 대한 검색조건은 동일한 문장이 파싱된 적이 없기 때문에 최초 파싱했다는 것을 확인할 수 있습니다. 하지만, 20번 부서 조건과 50번 부서 조건의 파싱 결과는 PARSE CALL을 통해 재 사용했다는 것을 알 수 있다.
3)CURSOR_SHARING = FORCE
2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유할 수 있다. 단, WHERE 조건에 정의된 상수가 다르더라도 커서를 공유할 수 있다.
SQL> ALTER SYSTEM SET CURSOR_SHARING = FORCE; 시스템이 변경되었습니다. SQL> SELECT * FROM DEPT WHERE DEPTNO = 60; 선택된 레코드가 없습니다. PARSE CALL 컬럼을 보면 이전에 실행되었던 파싱 결과를 재사용했다는 것을 알 수 있다.
4.4.3 동일한 SQL문의 작성지침
-SQL 표준화하는 이유
- 약속된 프로그래밍 규칙을 통해 향후 유지 보수적인 측면을 고려함.
- 성능 문제(프로그래밍이 어려워짐, 불필요한 메모리와 디스크 공간의 낭비를 초래할 수 있음)
모를 땐 http://docs.oracle.com/en/ 에서 찾기
5. 옵티마이저
5.1 옵티마이저의 개념
프로세스 그림
-사용자가 실행한 SQL문을 분석하여 가장 빠르게 실행될 수 있는 실행계획을 결정해 주는 알고리즘.
-공식기반 옵티마이저(05.v6) -> 비용기반 옵티마이저(v7~12g)
-옵티마이저가 무조건 최상의 선택이 아닐 수도 있다.
Optimal Plan - ex)a,b,c코스
Execution Plan - Optimal Plan에서 최종 결정된 실행 계획
5.2 옵티마이저의 종류
1)공식기반 - 15단계의 실행방법 중 어떤 방법을 선택하여 실행하느냐에 따라 성능이 결정되는 방법, 개발자 자신이 실행하는 SQL문의 문장에 의해 성능이 좌우되는 방법.(공식이 정해져있구나!)
2)비용기반 - 실행될 수 있는 다양한 방법들이 원가(Cost)를 계산하여 그 중에 가장 좋은 성능을 보장할 수 있는 방법을 선택함
5.3 공식기반 옵티마이저
이미지 출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148208&boardConfigUid=9&categoryUid=216&boardIdx=136&boardStep=1
DB Data 검색방법 3가지
Cluster-Scan(2단계~7단계)
Index-Scan(8단계~14단계)
Full-Table-Scan(15단계 최하위 검색방법-가장 떨어지는 검색방법, 성능기대X)
1단계는 ROWID에 의한 단일행 실행
P156
C:\Users\user>SQLPLUS SCOTT/TIGER
SQL*Plus: Release 11.2.0.1.0 Production on 화 3월 28 11:14:49 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;
세션이 변경되었습니다.
SQL> SET AUTOTRACE TRACE
SQL> CREATE INDEX I_EMP_DEPTNO ON BIG_EMP(DEPTNO);
인덱스가 생성되었습니다.
SQL> CREATE UNIQUE INDEX I_EMP_EMPNO ON BIG_EMP(EMPNO);
인덱스가 생성되었습니다.
SQL> SELECT ENAME
2 FROM BIG_EMP
3 WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200
4 ORDER BY ENAME;
30 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP |
|* 3 | INDEX RANGE SCAN | I_EMP_DEPTNO |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPNO"<=200 AND "EMPNO">=100)
3 - access("DEPTNO"=20)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
211 consistent gets
15 physical reads
0 redo size
766 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
30 rows processed
P158
SQL> CREATE INDEX I_BIG_EMP_DEPTNO ON BIG_EMP(DEPTNO);
인덱스가 생성되었습니다.
SQL> CREATE UNIQUE INDEX I_BIG_EMP_EMPNO ON BIG_EMP(EMPNO);
인덱스가 생성되었습니다.
SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;
세션이 변경되었습니다.
SQL> SET AUTOTRACE TRACE;
SQL> SELECT ENAME FROM BIG_EMP
2 WHERE DEPTNO = 20
3 AND EMPNO BETWEEN 100 AND 200
4 ORDER BY ENAME;
30 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP |
|* 3 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPNO"<=200 AND "EMPNO">=100)
3 - access("DEPTNO"=20)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
211 consistent gets
15 physical reads
0 redo size
766 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> SELECT ENAME FROM BIG_EMP
2 WHERE DEPTNO >= 20 AND DEPTNO <= 30
3 AND EMPNO = 100
4 ORDER BY ENAME;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP |
|* 2 | INDEX UNIQUE SCAN | I_BIG_EMP_EMPNO |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"<=30 AND "DEPTNO">=20)
2 - access("EMPNO"=100)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
4 physical reads
0 redo size
298 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
P160
SQL> CREATE INDEX I_BIG_EMP_EMPNO ON BIG_EMP(EMPNO);
인덱스가 생성되었습니다.
SQL> SELECT ENAME FROM BIG_EMP
2 WHERE DEPTNO = 20
3 AND EMPNO = 100
4 ORDER BY ENAME;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP |
| 3 | AND-EQUAL | |
|* 4 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO |
|* 5 | INDEX RANGE SCAN | I_BIG_EMP_EMPNO |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=20)
5 - access("EMPNO"=100)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
21 consistent gets
4 physical reads
0 redo size
298 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
순위가 같아(9순위) 인덱스 모두 사용(9순위만 해당)
P160 밑
SQL> CREATE INDEX I_BIG_EMP_EMPNO ON BIG_EMP(EMPNO);
인덱스가 생성되었습니다.
SQL> CREATE INDEX I_BIG_EMP_DEPTNO ON BIG_EMP(DEPTNO);
인덱스가 생성되었습니다.
SQL> SELECT ENAME FROM BIG_EMP
2 WHERE DEPTNO < 20 AND EMPNO >100
3 ORDER BY ENAME;
6351 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP |
|* 3 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPNO">100)
3 - access("DEPTNO"<20)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
1073 consistent gets
19 physical reads
0 redo size
85997 bytes sent via SQL*Net to client
5068 bytes received via SQL*Net from client
425 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6351 rows processed
마지막에 만든 인덱스를 사용한다. why? 이전에 만든 것보다 최근에 만든 것이 재검색을 할 가능성이 높기 때문에
P161
SQL> CREATE INDEX I_EMP_ENAME ON BIG_EMP (ENAME);
인덱스가 생성되었습니다.
SQL> SELECT * FROM BIG_EMP
2 WHERE ENAME = 'MARTIN' OR SUBSTR(ENAME,1,1) = 'F';
4116 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| BIG_EMP |
-------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='MARTIN' OR SUBSTR("ENAME",1,1)='F')
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
472 consistent gets
0 physical reads
0 redo size
189966 bytes sent via SQL*Net to client
3429 bytes received via SQL*Net from client
276 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4116 rows processed
5.3.4 AND와 OR 조건에서 우선 순위를 결정하는 원리(Rule Bse Optimizer에서)
-AND 조건의 경우에는 WHERE절에서 가까운 조건부터 검색함
-OR 조건의 경우에는 WHERE절에서 가까운 오른쪽(먼) 조건부터 검색
-AND 조건과 OR조건이 함께 사용되는 경우에는 AND와 OR원리가 함께 적용됨
--OR절은 하나의 문장이 아닌 여러 문장 분해후 결합 P163 - AND SQL> CREATE INDEX I_EMP_DEPTNO ON EMP(DEPTNO); 인덱스가 생성되었습니다. SQL> CREATE INDEX I_EMP_JOB ON EMP(JOB); 인덱스가 생성되었습니다. SQL> SELECT * 2 FROM EMP 3 WHERE JOB = 'CLERK' AND DEPTNO = 10; Execution Plan ---------------------------------------------------------- ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | | 2 | AND-EQUAL | | |* 3 | INDEX RANGE SCAN | I_EMP_JOB | |* 4 | INDEX RANGE SCAN | I_EMP_DEPTNO | ---------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("JOB"='CLERK') 4 - access("DEPTNO"=10) Note ----- - 'PLAN_TABLE' is old version - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 48 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 879 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> SELECT *
2 FROM EMP
3 WHERE DEPTNO = 10 AND JOB = 'CLERK';
Execution Plan
----------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | AND-EQUAL | |
|* 3 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 4 | INDEX RANGE SCAN | I_EMP_JOB |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
4 - access("JOB"='CLERK')
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
879 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT EMPNO, ENAME, DEPTNO
2 FROM EMP
3 WHERE DEPTNO = 30 OR DEPTNO = 20 OR DEPTNO = 10;
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 3 | INDEX RANGE SCAN | I_EMP_DEPTNO |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 5 | INDEX RANGE SCAN | I_EMP_DEPTNO |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 7 | INDEX RANGE SCAN | I_EMP_DEPTNO |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=20)
7 - access("DEPTNO"=30)
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
794 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
P166
SQL> SELECT *
2 FROM EMP
3 WHERE (DEPTNO = 10 OR DEPTNO = 20 OR DEPTNO = 30) AND JOB = 'CLERK';
Execution Plan
----------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP |
| 3 | AND-EQUAL | |
|* 4 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 5 | INDEX RANGE SCAN | I_EMP_JOB |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP |
| 7 | AND-EQUAL | |
|* 8 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 9 | INDEX RANGE SCAN | I_EMP_JOB |
| 10 | TABLE ACCESS BY INDEX ROWID| EMP |
| 11 | AND-EQUAL | |
|* 12 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 13 | INDEX RANGE SCAN | I_EMP_JOB |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30)
5 - access("JOB"='CLERK')
8 - access("DEPTNO"=20)
9 - access("JOB"='CLERK')
12 - access("DEPTNO"=10)
13 - access("JOB"='CLERK')
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
1008 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
궁금증 괄호 지우기
SQL> SELECT *
2 FROM EMP
3 WHERE DEPTNO = 10 OR DEPTNO = 20 OR DEPTNO = 30 AND JOB = 'CLERK';
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP |
| 3 | AND-EQUAL | |
|* 4 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 5 | INDEX RANGE SCAN | I_EMP_JOB |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 7 | INDEX RANGE SCAN | I_EMP_DEPTNO |
|* 8 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 9 | INDEX RANGE SCAN | I_EMP_DEPTNO |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30)
5 - access("JOB"='CLERK')
6 - filter(LNNVL("DEPTNO"=30) OR LNNVL("JOB"='CLERK'))
7 - access("DEPTNO"=20)
8 - filter(LNNVL("DEPTNO"=30) OR LNNVL("JOB"='CLERK'))
9 - access("DEPTNO"=10)
filter(LNNVL("DEPTNO"=20))
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1209 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
5.4 비용기반 옵티마이저
-통계정보부터 수집해야한다.
-통계정보를 반드시 만들어야 가장 좋은 성능을 기대할 수 있다.
-통계정보가 없다면 그 실행계획 자체는 신뢰할 수 없다.(반드시 필요)
-실행계획이 언제든지 바뀔 수 있다.
통계정보 수집에 주의 사항
-ORACLE 9i(manual / 하지만 과거에는 DBMS_STATS패키지와 DBMS_JOB으로 테이블,날짜,시간으로 자동 스케쥴링 할 수 있었다.) -> ORACLE 10G부터(AUTO:자동화라고 해도 통계정보 수집에 신경써야한다. / 대용량 DATA, 읽고 쓰기가 잦은 DATA들,성능을 기대해야할 때 통계 수집에 신경써야함)
-통계정보 수집은 상당히 많은 자원을 잡아먹기 때문에 일과시간(peak_time)에는 지양(파싱결과들 싹다 invalidation 된다)
-데이터의 성격에 따라 빈도수 조절하도록 함(P175표 참고)
문법
ANALYZE TABLE [테이블명] COMPUTE STATISTICS;
INDEX [인덱스명] ESTIMATE STATISTICS
CLUSTER [클러스터명] DELETE STATISTICS
p172 통계정보가 수정되어 있는지 여부 확인 SQL> SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES; TABLE_NAME LAST_ANA ------------------------------ -------- PLAN_TABLE S_ZIPCODE 17/03/27 S_SUJU_DTL 17/03/27 S_SUJU 17/03/27 S_SPINOUT 17/03/27 S_SANGPUM 17/03/27 S_PROMOTION 17/03/27 S_MAECHE 17/03/27 S_JAEGO 17/03/27 S_GOGAEK 17/03/27 S_CHULHAJISI 17/03/27 TABLE_NAME LAST_ANA ------------------------------ -------- S_CHANGGO 17/03/27 LARGE_EMP LARGE_DEPT LARGE_ACCOUNT EMP DEPT BIG_EMP 17/03/27 BIG_DEPT ACCOUNT 20 개의 행이 선택되었습니다.
--테이블 통계 정보 SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> ANALYZE TABLE BIG_DEPT COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> SELECT TABLE_NAME, BLOCKS, NUM_ROWS, AVG_ROW_LEN 2 FROM USER_TABLES 3 WHERE TABLE_NAME = 'BIG_EMP' OR TABLE_NAME = 'BIG_DEPT' 4 ; TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN ------------------------------ ---------- ---------- ----------- BIG_DEPT 1 289 23 BIG_EMP 182 28955 43
P184
5.4.4 CBO의 구조
Cost_Based
1.I/O 데이터를 한번에 저장하고, 한번에 불러오는 단위(블럭이 어떻게 구성되어있느냐에 따라 I/O cost가 다름) 핵심
2.CPU 크게 영향이 있는 기준은 아니다.
Cost Based Optimizer(CBO)의 구조
-Query Transformer(문장 변환기) - 오라클서버가 사용자가 작성한 쿼리를 원활한 동작을 위해서 문장변환을 한다. 불필요한 변환작업을 하지않기 위해 정확하게 sql문을 작성해야함.
-Estimator(비용계산기)
-Plan Generator(실행 계획 생성기) - 실행되어질 수 있는 여러 가지 방법 중에 가장 좋은 비용의 실행 계획을 선택해줌.
5.4.7 CBO의 문제점
1) 데이터의 분포도 문제
2) 잘못된 통계정보의 제공
3) 조인순서 및 조인방법의 선택(Cutoff)
4) 잘못된 인덱스의 선택
5) 너무 많은 테이블의 조인
6) INIT.ORA 파라메터의 잘못된 설정
1) 데이터의 분포도 문제
SQL> SELECT DEPTNO, COUNT(*) FROM BIG_EMP 2 GROUP BY DEPTNO 3 ORDER BY COUNT(*) DESC; Execution Plan ---------------------------------------------------------- -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 98 | 294 | 55 (8)| | 1 | SORT ORDER BY | | 98 | 294 | 55 (8)| | 2 | HASH GROUP BY | | 98 | 294 | 55 (8)| | 3 | TABLE ACCESS FULL| BIG_EMP | 28955 | 86865 | 51 (0)| -------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 185 consistent gets 0 physical reads 0 redo size 2203 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 99 rows processed
커디널리티(9362)와 ROWS(294)다르다. 이유는 균등한 데이터 분포라 가정)
SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS;
테이블이 분석되었습니다.
SQL> SET AUTOTRACE TRACE
SQL> SELECT * FROM BIG_EMP WHERE DEPTNO = 30;
9362 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 294 | 10584 | 52 (2)|
|* 1 | TABLE ACCESS FULL| BIG_EMP | 294 | 10584 | 52 (2)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=30)
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
808 consistent gets
0 physical reads
0 redo size
434415 bytes sent via SQL*Net to client
7279 bytes received via SQL*Net from client
626 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9362 rows processed
히스토그램(SQL문의 실행 계획을 보다 정확하게 분석할 수 있다)을 적용 SQL> SELECT DISTINCT(DEPTNO) FROM BIG_EMP; 99 개의 행이 선택되었습니다. --Bucket수 SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS 2 FOR COLUMNS DEPTNO SIZE 99; --Bucket수(1~254) 75가 기본값 테이블이 분석되었습니다. SQL> SELECT * FROM BIG_EMP WHERE DEPTNO = 30; 9362 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 9362 | 329K| 52 (2)| |* 1 | TABLE ACCESS FULL| BIG_EMP | 9362 | 329K| 52 (2)| ------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=30) Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 808 consistent gets 0 physical reads 0 redo size 434415 bytes sent via SQL*Net to client 7279 bytes received via SQL*Net from client 626 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9362 rows processed
해당 테이블에 히스토그램 적용 유무 확인 SQL> SELECT TABLE_NAME, COLUMN_NAME, 2 NUM_DISTINCT, NUM_BUCKETS, SAMPLE_SIZE 3 FROM USER_TAB_COLUMNS 4 WHERE TABLE_NAME = 'BIG_EMP' AND COLUMN_NAME = 'DEPTNO'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE ------------------------------ ------------------------------ ------------ ----------- ----------- BIG_EMP DEPTNO 98 98 28955
5.5 DBMS_STATS 패키지
-프로시저 및 함수를 자동실행할 때 사용할 수 있다.
-현재 데이터베이스 내의 상태정보를 수집할 수 있다.
-수집된 상태정보를 참조하거나 재설정 할 수 있다.
-수집된 상태정보를 백업할 수 있으며, 때에 따라서는 복구할 수 있다.
-자동화된 스케쥴링 작업 가능하다.
-통계정보는 마지막 통계정보만 수집한다.(해결책:백업 근데? 11g부터 이전 통계정보 자동 백업됨)
P.S
P673
SPM(SQL PLAN Management)-최신의 통계 정보는 수집하되 그 적용은 나중에 필요에 따라 적용하기 위한 기능/새로운 통계 정보를 적용하기 전에 SQL문을 테스트할 수 있다.
통계 정보의 지연 적용(이전 통계정보와 최신의 통계정보를 비교할 수 있도록 한다. 11g 추가기능)
-통계정보 수집을 하면 바로 데이터딕셔너리에 들어가는게 아니라 Pending_Area에 들어간다.(충분한 검증후에 되도록)
-오라클 11g 버전에서는 특정 테이블, 인덱스 통계정보를 Pending상태로 지정할 수 있다.
SQL> ANALYZE TABLE EMP COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> CONN SCOTT/TIGER 연결되었습니다. SQL> BEGIN 2 DBMS_STATS.SET_TABLE_PREFS( ownname => 'SCOTT', 3 tabname => 'EMP', 4 pname => 'PUBLISH', 5 pvalue => 'FALSE'); //지연적용 해라 6 END; 7 / PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SELECT num_rows, to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss') 2 FROM user_tables 3 WHERE table_name = 'EMP'; NUM_ROWS TO_CHAR(LAST_ANAL ---------- ----------------- 14 03/28/17 14:54:21
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT',
3 tabname => 'EMP',
4 estimate_percent => 100);
5 END;
6 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT num_rows, to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss')
2 FROM user_tables
3 WHERE table_name = 'EMP';
NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
14 03/28/17 14:54:21 -- 해당 테이블의 통계정보에 대해 지연 환경이 적용되었기 때문에 시간정보가 변경되지 않음
P675 SQL> SELECT num_rows, to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss') 2 FROM user_tab_pending_stats 3 WHERE table_name = 'EMP'; NUM_ROWS TO_CHAR(LAST_ANAL ---------- ----------------- 14 03/28/17 15:00:17
SQL> EXECUTE DBMS_STATS.PUBLISH_PENDING_STATS('SCOTT', 'EMP');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT num_rows, to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss')
2 FROM user_tab_pending_stats
3 WHERE table_name = 'EMP';
선택된 레코드가 없습니다. <--Pending 상태가 해제되어 있음을 확인할 수 있다.
SQL> SELECT num_rows, to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss')
2 FROM user_tables
3 WHERE table_name = 'EMP';
NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
14 03/28/17 15:00:17 -- 마지막 통계정보가 수집된 날짜 정보로 변경되었음
SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE; //Pending 영역으로 실행계획을 만든다.
SQL> EXECUTE DBMS_STATS.DELETE_PENDING_STATS('SCOTT', 'EMP'); //Pending 영역 삭제
백업된 통계정보 내역 확인하기 SQL> CONN /AS SYSDBA 연결되었습니다. SQL> SELECT to_char(stats_update_time, 'mm/dd/yy hh24:mi:ss') 2 FROM dba_tab_stats_history 3 WHERE owner='SCOTT' and table_name='EMP'; TO_CHAR(STATS_UPD ----------------- 03/28/17 15:03:41
복원하기
P669
A.4 실행 계획의 재평가 기능
오라클 11g 버전에서는 통계 정보, 매개 변수 등과 같은 하부 요인에 변화가 생겼을 때 실행 계획을 자동으로 재 평가하고 다른 실행 계획이 더 최적인 것으로 확인된 경우라면 실행 계획을 변경할 수 있도록 적용 해주는 기능이 추가됨.
SQL PLAN Baselining
SQL> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE; 시스템이 변경되었습니다. SQL> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE
5.6 스토어드 아우트라인(Stored Outline)
이전의 실행계획을 데이터베이스 내에 저장해 두었다가 재사용 할 수 있는 기능
6 HINT절과 SQL 자동 튜닝
실행계획 결정방법
RBO -> CBO -> 사람(사람이 직접 제어할 때 쓰는 문법절을 HINT라고한다.)
6.1.1 문법
문법
1. /*+ HINT */
2. --+ HINT
--+의 경우에는 닫는부호가 없기때문에 뒤에 컬럼명을 쓰면 안된다.
HINT를 줬는데 실행이 안되는경우.
1. 오타 - HINT절을 잘못 작성하면 주석으로 인식함
2. CBO, RBO 섞어서 쓰면 안됨
3. 최우선 순위(ex: Parallel_scan우선순위 > Full_scan,Index_scan, Cluster_scan 무시됨)
6.1.2 HINT절의 종류
이미지 출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148218&boardConfigUid=9&categoryUid=216&boardIdx=139&boardStep=1
-RULE
-FIRST_ROWS(존재여부를 빠르게 찾기 위한)
-ALL_ROWS(전체를 빠르게 찾기 위한)
-FULL(FULL SCAN)
-ROWID
-INDEX(이 INDEX 써줘)
-NO_INDEX(이 INDEX는 쓰지마)
-INDEX_DESC
-INDEX_ASC
-INDEX_FFS - INDEX FAST FULL SCAN으로 인덱스 만을 검색하는 힌트절
-USE_CONCAT
-NO_EXPAND
-NO_MERGE
-MERGE
-LEADING
P.S
*옵티마이저 모드 결정방법
1)Session level(세션)
ALTER SESSION SET OPTIMIZER_MODE=RULE;
2)Statement level(문장단위)
SELECT /*+ HINT */ ~
3)DB-level(P171참고)
optimizer_mode string ALL_ROWS
P.SS
*Index-Operation
Index Range(부분) Scan - ex)WHERE DEPTNO=20 OR DEPTNO=30
Index Unique Scan
Index Full(전체) Scan - ex)WHERE DEPTNO>=20 OR DEPTNO<=30
Index Fast-Full scan - index만 검색하고 추가적인 테이블 검색이 필요 없는 경우)
Index Skip Scan
'외부 > 교육' 카테고리의 다른 글
| [2017.04.27~2017.04.28] DevOpse with Docker 2/2 (0) | 2017.04.28 |
|---|---|
| [2017.04.27~2017.04.28] DevOpse with Docker 1/2 (0) | 2017.04.27 |
| [2017.03.27~2017.03.30]데이터베이스 분석 및 개선을 통한 성능 확보 교육 4/4 (0) | 2017.03.31 |
| [2017.03.27~2017.03.30]데이터베이스 분석 및 개선을 통한 성능 확보 교육 3/4 (0) | 2017.03.30 |
| [2017.03.27~2017.03.30]데이터베이스 분석 및 개선을 통한 성능 확보 교육 1/4 (0) | 2017.03.26 |
