-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : TEST
Scope : COMPREHENSIVE
Time Limit(seconds) : 10
Completion Status : COMPLETED
Started at : 05/23/2014 08:50:40
Completed at : 05/23/2014 08:50:41
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : afjq3us3nf5dt
SQL Text : select count(*) from test1 t1, test2 t2 where t1.object_id =
t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以
Recommendation (estimated benefit: 100%)
----------------------------------------
-考虑运行可以改进物理方案设计的 Access Advi
create index TEST.IDX$$_0C890001 on TEST.TEST1('OBJECT_ID');
-考虑运行可以改进物理方案设计的 Access Advi
create index TEST.IDX$$_0C890002 on TEST.TEST2('OBJECT_ID');
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的
可能比单个语句更可取。通过这种方法可以获得全面的索引建
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2544416891
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 298 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | HASH JOIN | | 50981 | 497K| 298 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| TEST1 | 50982 | 248K| 149 (1)| 00:00:02 |
| 4 | TABLE ACCESS FULL| TEST2 | 50983 | 248K| 149 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2- Using New Indices
--------------------
Plan hash value: 3060659111
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 53 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | HASH JOIN | | 50981 | 497K| 53 (2)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX$$_0C890001 | 50982 | 248K| 26 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_0C890002 | 50983 | 248K| 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
5.验证一下
根据advisor的建议进行调优，1410降到204，是乎有点效果。
SQL>create index ind_t1_object_id on test1(object_id);
SQL> create index ind_t2_object_id on test2(object_id);
SQL> set autotrace traceonly
SQL> select count