SELECT t1.table_name FROM DBA_TABLE T1,DBA_TABLE_BAK T2 WHERE T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER='TEST1'
用于做测试的SQL。两张表第一张500万，第二张50万。
用DBMS_SQLTUNE包得到
如下：
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_1361
Tuning Task Owner : TEST1
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 07/14/2014 23:40:11
Completed at : 07/14/2014 23:41:51
Number of Statistic Findings : 1
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST1
SQL ID : 81b1x7jgp45a6
SQL Text : SELECT t1.table_name FROM DBA_TABLE T1,DBA_TABLE_BAK T2 WHERE
T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER='TEST1'
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "TEST1"."DBA_TABLE_BA
Recommendation
--------------
- 考虑收集此表的优
execute dbms_stats.gather_table_stats(ownname => 'TEST1', tabname =>
'DBA_TABLE_BAK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需
2- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以
Recommendation (estimated benefit: 100%)
----------------------------------------
-考虑运行可以改进物理方案设计的 Access Advi
create index TEST1.IDX$$_05510001 on TEST1.DBA_TABLE_BAK('TABLE_NAME');
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的
可能比单个语句更可取。通过这种方法可以获得全面的索引建
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2664171826
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86M| 3129M| | 21472 (8)| 00:04:18 |
|* 1 | HASH JOIN | | 86M| 3129M| 11M| 21472 (8)| 00:04:18 |
| 2 | TABLE ACCESS FULL| DBA_TABLE_BAK | 397K| 6606K| | 2659 (1)| 00:00:32 |
|* 3 | TABLE ACCESS FULL| DBA_TABLE | 422K| 8669K| | 16103 (2)| 00:03:14 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------