오라클 데이터베이스 성능 튜닝.

Presentation on theme: "오라클 데이터베이스 성능 튜닝."— Presentation transcript:

1 오라클 데이터베이스 성능 튜닝

2 Hint란 Hint 란 EXPLAIN PLAN
오라클은 SQL 문장 수행을 위해 실행계획을 생성한다. Optimizer가 생성한 실행계획이 항상 가장 효율적인 계획이 되지는 않는다. 효율적이지 않은 실행계획을 세우고 수행할 경우 개선을 시켜줄 장치가 필요하다. 이 때 Hint란 도구를 사용해서 효율적인 실행계획을 수립하고 수행하도록 훈수를 둘 수 있게끔 만들어 주는 것을 Hint라고 한다. Hint를 사용할 경우 오라클이 만든 실행계획은 무시되고 유저의 Hint를 참조한 실행계획을 세우는 방향으로 진행된다.

3 Hint Hint의 사용 방법 여러 줄의 사용 방법 /*+ Hint */ --+ Hint

4 Hint Hint의 사용 방법 예제 C:\Documents and Settings\Administrator>sqlplus scott/tiger SQL*Plus: Release Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options SQL> CREATE INDEX SCOTT.I_BIG_EMP_DEPTNO ON SCOTT.BIG_EMP(DEPTNO) 2 TABLESPACE USERS; 인덱스가 생성되었습니다. SQL> CREATE UNIQUE INDEX I_BIG_EMP_EMPNO ON BIG_EMP(EMPNO); SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'ORIG' 3 FOR 4 SELECT ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE DEPTNO = 10; 해석되었습니다.

5 Hint Hint의 사용 방법 예제 SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','ORIG')); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | | | (3)| |* 1 | TABLE ACCESS FULL| BIG_EMP | | | (3)| Predicate Information (identified by operation id): 1 - filter("DEPTNO"=10) Note ----- - 'PLAN_TABLE' is old version 16 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'HINT1' 3 FOR 4 SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_DEPTNO) */ ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE DEPTNO = 10; 해석되었습니다.

6 Hint Hint의 사용 방법 예제 SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','HINT1')); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | | | (0)| | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| |* 2 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO | | | (0)| Predicate Information (identified by operation id): 2 - access("DEPTNO"=10) Note ----- - 'PLAN_TABLE' is old version 17 개의 행이 선택되었습니다. SQL>

7 Hint의 종류 Optimizer Mode
ALL_ROWS Cost-Based Optimizer에 의한 실행계획이 수립되며 모든 행이 최대한 빨리 나오는 것을 목표로 실행계획이 수립됨 FIRST_ROWS(N) Cost-Based Optimizer에 의한 실행계획이 수립되며 첫째행이 최대한 빨리 나오는 것을 목표로 실행계획이 수립됨 CHOOSE SQL에 포함되는 각 Object들의 통계 존재여부를 통해 Rule-Based Optimizer와 Cost-Based Optimizer 사이에 선택하여 실행계획을 수립함 RULE Rule-Based Optimizer에 의한 실행계획이 수립되도록 유도함

8 Hint의 종류 ALL_ROWS 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'ALL_ROWS' 3 FOR 4 SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_DEPTNO) */ ENAME 5 FROM BIG_EMP 6 WHERE DEPTNO = 20 7 AND EMPNO BETWEEN 100 AND 200 8 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','ALL_ROWS')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 0 | SELECT STATEMENT | | | | (2)| 00:00:01 | 1 | SORT ORDER BY | | | | (2)| 00:00:01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 |* 3 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO | | | (0)| 00:00:01 Predicate Information (identified by operation id): 2 - filter("EMPNO"<=200 AND "EMPNO">=100) 3 - access("DEPTNO"=20) 16 개의 행이 선택되었습니다. SQL>

9 Hint의 종류 FIRST_ROWS(N) 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'FIRST_ROWS' 3 FOR 4 SELECT /*+ FIRST_ROWS(1) */ ENAME 5 FROM BIG_EMP 6 WHERE DEPTNO = 20 7 AND EMPNO BETWEEN 100 AND 200 8 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','FIRST_ROWS')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 0 | SELECT STATEMENT | | | | (20)| 00:00:01 | 1 | SORT ORDER BY | | | | (20)| 00:00:01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 |* 3 | INDEX RANGE SCAN | I_BIG_EMP_EMPNO | | | (0)| 00:00:01 Predicate Information (identified by operation id): 2 - filter("DEPTNO"=20) 3 - access("EMPNO">=100 AND "EMPNO"<=200) 16 개의 행이 선택되었습니다. SQL>

10 Hint의 종류 CHOOSE 실습 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'CHOOSE'
3 FOR 4 SELECT /*+ CHOOSE */ ENAME 5 FROM BIG_EMP 6 WHERE DEPTNO = 20 7 AND EMPNO BETWEEN 100 AND 200 8 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','CHOOSE')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 0 | SELECT STATEMENT | | | | (20)| 00:00:01 | 1 | SORT ORDER BY | | | | (20)| 00:00:01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 |* 3 | INDEX RANGE SCAN | I_BIG_EMP_EMPNO | | | (0)| 00:00:01 Predicate Information (identified by operation id): 2 - filter("DEPTNO"=20) 3 - access("EMPNO">=100 AND "EMPNO"<=200) 16 개의 행이 선택되었습니다. SQL>

