我们现在创建一个测试表，看此时正确的执行计划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 calls0 db block gets4 consistent gets0 physical reads0 redo size1092 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 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 calls0 db block gets47 consistent gets0 physical reads0 redo size1088 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed现在我们使用sqltune advisor来进行调整创建TUNING_TASK并执行declare l_task_name varchar2(30); l_sqlclob;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 NameTypeIn/Out Default? —————————— ———————– —— ——– SQL_IDVARCHAR2IN PLAN_HASH_VALUENUMBERINDEFAULT SCOPEVARCHAR2INDEFAULT TIME_LIMITNUMBERINDEFAULT TASK_NAMEVARCHAR2INDEFAULT DESCRIPTIONVARCHAR2INDEFAULTDECLARE 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_NAMEEXECUTION_STARTEXECUTION_ENDSTATUS—————————— ——————- ——————- ———–test01INITIAL执行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:COMPLETECOMPLETE Elapsed Time (s):.000378.00009874.07 % CPU Time (s):.000299.00009966.88 % User I/O Time (s):00 Buffer Gets:46393.47 % Physical Read Requests:00 Physical Write Requests:00 Physical Read Bytes:00 Physical Write Bytes:00 Rows Processed:11 Fetches:11 Executions:11Notes —– 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 calls0 db block gets4 consistent gets0 physical reads0 redo size1092 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 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 calls0 db block gets114 consistent gets50 physical reads0 redo size1088 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 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_TEXTSQL_ID———————————————————— ————-select * from t4 where empno=2005avs113b5fn8vDECLARE 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 toselect 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 designor creating the recommended index.create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");Rationale ———Creating the recommended indices significantly improves the execution planof this statement. However, it might be preferable to run "Access Advisor"using a representative SQL workload as opposed to a single statement. Thiswill allow to get comprehensive index recommendations which takes intoaccount 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';
不给自己一点轻松的机会，好象世界的每个角落都需要自己的脚去留个痕迹，才叫人生。
