Listing 2
DBMS_SQLTUNE.REPORT_TUNING_TASK('FOLIO_COUNT')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : FOLIO_COUNT
Scope : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status : COMPLETED
Started at : 04/06/2004 01:01:31
Completed at : 04/06/2004 01:01:31
-------------------------------------------------------------------------------
SQL ID : 1mzhrcv0bg0pw
SQL Text: select account_no from accounts where old_account_no = 11
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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: 94.26%)
------------------------------------------
Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index ARUP.IDX$_00001 on ARUP.ACCOUNTS("OLD_ACCOUNT_NO")
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
-----------
DBMS_SQLTUNE.REPORT_TUNING_TASK('FOLIO_COUNT')
--------------------------------------------------------------------------------
Plan hash value: 290945073
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 34 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ACCOUNTS | 1 | 10 | 34 (0)| 00:00:01 |
------------------------------------------------------------------------------
2- Using New Indices
--------------------
Plan hash value: 633506680
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACCOUNTS | 1 | 10 | 2 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | IDX$_00001 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
|
