본문 바로가기
DBMS/ORACLE

[oracle] optimizer_adaptive_plans 기능 설명 및 권장값

by 드바 2023. 12. 22.

DBMS: oracle 19.3

 

1. adaptive plan

optimizer_adaptive_plans는 실행 중인 쿼리의 실행 계획을 동적으로 조정하여 최적의 성능을 제공하는 데 사용됩니다.
 
아래 예시처럼 실행계획에 STATISTICS COLLECTOR 구문이 보이면 adaptive_plans이 적용됩니다.
실행계획에 조인 방법이 2개 보이는데(HASH JOIN과 NL JOIN이 함께 보임)
SQL실행 시 2개의 방법 중 수집된 통계를 바탕으로 최적의 경로를 선택하는 기능입니다.

statistics collector

 

관련 파라미터

optimizer_adaptive_plans(기본값: true)
운영환경에서 갑작스런 PLAN변경은 민감한 부분이기 때문에 비활성(false) 권고

adaptive plan 기능 비활성 힌트

no_adaptive_plan 힌트 사용으로 SQL레벨에서 기능 비활성

-- no_adaptive_plan
SELECT /*+ index(e) index(d) no_adaptive_plan */ *
  FROM emp e, dept d
 WHERE e.deptno = d.deptno(+)
   AND e.empno = '7654'
 UNION ALL
SELECT /*+ index(e) index(d) */ *
  FROM emp e, dept d
 WHERE e.deptno = d.deptno
   AND d.dname = 'SALES'
   AND lnnvl(e.empno = '7654') ;


Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=5 Bytes=290)
   1    0   UNION-ALL
   2    1     NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=58)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=38)
   4    3         INDEX (UNIQUE SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=0 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
   6    5         INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1)
   7    1     NESTED LOOPS (Cost=3 Card=4 Bytes=232)
   8    7       NESTED LOOPS (Cost=3 Card=5 Bytes=232)
   9    8         TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'DEPT' (TABLE) (Cost=2 Card=1 Bytes=20)
  10    9           INDEX (RANGE SCAN) OF 'DEPT_IX1' (INDEX) (Cost=1 Card=1)
  11    8         INDEX (RANGE SCAN) OF 'EMP_IX1' (INDEX) (Cost=0 Card=5)
  12    7       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=4 Bytes=152)
-----------------------------------------------------------

 

2. 기능 테스트

실행계획 조회

DEPT가 EMP와 조인할 때 HASH JOIN, NL JOIN 2개의 실행 계획이 보입니다

-- SQL
SELECT /*+ index(e) index(d) */ *
  FROM emp e, dept d
 WHERE e.deptno = d.deptno(+)
   AND e.empno = '7654'
 UNION ALL
SELECT /*+ index(e) index(d) */ *
  FROM emp e, dept d
 WHERE e.deptno = d.deptno
   AND d.dname = 'SALES'
   AND lnnvl(e.empno = '7654') ;

-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=5 Bytes=290)
   1    0   UNION-ALL
   2    1     NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=58)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=38)
   4    3         INDEX (UNIQUE SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=0 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
   6    5         INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1)
   7    1     HASH JOIN (Cost=3 Card=4 Bytes=232)
   8    7       NESTED LOOPS (Cost=3 Card=4 Bytes=232)
   9    8         NESTED LOOPS (Cost=3 Card=5 Bytes=232)
  10    9           STATISTICS COLLECTOR
  11   10             TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'DEPT' (TABLE) (Cost=2 Card=1 Bytes=20)
  12   11               INDEX (RANGE SCAN) OF 'DEPT_IX1' (INDEX) (Cost=1 Card=1)
  13    9           INDEX (RANGE SCAN) OF 'EMP_IX1' (INDEX) (Cost=0 Card=5)
  14    8         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=4 Bytes=152)
  15    7       TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'EMP' (TABLE) (Cost=1 Card=4 Bytes=152)
  16   15         INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=0 Card=5)
-----------------------------------------------------------

 

SQL 실행

SQL 수핼 후 dbms_xplan 확인 시 NL로 풀린 것이 확인됩니다(note에 adaptive plan이라는 것이 보입니다)

 -- 실제 실행 통계
----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |      1 |      6 |00:00:00.01 |       8 |
|   1 |  UNION-ALL                             |          |      1 |      6 |00:00:00.01 |       8 |
|   2 |   NESTED LOOPS OUTER                   |          |      1 |      1 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID         | EMP      |      1 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN                  | PK_EMP   |      1 |      1 |00:00:00.01 |       1 |
|   5 |    TABLE ACCESS BY INDEX ROWID         | DEPT     |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX UNIQUE SCAN                  | PK_DEPT  |      1 |      1 |00:00:00.01 |       1 |
|   7 |   NESTED LOOPS                         |          |      1 |      5 |00:00:00.01 |       4 |
|   8 |    NESTED LOOPS                        |          |      1 |      6 |00:00:00.01 |       3 |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED| DEPT     |      1 |      1 |00:00:00.01 |       2 |
|* 10 |      INDEX RANGE SCAN                  | DEPT_IX1 |      1 |      1 |00:00:00.01 |       1 |
|* 11 |     INDEX RANGE SCAN                   | EMP_IX1  |      1 |      6 |00:00:00.01 |       1 |
|* 12 |    TABLE ACCESS BY INDEX ROWID         | EMP      |      6 |      5 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan

 

비활성된(선택되지않은) PLAN 정보 확인

