|
--????
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????м??
|
sales@spasvo.com