[Oracle] SQL 어드바이저를 활용한 SQL 튜닝

[실습1] 아래의 SQL을 오렌지의 SQL 튜닝 어드바이져로 튜닝하시오

@demo

select  ename, sal
  from emp
  where sal = 3000  and ename='SCOTT'; 

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ORANGE:202509091118131
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 09/09/2025 11:18:10
Completed at       : 09/09/2025 11:18:11

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 1r01x0kf4bya8
SQL Text   : select  ename, sal
               from emp
               where sal = 3000  and ename='SCOTT'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  "SCOTT"."EMP" 테이블은 분석되지 않았습니다.

  Recommendation
  --------------
  - 이 테이블에 대한 최적기 통계를 수집하는 것을 고려하십시오.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    더 나은 실행 계획을 선택하기 위해서는 최적기에 테이블에 대한 최신 통계가 필요합니다.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  이 명령문의 실행 계획은 하나 이상의 인덱스를 생성하여 향상시킬 수 있습니다.

  Recommendation (estimated benefit: 66.67%)
  ------------------------------------------
  - Access Advisor를 실행하여 물리적 스키마 설계를 향상시키거나 권장 인덱스를 생성하는 것을 고려하십시오.
    create index SCOTT.IDX$$_004E0001 on SCOTT.EMP("ENAME","SAL");

  Rationale
  ---------
    권장 인덱스를 생성하면 이 명령문의 실행 계획이 크게 향상됩니다. 하지만 단일 명령문 대신 대표 SQL 작업 로드를 사용하는
    "Access Advisor"를 실행하는 것이 더 좋을 수 있습니다. 이렇게 하면 인덱스 유지 관리 오버헤드 및 추가 공간 사용을
    고려한 포괄적인 인덱스 권장 사항을 얻을 수 있게 됩니다.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SAL"=3000 AND "ENAME"='SCOTT')

2- Using New Indices
--------------------
Plan hash value: 4085784664

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    20 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_004E0001 |     1 |    20 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ENAME"='SCOTT' AND "SAL"=3000)

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

위에서 권고한대로 따라하면

execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');


create index SCOTT.emp_ename_sal on SCOTT.EMP("ENAME","SAL");

실행계획 보기

select  ename, sal
  from emp
  where sal = 3000  and ename='SCOTT'; 
  
SQL_ID  1r01x0kf4bya8, child number 0
-------------------------------------
select  ename, sal   from emp   where sal = 3000  and ename='SCOTT'
 
Plan hash value: 3640066991
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| EMP_ENAME_SAL |      1 |      1 |    10 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / EMP@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ENAME"='SCOTT' AND "SAL"=3000)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]

---

문제1. 아래의 SQL을 오렌지의 SQL 튜닝 어드바이져로 튜닝하고 튜닝후의 실행계획을 보시오

select count(*)
 from emp, emp, emp, emp;

실행계획

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ORANGE:202509091126381
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 09/09/2025 11:26:35
Completed at       : 09/09/2025 11:26:35

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 8x3s3x6d3m9xh
SQL Text   : select count(*)
              from emp, emp, emp, emp

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  비용이 높은 카티션 곱 연산이 실행 계획의 행 ID 2에서 발견되었습니다.

  Recommendation
  --------------
  - 이 명령문에서 접속이 해제된 테이블 또는 뷰를 제거하는 것을 고려하거나 이러한 객체를 참조하는 조인 조건을 추가하십시오.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 4002368597

------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |  4011   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE         |      |     1 |            |          |
|   2 |   MERGE JOIN CARTESIAN  |      | 38416 |  4011   (1)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN |      |  2744 |   291   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|      |   196 |    24   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL  | EMP  |    14 |     3   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |      |    14 |    21   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL | EMP  |    14 |     2   (0)| 00:00:01 |
|   8 |     BUFFER SORT         |      |    14 |   290   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL  | EMP  |    14 |     1   (0)| 00:00:01 |
|  10 |    BUFFER SORT          |      |    14 |  4010   (1)| 00:00:01 |
|  11 |     TABLE ACCESS FULL   | EMP  |    14 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------

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