11 Hint의 종류 RULE 실습 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'RULE'
3 FOR 4 SELECT /*+ RULE */ ENAME 5 FROM BIG_EMP 6 WHERE DEPTNO = 20 7 AND EMPNO BETWEEN 100 AND 200 8 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','RULE')); PLAN_TABLE_OUTPUT Plan hash value: | 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 ----- - rule based optimizer used (consider using cbo) 20 개의 행이 선택되었습니다.

12 Hint의 종류 Table Access 유형 FULL full table scan 실행계획이 수립되도록 함
INDEX index를 사용한 실행계획이 수립되도록 함 NO_INDEX index를 사용하지 않는 실행계획이 수립되도록 함 INDEX_ASC index를 올림차순으로 사용하는 실행계획이 수립되도록 함 INDEX_DESC index를 내림차순으로 사용하는 실행계획이 수립되도록 함 INDEX_FFS index만을 검색하는 실행계획이 수립되도록 함

13 Hint의 종류 FULL 실습 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'FULL'
3 FOR 4 SELECT /*+ FULL(BIG_EMP) */ ENAME 5 FROM BIG_EMP 6 WHERE DEPTNO = 20 7 AND EMPNO BETWEEN 100 AND 200 8 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','FULL')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (5)| 00:00:01 | | 1 | SORT ORDER BY | | | | (5)| 00:00:01 | |* 2 | TABLE ACCESS FULL| BIG_EMP | | | (3)| 00:00:01 | Predicate Information (identified by operation id): 2 - filter("EMPNO"<=200 AND "DEPTNO"=20 AND "EMPNO">=100) 14 개의 행이 선택되었습니다. SQL>

14 Hint의 종류 INDEX 실습 SQL> CREATE INDEX TEST_INDEX ON BIG_EMP(EMPNO, ENAME); 인덱스가 생성되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'INDEX' 3 FOR 4 SELECT ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','INDEX')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | I_BIG_EMP_EMPNO | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL>

15 Hint의 종류 INDEX 실습 SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'INDEX1'
3 FOR 4 SELECT /*+ INDEX(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','INDEX1')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL>

16 Hint의 종류 INDEX_ASC 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'INDEX_ASC' 3 FOR 4 SELECT /*+ INDEX_ASC(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','INDEX_ASC')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | | (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL>

17 Hint의 종류 INDEX_DESC 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'INDEX_DESC' 3 FOR 4 SELECT /*+ INDEX_DESC(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO 5 FROM BIG_EMP 6 WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','INDEX_DESC')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | BIG_EMP | | | (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN DESCENDING| TEST_INDEX | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL>

18 Hint의 종류 INDEX_FFS 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'INDEX_FF' 3 FOR 4 SELECT /*+ INDEX_FFS(BIG_EMP TEST_INDEX) */ EMPNO 5 FROM BIG_EMP 6 WHERE EMPNO < 200; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','INDEX_FF')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| TEST_INDEX | | | (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter("EMPNO"<200) 13 개의 행이 선택되었습니다. SQL>

19 Hint의 종류 SQL transformation 유형
USE_CONCAT full table scan 실행계획이 수립되도록 함 NO_EXPAND index를 사용한 실행계획이 수립되도록 함 MERGE index를 사용하지 않는 실행계획이 수립되도록 함 NO_MERGE index를 올림차순으로 사용하는 실행계획이 수립되도록 함 REWRITE index를 내림차순으로 사용하는 실행계획이 수립되도록 함 NOREWRITE index만을 검색하는 실행계획이 수립되도록 함

20 Hint의 종류 USE_CONCAT 실습 SQL> CREATE INDEX TEST_INDEX1 ON BIG_EMP(SAL); 인덱스가 생성되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'USE_CONCAT' 3 FOR 4 SELECT /*+ RULE USE_CONCAT */ * 5 FROM BIG_EMP 6 WHERE EMPNO > 50 7 OR SAL < 50000; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','USE_CONCAT')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | | 0 | SELECT STATEMENT | | | 1 | CONCATENATION | | | 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | |* 3 | INDEX RANGE SCAN | TEST_INDEX1 | |* 4 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | |* 5 | INDEX RANGE SCAN | TEST_INDEX |

21 Hint의 종류 USE_CONCAT 실습 Predicate Information (identified by operation id): 3 - access("SAL"<50000) 4 - filter(LNNVL("SAL"<50000)) 5 - access("EMPNO">50) Note ----- PLAN_TABLE_OUTPUT - rule based optimizer used (consider using cbo) 23 개의 행이 선택되었습니다. SQL>

