통계 정보의 부족이나, 오래된 통계정보를 가지고 잘 못 생성 된 Plan을 이용하는 SQL을 튜닝하는 방법 중 하나는 힌트를 사용하는 것이다.
그러나 패키지화된 제품 안의 SQL이 잘못 된 실행 계획을 가지고 동작을 할 경우, 제품 안의 SQL을 수정하기는 힘든 상황에서 다음과 같이 SQL Profile을 이용하여 해당 SQL의 Plan을 변경할 수 있다.
0. 테스트를 위하여 Scott 스키마에 TEST_SPF 테이블과 인덱스를 생성 하고 통계 정보를 생성 하자.
SCOTT@ora11g 11-JUL-16> CREATE TABLE TEST_SPF( id INT , amount INT , comm VARCHAR2(4000) ) ; Table created. SCOTT@ora11g 11-JUL-16> CREATE INDEX IDX_TEST_SPF_ID ON TEST_SPF ( id ) ; Index created. SCOTT@ora11g 11-JUL-16> INSERT INTO test_spf SELECT 5, 1000+2*level, lpad('',4000,'*') FROM dual CONNECT BY LEVEL <= 1000000 UNION ALL SELECT 4, 1000+2*level, lpad('',4000,'*') FROM dual CONNECT BY LEVEL <= 10000 UNION ALL SELECT 3, 1000+2*level, lpad('',4000,'*') FROM dual CONNECT BY LEVEL <= 10000 UNION ALL SELECT 2, 1000+2*level, lpad('',4000,'*') FROM dual CONNECT BY LEVEL <= 100 UNION ALL SELECT 1, 1000+2*level, lpad('',4000,'*') FROM dual CONNECT BY LEVEL <= 1;
1020101 rows created.
SCOTT@ora11g 11-JUL-16> COMMIT ; Commit complete. SCOTT@ora11g 11-JUL-16> EXEC dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'TEST_SPF', cascade=>true, estimate_percent=>100, method_opt=>'for all indexed columns size auto', no_invalidate=>false);
PL/SQL procedure successfully completed. SCOTT@ora11g 11-JUL-16> SELECT TABLE_NAME , BLOCKS , NUM_ROWS FROM user_tables where TABLE_NAME = 'TEST_SPF' ; TABLE_NAME BLOCKS NUM_ROWS ------------------------------ ---------- ---------- TEST_SPF 2008 1020101 SCOTT@ora11g 11-JUL-16> SELECT sum(amount) FROM test_spf WHERE id = 5 ; SUM(AMOUNT) ----------- 1.0010E+12 SCOTT@ora11g 11-JUL-16> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost outline predicate bytes')) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 9jkbxwsdk9vhr, child number 0 ------------------------------------- SELECT sum(amount) FROM test_spf WHERE id = 5 Plan hash value: 3957835817 -------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 551 (100)| | 1 | SORT AGGREGATE | | 1 | 8 | | |* 2 | TABLE ACCESS FULL| TEST_SPF | 204K| 1593K| 551 (2)| -------------------------------------------------------------------------- 정상적인 상황이라면 id = 5인 데이터를 조회 하기 위해서는 Full Table Scan이 가장 효과적이라고 판단을 할 것이다. 그러나 다음과 같이 수정 할 수 없는 SQL이 실행 되고 있다고 가정 한다면, 어떻게 Plan을 변경하여서 효과적으로 SQL이 수행되게 할 것인가 ? SCOTT@ora11g 11-JUL-16> SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5 ; SUM(AMOUNT) ----------- 1.0010E+12 SCOTT@ora11g 11-JUL-16> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost outline predicate bytes')) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 1frkbg9ft1qpg, child number 0 ------------------------------------- SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5 Plan hash value: 2534838301 -------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1014 (100)| | 1 | SORT AGGREGATE | | 1 | 8 | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST_SPF | 204K| 1593K| 1014 (1)| |* 3 | INDEX RANGE SCAN | IDX_TEST_SPF_ID | 204K| | 634 (1)| -------------------------------------------------------------------------------------- |
1. SQL Tuning Advisor 수행
SCOTT 유저가 DBMS_SQLTUNE 패키지를 이용할 수 있게 ADVISOR 권한을 주자. SYS@ora11g 11-JUL-16> GRANT advisor TO scott ; SCOTT 유저에서 SQL Tuning Advisor 수행한다. SCOTT@ora11g 11-JUL-16> DECLARE tempstr VARCHAR2(500); task_id VARCHAR2(200); BEGIN tempstr := 'SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5'; task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text=>tempstr, task_name=>'SQLPROFILE1'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK('SQLPROFILE1'); END; /
PL/SQL procedure successfully completed. 수행 된 결과를 확인하기 위하여 Tuning Advisor Report 확인 SCOTT@ora11g 11-JUL-16> set long 5000 SCOTT@ora11g 11-JUL-16> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROFILE1') FROM dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROFILE1') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : SQLPROFILE1 Tuning Task Owner : SCOTT Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 07/11/2016 17:26:02 Completed at : 07/11/2016 17:26:04 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 1qv1qf7426wkh SQL Text : SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 60.55%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'SQLPROFILE1', task_owner => 'SCOTT', replace => TRUE);
Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved --------------------- --------------- -------------- ----------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .190088 .050952 73.19 % CPU Time (s): .190011 .050803 73.26 % User I/O Time (s): 0 0 Buffer Gets: 4950 1955 60.5 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 6 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 2534838301 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 4963 (1)| 00:01:00 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST_SPF | 204K| 1593K| 4963 (1)| 00:01:00 | |* 3 | INDEX RANGE SCAN | IDX_TEST_SPF_ID | 1000K| | 3100 (1)| 00:00:38 | ------------------------------------------------------------------------------------------------ ---------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=5) 2- Using SQL Profile -------------------- Plan hash value: 3957835817 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 551 (2)| 00:00:07 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| TEST_SPF | 204K| 1593K| 551 (2)| 00:00:07 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=5) ------------------------------------------------------------------------------- |
2. SQL Profile 사용 설정
위의 Advisor 리포트를 보면 SQL Profile을 사용하여 변경 된 Plan으로 실행 할 경우, 수행 시간과 CPU 사용 시간이 73% 정도 줄어 드는 것을 확인 할 수 있다. 그럼 해당 SQL이 수행 될 때 어떻게 SQL Profile을 사용하게 할 것인가 ? 위의 리포트에 친절하게 그 답이 나와 있다.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQLPROFILE1', task_owner => 'SCOTT', replace => TRUE);
실제 SQL Profile을 적용 후 변경 된 Plan으로 SQL이 실행 되는지 확인해 보자. SYS 유저로 다음 명령어 수행
SYS@ora11g 11-JUL-16> execute dbms_sqltune.accept_sql_profile(task_name => 'SQLPROFILE1', task_owner => 'SCOTT', replace => TRUE);
PL/SQL procedure successfully completed.
SCOTT@ora11g 13-JUL-16> set autotrace on explain
SCOTT@ora11g 13-JUL-16> SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5 ;
SUM(AMOUNT)
-----------
1.0010E+12
Execution Plan
----------------------------------------------------------
Plan hash value: 3957835817
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 551 (2)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TEST_SPF | 204K| 1593K| 551 (2)| 00:00:07 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=5)
Note
-----
- SQL profile "SYS_SQLPROF_0155d91a21150000" used for this statement
의도한대로 SQL Profile을 이용하여 SQL Plan이 변경 되어서 수행 되었음을 확인해 볼 수 있다.
프로파일 정보 조회 (DBA_SQL_PROFILES)
SYS@ora11g 13-JUL-16> SELECT NAME , SQL_TEXT , CREATED , STATUS FROM DBA_SQL_PROFILES ;
NAME SQL_TEXT CREATED STATUS
--------------------------- -------------------------------------------------------------- ---------------------------- -----------
SYS_SQLPROF_0155d91a21150000 SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amou 11-JUL-16 05.36.56.000000 PM ENABLED
3. SQL Profile 비활성화/삭제
더 이상 SQL Profile을 사용하지 않을 경우 Disable 시키는 방법과 Drop 시키는 방법이 있다. 상황에 맞는 방법을 사용하면 된다.
비활성화
SYS@ora11g 13-JUL-16> SELECT name , status , dbms_lob.substr( sql_text, dbms_lob.getlength(sql_text), 1) sql_text FROM dba_sql_profiles ;
NAME STATUS SQL_TEXT
----------------------------- ---------- ------------------- -------- ------------------------------------------------------------------
SYS_SQLPROF_0155d91a21150000 ENABLED SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5
SYS@ora11g 13-JUL-16> exec bms_sqltune.alter_sql_profile(
name=>'SYS_SQLPROF_0155d91a21150000',
attribute_name=>'STATUS',
value=>'DISABLED') ;
PL/SQL procedure successfully completed.
SYS@ora11g 13-JUL-16>SELECT name , created , status , dbms_lob.substr( sql_text, dbms_lob.getlength(sql_text), 1) sql_text FROM dba_sql_profiles ;
NAME STATUS SQL_TEXT
----------------------------- ---------- ------------------- -------- ------------------------------------------------------------------
SYS_SQLPROF_0155d91a21150000 DISABLED SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5
SCOTT@ora11g 13-JUL-16> set autotrace on explain
SCOTT@ora11g 13-JUL-16> SELECT /*+ INDEX( test_spf , idx_test_spf_id ) */ sum(amount) FROM test_spf WHERE id = 5 ;
SUM(AMOUNT)
-----------
1.0010E+12
Execution Plan
----------------------------------------------------------
Plan hash value: 2534838301
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1014 (1)| 00:00:13 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_SPF | 204K| 1593K| 1014 (1)| 00:00:13 |
|* 3 | INDEX RANGE SCAN | IDX_TEST_SPF_ID | 204K| | 634 (1)| 00:00:08 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=5)
삭제
SYS@ora11g 13-JUL-16> exec dbms_sqltune.drop_sql_profile(name=>'SYS_SQLPROF_0155d91a21150000') ;
PL/SQL procedure successfully completed.
SYS@ora11g 13-JUL-16> SELECT name, status, dbms_lob.substr( sql_text, dbms_lob.getlength(sql_text), 1) sql_text FROM dba_sql_profiles ;
no rows selected
4. Profile 이동
프로파일을 다른 데이터 베이스로 옮기고자 할 경우, 예를 들어 테스트 환경에서 운영환경으로 이전을 하거나 그 반대의 경우를 원할 경우, 프로파일을 테이블에 데이터 저장하듯이 저장 한 후에
해당 테이블의 실제 데이터 옮기는 것처럼 데이터 펌프나 DB Link를 이용하여 데이터를 옮기면 된다.
Source DB |
Target DB |
1. 프로파일 데이터를 저장할 Staging 테이블 생성 SYS@ora11g 13-JUL-16> exec dbms_sqltune.create_stgtab_sqlprof ( table_name => 'P_STGTAB' , schema_name => 'scott') ; PL/SQL procedure successfully completed. 테이블이 실제로 만들어 졌는지 scott 유저로 확인해 보자. SCOTT@ora11g 13-JUL-16> SELECT * FROM tab ; TNAME TABTYPE CLUSTERID --------------- ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE P_STGTAB TABLE SALGRADE TABLE TEST_SPF TABLE 2. Staging 테이블에 SQL Profile 데이터 넣기 SYS@ora11g 13-JUL-16> exec dbms_sqltune.pack_stgtab_sqlprof( profile_name => '%' , staging_table_name => 'P_STGTAB' , staging_schema_owner => 'scott') ; PL/SQL procedure successfully completed. P_STGTAB 테이블을 Target DB로 옮김 ( 데이터 펌프 또는 DB Link 이용)
|
|
|
Scott 유저에 P_STGTAB 테이블로 데이터 이전 후, 다음 명령어를 이용하여 SQL Profile 적용. SYS@ora11g 13-JUL-16> exec dbms_sqltune.unpack_stgtab_sqlprof( replace => true , staging_table_name => 'P_STGTAB' , staging_schema_owner => 'scott') ; PL/SQL procedure successfully completed. SYS@ora11g 13-JUL-16> SELECT name FROM dba_sql_profiles ; NAME --------------------------- SYS_SQLPROF_0155e2940b900002
|