sql_tune advisor的使用，sql_tuneadvisor

sql_tune advisor的使用，sql_tuneadvisor

我們現在建立一個測試表，看此時正確的執行計畫13:11:53 scott@orcl> select * from t2 where empno=200;Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 2008370210--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=200)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size       1092  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed我們使用hint來強制走一個錯誤的執行計畫13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=200)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets         47  consistent gets          0  physical reads          0  redo size       1088  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed現在我們使用sqltune advisor來進行調整建立TUNING_TASK並執行declare  l_task_name varchar2(30);  l_sql       clob;begin  l_sql       := 'select /*+ full(t2) */ * from t2 where empno=200';  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => l_sql,                                                 user_name   => 'SCOTT',                                                 scope       => 'COMPREHENSIVE',                                                 time_limit  => 60,                                                 task_name   => 'test01',                                                 description => null);end;/time_limit：執行的最長時間，預設是60。scope：LIMITED，用大概1秒時間去最佳化SQL語句，但是並不進行SQL Profiling分析。COMPREHENSIVE，進行全面分析，包含SQL Profiling分析；比LIMITED用時更長。**也可以用sql_id建立sql tunning任務，比用sql_text方便很多FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2 Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_ID                         VARCHAR2                IN PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT SCOPE                          VARCHAR2                IN     DEFAULT TIME_LIMIT                     NUMBER                  IN     DEFAULT TASK_NAME                      VARCHAR2                IN     DEFAULT DESCRIPTION                    VARCHAR2                IN     DEFAULTDECLARE my_task_name VARCHAR2(30);BEGIN  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(         SQL_ID      => 'ddw7j6yfnw0vz',         scope       => 'COMPREHENSIVE',         time_limit  => 60,         task_name   => 'tunning_task_ddw7j6yfnw0vz',          description => 'Task to tune a query on  ddw7j6yfnw0vz');END;/ 我們查看此時任務的狀態13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';TASK_NAME                      EXECUTION_START     EXECUTION_END       STATUS------------------------------ ------------------- ------------------- -----------test01                                                                 INITIAL執行sql tuning任務BEGIN  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );END;/展示sql tunning結果SET LONG 10000SET LONGCHUNKSIZE 1000SET LINESIZE 100SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')FROM   DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : test01Tuning Task Owner  : SCOTTWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 60Completion Status  : COMPLETEDStarted at         : 12/21/2014 13:29:11Completed at       : 12/21/2014 13:29:15-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID     : 3bgc9fc2fp597SQL Text   : select /*+ full(t2) */ * from t2 where empno=200-------------------------------------------------------------------------------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: 93.46%)  ------------------------------------------  - Consider accepting the recommended SQL profile.    execute dbms_sqltune.accept_sql_profile(task_name => 'test01', 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):             .000378           .000098      74.07 %  CPU Time (s):                 .000299           .000099      66.88 %  User I/O Time (s):                  0                 0  Buffer Gets:                       46                 3      93.47 %  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 10 executions.  2. Statistics for the SQL profile plan were averaged over 10 executions.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original With Adjusted Cost------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=200)2- Using SQL Profile--------------------Plan hash value: 2008370210--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=200)-------------------------------------------------------------------------------我們可以看到他提供的建議，執行sql_profile，我們根據他的建議執行這個profileexecute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);然後我們再來執行下原來的帶hint的語句select /*+ full(t2) */ * from t2 where empno=200;13:39:32 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 2008370210--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=200)Note-----   - SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size       1092  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed可以看到使用了sql_profile 從而走了正確的執行計畫現在我們再來看看其他的情況，我們原來的表上沒有索引，看看tune advisor能提供什麼樣的建議13:42:44 scott@orcl> select * from t4 where empno=200;Elapsed: 00:00:00.04Execution Plan----------------------------------------------------------Plan hash value: 2560505625--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=200)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------         14  recursive calls          0  db block gets        114  consistent gets         50  physical reads          0  redo size       1088  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed此時表是沒有索引的，走的全表掃描我們使用sql_id的方式來建立task13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';SQL_TEXT                                                     SQL_ID------------------------------------------------------------ -------------select * from t4 where empno=200                             5avs113b5fn8vDECLARE my_task_name VARCHAR2(30);BEGIN  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(         SQL_ID      => '5avs113b5fn8v',         scope       => 'COMPREHENSIVE',         time_limit  => 60,         task_name   => 'tunning_task_5avs113b5fn8v',          description => 'Task to tune a query on  5avs113b5fn8v');END;/ 啟動這個taskBEGIN  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );END;/查看reportSET LONG 10000SET LONGCHUNKSIZE 1000SET LINESIZE 100SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')FROM   DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : tunning_task_5avs113b5fn8vTuning Task Owner  : SCOTTWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 60Completion Status  : COMPLETEDStarted at         : 12/21/2014 13:48:02Completed at       : 12/21/2014 13:48:03-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID     : 5avs113b5fn8vSQL Text   : select * from t4 where empno=200-------------------------------------------------------------------------------FINDINGS SECTION (2 findings)-------------------------------------------------------------------------------1- Statistics Finding---------------------  Table "SCOTT"."T4" was not analyzed.  Recommendation  --------------  - Consider collecting optimizer statistics for this table.    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>            'T4', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt            => 'FOR ALL COLUMNS SIZE AUTO');  Rationale  ---------    The optimizer requires up-to-date statistics for the table in order to    select a good execution plan.2- Index Finding (see explain plans section below)--------------------------------------------------  The execution plan of this statement can be improved by creating one or more  indices.  Recommendation (estimated benefit: 86.7%)  -----------------------------------------  - Consider running the Access Advisor to improve the physical schema design    or creating the recommended index.    create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");  Rationale  ---------    Creating the recommended indices significantly improves the execution plan    of this statement. However, it might be preferable to run "Access Advisor"    using a representative SQL workload as opposed to a single statement. This    will allow to get comprehensive index recommendations which takes into    account index maintenance overhead and additional space consumption.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 2560505625--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=200)2- Using New Indices--------------------Plan hash value: 3508715929----------------------------------------------------------------------------------------------| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                |     1 |   100 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T4             |     1 |   100 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX$$_00540001 |     1 |       |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=200)-------------------------------------------------------------------------------可以看到 sql_tune advisor提供了建議在empno 列上面建立索引，可見分析的還是很準確的刪除tune_tastEXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');其他--sql tunning任務建立後，也可以修改參數BEGIN  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(    task_name => 'test_sql_tuning',    parameter => 'TIME_LIMIT', value => 300);END;/--查看SQL Tuning Advisor的進展（task執行很久）col opname for a20col ADVISOR_NAME for a20SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK FROM   V$ADVISOR_PROGRESS WHERE  USERNAME = 'TEST';