This is something I want to share,
---------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : TEST
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 07/20/2007 15:05:41
Completed at : 07/20/2007 15:07:26
Number of SQL Profile Findings : 1
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 6807a6a565pc0
SQL Text : SELECT p.cover_start_date, p.cover_end_date FROM policy_hist p WHERE p.scheme_id = :1 AND p.endorse_no = (SELECT
max(p1.endorse_no) - 1 FROM policy_hist p1, policy_hist p2 WHERE
p1.scheme_id = p.scheme_id AND p1.cover_start_date != p2.cover_start_date AND p1.scheme_id = p2.scheme_id)
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task', replace => TRUE);
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: 99.97%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
create index LIVE.IDX$$_2FBE0001 on LIVE.POLICY_HIST('SCHEME_ID');
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index. Create index LIVE.IDX$$_2FBE0002 on LIVE.POLICY_HIST ('SCHEME_ID','ENDORSE_N
O');
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 With Adjusted Cost
------------------------------
Plan hash value: 4066447297
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | | 37718 (8)| 00:07:33 |
|* 1 | FILTER | | | | | | |
|* 2 | TABLE ACCESS FULL | POLICY_HIST | 83 | 1743 | | 12556 (8)| 00:02:31 |
| 3 | SORT AGGREGATE | | 1 | 23 | | | |
| 4 | MERGE JOIN | | 689K| 15M| | 25162 (8)| 00:05:02 |
| 5 | SORT JOIN | | 8310 | 83100 | 344K| 12568 (8)| 00:02:31 |
|* 6 | TABLE ACCESS FULL | POLICY_HIST | 8310 | 83100 | | 12529 (8)| 00:02:31 |
|* 7 | FILTER | | | | | | |
|* 8 | SORT JOIN | | 7131 | 92703 | 344K| 12574 (8)| 00:02:31 |
|* 9 | TABLE ACCESS FULL| POLICY_HIST | 7131 | 92703 | | 12535 (8)| 00:02:31 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("P"."ENDORSE_NO"= (SELECT /*+ LEADING ("P2" "P1") FULL ("P2") USE_MERGE
("P1") FULL ("P1") */ MAX("P1"."ENDORSE_NO")-1 FROM "CLAS"."POLICY_HIST"
"P2","CLAS"."POLICY_HIST" "P1" WHERE "P1"."COVER_START_DATE"<>"P2"."COVER_START_DATE"
AND "P1"."SCHEME_ID"="P2"."SCHEME_ID" AND "P1"."SCHEME_ID"=:B1 AND "P2"."SCHEME_ID"
IS NOT NULL))
2 - filter("P"."SCHEME_ID"=TO_NUMBER(:1))
6 - filter("P2"."SCHEME_ID" IS NOT NULL)
7 - filter("P1"."COVER_START_DATE"<>"P2"."COVER_START_DATE")
8 - access("P1"."SCHEME_ID"="P2"."SCHEME_ID")
filter("P1"."SCHEME_ID"="P2"."SCHEME_ID")
9 - filter("P1"."SCHEME_ID"=:B1)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
---------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 867872509
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83 | 1743 | 37628 (8)| 00:07:32 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | POLICY_HIST | 83 | 1743 | 12556 (8)| 00:02:31 |
| 3 | SORT AGGREGATE | | 1 | 23 | | |
|* 4 | HASH JOIN | | 6898 | 154K| 25072 (8)| 00:05:01 |
|* 5 | TABLE ACCESS FULL| POLICY_HIST | 7131 | 71310 | 12535 (8)| 00:02:31 |
|* 6 | TABLE ACCESS FULL| POLICY_HIST | 7131 | 92703 | 12535 (8)| 00:02:31 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("P"."ENDORSE_NO"= (SELECT MAX("P1"."ENDORSE_NO")-1 FROM
"CLAS"."POLICY_HIST" "P2","CLAS"."POLICY_HIST" "P1" WHERE
"P1"."SCHEME_ID"=:B1 AND "P1"."COVER_START_DATE"<>"P2"."COVER_START_DATE"
AND "P1"."SCHEME_ID"="P2"."SCHEME_ID" AND "P2"."SCHEME_ID"=:B2))
2 - filter("P"."SCHEME_ID"=TO_NUMBER(:1))
4 - access("P1"."SCHEME_ID"="P2"."SCHEME_ID")
filter("P1"."COVER_START_DATE"<>"P2"."COVER_START_DATE")
5 - filter("P2"."SCHEME_ID"=:B1)
6 - filter("P1"."SCHEME_ID"=:B1)
3- Using New Indices
--------------------
Plan hash value: 3343551894
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83 | 3901 | 8 (13)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | POLICY_HIST | 83 | 1743 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 83 | 3901 | 8 (13)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 1 | 26 | 5 (20)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 23 | 5 (20)| 00:00:01 |
|* 5 | HASH JOIN | | 6898 | 154K| 5 (20)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| POLICY_HIST | 83 | 1079 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX$$_2FBE0001 | 83 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| POLICY_HIST | 83 | 830 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX$$_2FBE0001 | 83 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX$$_2FBE0002 | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("P1"."SCHEME_ID"="P2"."SCHEME_ID")
filter("P1"."COVER_START_DATE"<>"P2"."COVER_START_DATE")
7 - access("P1"."SCHEME_ID"=TO_NUMBER(:1))
9 - access("P2"."SCHEME_ID"=TO_NUMBER(:1))
10 - access("P"."SCHEME_ID"=TO_NUMBER(:1) AND "P"."ENDORSE_NO"="VW_COL_1")
-------------------------------------------------------------------------------
Wow, Not only it shows how to improve but also shows an explain plan after using indexes (for example).
Adith