我们现在创建一个测试表，看此时正确的执行计划
13:11:53 scott@orcl> select * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们使用hint来强制走一个错误的执行计划
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
现在我们使用sqltune advisor来进行调整
创建TUNING_TASK并执行
declare
l_task_name varchar2(30);
l_sql clob;
begin
l_sql := 'select /*+ full(t2) */ * from t2 where empno=200';
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => l_sql,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test01',
description => null);
end;
/
time_limit：执行的最长时间，默认是60。
scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。
**也可以用sql_id创建sql tunning任务，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => 'ddw7j6yfnw0vz',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_ddw7j6yfnw0vz',
descrip