Oracle SQL优化之sql tuning advisor(STA)(三)
设计的访问指导或者创建推荐的索引。
create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME");
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运
行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护
的开销和附加的空间消耗。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 380737209
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | | 5063 (1)| 00:01:01 |
| 1 | SORT AGGREGATE | | 1 | 83 | | | |
|* 2 | HASH JOIN | | 5861K| 463M| 1272K| 5063 (1)| 00:01:01 |
| 3 | TABLE ACCESS FULL| IND | 44789 | 743K| | 379 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 577K| 36M| | 2472 (1)| 00:00:30 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
2- Using New Indices
--------------------
Plan hash value: 4048334321
---------------------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 83 | | 1228 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | 83 | | |
|
| 2 | MERGE JOIN | | 5861K| 463M| | 1228 (2)| 00:00:15 |
| 3 | INDEX FULL SCAN | IDX$$_00790001 | 577K| 36M| | 944 (1)| 00:00:12 |
|* 4 | SORT JOIN | | 44789 | 743K| 2120K| 268
(1)| 00:00:04 |
| 5 | INDEX FAST FULL SCAN| IDX$$_00790002 | 44789 | 743K| | 18 (0)| 00:00:01 |
----------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
filter("O"."OBJECT_NAME"="I"."INDEX_NAME")
报告如上，此报告主要包含建议的索引和前后执行计划对比，可见效果还是可以的。
五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');