dbms_xplan 사용 시 ADAPTIVE 옵션을 주면 어떤 부분이 비활성화되었는지 확인 가능 합니다

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('69kt89d42t0td',NULL,'ADAPTIVE ALLSTATS LAST -ROWS')) ;
--------------------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name     | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |          |      1 |      6 |00:00:00.01 |       8 |
|     1 |  UNION-ALL                               |          |      1 |      6 |00:00:00.01 |       8 |
|     2 |   NESTED LOOPS OUTER                     |          |      1 |      1 |00:00:00.01 |       4 |
|     3 |    TABLE ACCESS BY INDEX ROWID           | EMP      |      1 |      1 |00:00:00.01 |       2 |
|  *  4 |     INDEX UNIQUE SCAN                    | PK_EMP   |      1 |      1 |00:00:00.01 |       1 |
|     5 |    TABLE ACCESS BY INDEX ROWID           | DEPT     |      1 |      1 |00:00:00.01 |       2 |
|  *  6 |     INDEX UNIQUE SCAN                    | PK_DEPT  |      1 |      1 |00:00:00.01 |       1 |
|- *  7 |   HASH JOIN                              |          |      1 |      5 |00:00:00.01 |       4 |
|     8 |    NESTED LOOPS                          |          |      1 |      5 |00:00:00.01 |       4 |
|     9 |     NESTED LOOPS                         |          |      1 |      6 |00:00:00.01 |       3 |
|-   10 |      STATISTICS COLLECTOR                |          |      1 |      1 |00:00:00.01 |       2 |
|    11 |       TABLE ACCESS BY INDEX ROWID BATCHED| DEPT     |      1 |      1 |00:00:00.01 |       2 |
|  * 12 |        INDEX RANGE SCAN                  | DEPT_IX1 |      1 |      1 |00:00:00.01 |       1 |
|  * 13 |      INDEX RANGE SCAN                    | EMP_IX1  |      1 |      6 |00:00:00.01 |       1 |
|  * 14 |     TABLE ACCESS BY INDEX ROWID          | EMP      |      6 |      5 |00:00:00.01 |       1 |
|-   15 |    TABLE ACCESS BY INDEX ROWID BATCHED   | EMP      |      0 |      0 |00:00:00.01 |       0 |
|- * 16 |     INDEX FULL SCAN                      | PK_EMP   |      0 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

 

데이터 변경 후 테스트

기준이 되는 dept 테이블의 데이터를 증가시키고 테이블 통계정보는 변경 없는 상태에서 테스트

-- dept 건수
SQL> select count(*) from dept ;
  COUNT(*)
----------
	32
    
---- dept 통계정보는 여전히 4건
SQL> select num_rows from dba_Tables where table_name = 'DEPT' ;
  NUM_ROWS
----------
	 4

 
dept 테이블 데이터가 증가하자 실행계획이 변경되었습니다

-----------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name     | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |          |      1 |      6 |00:00:00.01 |       8 |       |       |          |
|     1 |  UNION-ALL                               |          |      1 |      6 |00:00:00.01 |       8 |       |       |          |
|     2 |   NESTED LOOPS OUTER                     |          |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|     3 |    TABLE ACCESS BY INDEX ROWID           | EMP      |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  *  4 |     INDEX UNIQUE SCAN                    | PK_EMP   |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|     5 |    TABLE ACCESS BY INDEX ROWID           | DEPT     |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  *  6 |     INDEX UNIQUE SCAN                    | PK_DEPT  |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|  *  7 |   HASH JOIN                              |          |      1 |      5 |00:00:00.01 |       4 |  1185K|  1185K|  922K (0)|
|-    8 |    NESTED LOOPS                          |          |      1 |      8 |00:00:00.01 |       2 |       |       |          |
|-    9 |     NESTED LOOPS                         |          |      1 |      8 |00:00:00.01 |       2 |       |       |          |
|-   10 |      STATISTICS COLLECTOR                |          |      1 |      8 |00:00:00.01 |       2 |       |       |          |
|    11 |       TABLE ACCESS BY INDEX ROWID BATCHED| DEPT     |      1 |      8 |00:00:00.01 |       2 |       |       |          |
|  * 12 |        INDEX RANGE SCAN                  | DEPT_IX1 |      1 |      8 |00:00:00.01 |       1 |       |       |          |
|- * 13 |      INDEX RANGE SCAN                    | EMP_IX1  |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|- * 14 |     TABLE ACCESS BY INDEX ROWID          | EMP      |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|    15 |    TABLE ACCESS BY INDEX ROWID BATCHED   | EMP      |      1 |     13 |00:00:00.01 |       2 |       |       |          |
|  * 16 |     INDEX FULL SCAN                      | PK_EMP   |      1 |     13 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

 
참고
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/query-optimizer-concepts.html#GUID-5A1EB094-1A9E-4B69-9BE5-39BDA2B3253C
 
 

'DBMS > ORACLE' 카테고리의 다른 글

[oracle] 오브젝트 생성시간은 어떤 시간대(time zone)  (0) 2024.02.17
[oracle] 테이블 LOB Object 이름 지정  (0) 2024.02.08
[oracle] datapump 이기종버전 원격지 데이터 로컬로 가져오기  (1) 2023.12.03
oracle expdp 원격지(remote) 데이터 로컬로 가져오기  (0) 2023.12.01
ASM DISK 추가/일시중지/재개 시 상태 변화  (0) 2023.10.26

관련글

댓글

티스토리툴바