|
--环境
scott@ORA11G> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--创建演示表
scott@ORA11G> CREATE TABLE t
2 NOLOGGING
3 AS
4 SELECT *
5 FROM dba_source,
6 ( SELECT *
7 FROM DUAL
8 CONNECT BY ROWNUM < 5);
Table created.
--执行SQL 语句
scott@ORA11G> SELECT COUNT (*)
2 FROM t a
3 WHERE a.ROWID > (SELECT MIN (b.ROWID)
4 FROM t b
5 WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);
COUNT(*)
----------
1872756
1 row selected.
--开始SQL自动调整并报告结果
--脚本tune_last_sql.sql中包含了创建调优任务、开始执行调优、以及报告调优成果。脚本内容见文章尾部
scott@ORA11G> @tune_last_sql
RECS
-----------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_833
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/22/2013 15:06:06
Completed at : 05/22/2013 15:07:17
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 44tg722u0ypqh
SQL Text : SELECT COUNT (*)
FROM t a
WHERE a.ROWID > (SELECT MIN (b.ROWID)
FROM t b
WHERE a.owner = b.owner AND a.name = b.name
AND a.TYPE = b.TYPE AND a.line = b.line)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1985065416
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 134 | | 42648 (1)| 00:08:32 |
| 1 | SORT AGGREGATE | | 1 | 134 | | | |
|* 2 | HASH JOIN | | 129K| 16M| 195M| 42648 (1)| 00:08:32 |
| 3 | TABLE ACCESS FULL | T | 2590K| 165M| | 11596 (1)| 00:02:20 |
| 4 | VIEW | VW_SQ_1 | 2590K| 165M| | 11674 (1)| 00:02:21 |
| 5 | HASH GROUP BY | | 2590K| 165M| | 11674 (1)| 00:02:21 |
| 6 | TABLE ACCESS FULL| T | 2590K| 165M| | 11596 (1)| 00:02:20 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
"A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
filter("A".ROWID>"MIN(B.ROWID)")
--上面的report总共分为3个部分，分别是SQL调优的基本信息、SQL调优的建议findings、以及SQL对应的执行计划部分
--在基本信息部分包含了SQL调优的任务名称，状态，执行，完成时间，对应的SQL完整语句等
--在finding部分则给出本次调优所得到的成果，如本次是提示缺少统计信息
--在执行计划部分则给出了当前SQL语句的执行计划以及谓词信息
-->接下来根据建议来收集统计信息
scott@ORA11G> BEGIN
2 DBMS_STATS.gather_table_stats (ownname => 'SCOTT',
3 tabname => 'T',
4 estimate_percent => DBMS_STATS.auto_sample_size,
5 method_opt => 'FOR ALL COLUMNS SIZE AUTO');
6 END;
7 /
PL/SQL procedure successfully completed.
-->对原SQL语句增加order提示并执行
scott@ORA11G> SELECT /*+ ordered */COUNT (*)
2 FROM t a
3 WHERE a.ROWID > (SELECT MIN (b.ROWID)
4 FROM t b
5 WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);
COUNT(*)
----------
1872756
1 row selected.
--再次调优SQL语句
scott@ORA11G> @tune_last_sql
RECS
-----------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_849
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/22/2013 21:26:07
Completed at : 05/22/2013 21:26:42
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : fsp3852n56gf8
SQL Text : SELECT /*+ ordered */COUNT (*)
FROM t a
WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b
WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE
AND a.line = b.line)
-------------------------------------------------------------------------------
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: 67.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_849',
task_owner => 'SCOTT', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2929971977
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 218K (1)| 00:43:47 |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | VIEW | VM_NWVW_2 | 551K| | | 218K (1)| 00:43:47 |
|* 3 | FILTER | | | | | | |
| 4 | HASH GROUP BY | | 551K| 51M| 1197M| 218K (1)| 00:43:47 |
|* 5 | HASH JOIN | | 11M| 1031M| 145M| 37646 (1)| 00:07:32 |
| 6 | TABLE ACCESS FULL| T | 2497K| 116M| | 11596 (1)| 00:02:20 |
| 7 | TABLE ACCESS FULL| T | 2497K| 116M| | 11596 (1)| 00:02:20 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A".ROWID>MIN("B".ROWID))
5 - access("A"."OWNER"="B"."OWNER" AND "A"."NAME"="B"."NAME" AND
"A"."TYPE"="B"."TYPE" AND "A"."LINE"="B"."LINE")
2- Using SQL Profile
--------------------
Plan hash value: 1985065416
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 116 | | 70117 (1)| 00:14:02 |
| 1 | SORT AGGREGATE | | 1 | 116 | | | |
|* 2 | HASH JOIN | | 2025K| 224M| 145M| 70117 (1)| 00:14:02 |
| 3 | TABLE ACCESS FULL | T | 2497K| 116M| | 11596 (1)| 00:02:20 |
| 4 | VIEW | VW_SQ_1 | 2497K| 159M| | 41851 (1)| 00:08:23 |
| 5 | HASH GROUP BY | | 2497K| 116M| 153M| 41851 (1)| 00:08:23 |
| 6 | TABLE ACCESS FULL| T | 2497K| 116M| | 11596 (1)| 00:02:20 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
"A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
filter("A".ROWID>"MIN(B.ROWID)")
-------------------------------------------------------------------------------
--针对上述的SQL语句，SQL调优器找到了一个更为高效的执行计划，并提示我们接受该执行计划，如下
--A potentially better execution plan was found for this statement.
--Recommendation (estimated benefit: 67.95%)
--Consider accepting the recommended SQL profile
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
--接受SQL profile
scott@ORA11G> exec DBMS_SQLTUNE.accept_sql_profile (task_name => 'TASK_849', task_owner => 'SCOTT', REPLACE => TRUE);
PL/SQL procedure successfully completed.
--当接受SQL profile后，我们再次来执行原来带order提示的SQL语句
scott@ORA11G> set autot trace exp;
scott@ORA11G> SELECT /*+ ordered */COUNT (*)
2 FROM t a
3 WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b
4 WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE
5 AND a.line = b.line);
Execution Plan
----------------------------------------------------------
Plan hash value: 1985065416
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 116 | | 70117 (1)| 00:14:02 |
| 1 | SORT AGGREGATE | | 1 | 116 | | | |
|* 2 | HASH JOIN | | 2025K| 224M| 145M| 70117 (1)| 00:14:02 |
| 3 | TABLE ACCESS FULL | T | 2497K| 116M| | 11596 (1)| 00:02:20 |
| 4 | VIEW | VW_SQ_1 | 2497K| 159M| | 41851 (1)| 00:08:23 |
| 5 | HASH GROUP BY | | 2497K| 116M| 153M| 41851 (1)| 00:08:23 |
| 6 | TABLE ACCESS FULL| T | 2497K| 116M| | 11596 (1)| 00:02:20 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
"A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
filter("A".ROWID>"MIN(B.ROWID)")
Note
-----
- SQL profile "SYS_SQLPROF_013ecc70b5f70000" used for this statement
scott@ORA11G> set autot off;
--上面的autotrace中，最后一部分表明当前的SQL语句使用了存储的SQL profile的执行计划 | 