22 Hint의 종류 NO_EXPAND 실습 SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'NO_EXPAND' 3 FOR 4 SELECT /*+ INDEX(BIG_EMP TEST_INDEX) NO_EXPAND */ * 5 FROM BIG_EMP 6 WHERE EMPNO > 50 7 OR SAL < 50000; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','NO_EXPAND')); PLAN_TABLE_OUTPUT Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 1017K| (1)| 00:00:08 | |* 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | | 1017K| (1)| 00:00:08 | | 2 | INDEX FULL SCAN | TEST_INDEX | | | (0)| 00:00:02 | Predicate Information (identified by operation id): 1 - filter("SAL"<50000 OR "EMPNO">50) 14 개의 행이 선택되었습니다. SQL>

23 Hint의 종류 JOIN 방법 유형 ORDERED full table scan 실행계획이 수립되도록 함
LEADING index를 사용한 실행계획이 수립되도록 함 USE_NL index를 사용하지 않는 실행계획이 수립되도록 함 USE_MERGE index를 올림차순으로 사용하는 실행계획이 수립되도록 함 USE_HASH index를 내림차순으로 사용하는 실행계획이 수립되도록 함

24 Hint의 종류 JOIN 방법 유형 DRIVING_SITE full table scan 실행계획이 수립되도록 함
HASH_AJ, MERGE_AJ, NL_AJ index를 사용한 실행계획이 수립되도록 함 HASH_SJ, MERGE_SJ, NL_SJ index를 사용하지 않는 실행계획이 수립되도록 함

25 Hint의 종류 JOIN 방법 유형 DRIVING_SITE full table scan 실행계획이 수립되도록 함
HASH_AJ, MERGE_AJ, NL_AJ index를 사용한 실행계획이 수립되도록 함 HASH_SJ, MERGE_SJ, NL_SJ index를 사용하지 않는 실행계획이 수립되도록 함

26 Hint의 종류 병렬 옵션 Parallel Execution Parallel execution을 사용하면 하나의 일을 여러 개의 일로 쪼개서 병렬로 수행한다 이는 단일 수행보다 수행시간에 이득을 가져온다 (parallel query, parallel DDL, parallel DML, parallel Data Loading)

27 Hint의 종류 병렬 옵션 동작원리 유저가 SQL문을 수행하면 오라클은 서버 프로세스를 구동하고 이는 SQL 수행과 유저(유저프로세스)에게 결과 제공하는 일을 수행한다 아래는 Single execution에 해당하는 그림이다 SELECT COUNT(*) FROM ORDERS; User process Server process Database

28 Hint의 종류 병렬 옵션 동작원리 다음은 parallel execution에 대항하는 그림이다 SELECT /*+ parallel (orders, 4) */ COUNT (*) FROM ORDERS; parallel execution에서는 서버 프로세스는 parallel execution server들의 상위의 역할을 수행하는 parallel execution coordinator 역할을 수행한다 그리고 더 이상의 일은 수행하지 않는다 위의 쿼리는 4개의 sub task로 나뉘어서 수행된다. User process Server process (Query coordinator) Database Parallel execution server processes

29 Hint의 종류 병렬 옵션 Parallel Query full table scan, full index scan, partition range scan, index range scan을 수행하는 SELECT문에서 사용된다. parallel 힌트 사용을 통해 수행할 수 있다 또는 테이블을 변경하여 쿼리 수행 시 해당 테이블이 위 경우에 해당하면 자동으로 parallel 정도로 수행이된다. 세션 레벨에서 parallel execution에 대해서 사용을 금할 수 있다 또는 문장 단위로 사용을 금할 수 있다. SELECT /*+ parallel (orders, 4) */ COUNT (*) FROM ORDERS; ALTER TABLE ORDERS PARALLEL (DEGREE 4); ALTER SESSION DISABLE PARALLEL QUERY; SELECT /*+ NO_PARALLEL (ORDERS) */ COUNT (*) FROM ORDERS;

30 Hint의 종류 병렬 옵션 Parallel DML INSERT, UPDATE, MERGE, DELETE 문에서 사용이 가능하다 이는 배치 job, 집계 정보 생성, 대량 데이터 Moving에 유용하다 INSERT INTO … VALUES (…)문장은 row 하나씩 작업을 하기 때문에 parallel execution이 수행되지 않는다 하지만 INSERT INTO … SELECT … FROM문장은 수행이 가능하다 Parallel DML은 기본으로 Disable 되어 있다 Parallel DML을 사용 전에 명시적으로 다음을 선언해야 한다. 명시적으로 세팅한 후 parallel 힌트나 테이블의 parallel 속성을 변경하여 parallel DML 수행이 가능한다. ALTER SESSION ENABLE PARALLEL DML;

31 Hint의 종류 병렬 옵션 Parallel DML 위 문장은 SELECT 문은 Single execution으로 수행이 되고 INSERT 문은 parallel로 수행된다 반면 위 문장은 SELECT 문과 INSERT 문 모두가 parallel로 수행된다. INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */ INTO TEMP_ORDERS SELECT * FROM ORDERS; INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */ INTO TEMP_ORDERS SELECT /*+ PARALLEL (ORDERS, 4) */ * FROM ORDERS;