sql_tuneadvisor的使用(三)
ional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using New Indices
--------------------
Plan hash value: 3508715929
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00540001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建议在empno 列上面创建索引，可见分析的还是很准确的
删除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');
其他
--sql tunning任务创建后，也可以修改参数
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name =>
'test_sql_tuning',
parameter => 'TIME_LIMIT', value => 300);
END;
/
--查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';