|
tion => '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 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test01
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:29:11
Completed at : 12/21/2014 13:29:15
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3bgc9fc2fp597
SQL 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 |
-------------- |