共计 14915 个字符，预计需要花费 38 分钟才能阅读完成。
我们都希望 Oracle 数据库的执行的 SQL，CBO 都能够产生正确的执行计划，但是事实上由于各种原因（例如 SQL 所对应的对应的统计信息不准确，或者 CBO 内部一些计算公式的缺陷等），导致了 CBO 会产生效率不高的，甚至是错误的执行计划。特别是 CBO 对目标 SQL 所产生的初始执行计划是正确的，后来由于各种原因（比如统计信息的变更），导致了 CBO 重新产生了一个错误的执行计划，这种执行计划的改变往往会导致目标 SQL 执行时间呈一个数量级的递增，而且通常会给我们造成一个困惑，一条 SQL 原本可以正常的运行，但是为什么会突然变得很慢？其实这种 SQL 执行效率突然的衰减往往是因为目标 SQL 执行计划的改变。这时候我们可以使用 SQL_Profile 或者 SPM 来解决执行计划变更的问题，用他们来调整稳定目标的 SQL 执行计划。下面进行一个 Automatic 的 SQL Profile 来稳定执行计划的实验。
1. 创建一个测试表并插入数据，并创建相对应的索引
SQL> create table t1(n number);
Table created.
SQL> declare
2 begin
3 for i in 1 .. 10000
4 loop
5 insert into t1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
———-
10000
SQL> create index idx_t1 on t1(n);
Index created.
2. 对表 T1 收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname =>’SYS’,tabname =>’T1′,method_opt =>’for all columns size 1′,CASCADE =>true);
PL/SQL procedure successfully completed.
3. 使用 hint 强制不使用索引，来模拟那些执行计划错误的 SQL，并查看执行计划。
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
———-
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 1kg76709mx29d, child number 0
————————————-
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 3617692013
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
——————————————————————————–
————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “T1″@”SEL$1”)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
——————————————————————————–
—————————————————
1 – filter(“N”=1)
Column Projection Information (identified by operation id):
———————————————————–
1 – “N”[NUMBER,22]
42 rows selected.
从上面的内容我们不难发现，这条 sql 语句所走的是全表扫描，但是这显然是个错误的执行计划，正确的执行计划，我们应该是走索引。
我们现在使用 SQL Tuning Advisor 来尝试对这条 SQL 进行通过产生 Automatic 类型的 SQL Profile
4. 创建一个名为 my_sql_tuning_task2 的自动调整任务
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext :=’select /*+ no_index(t1 idx_t1) */ * from t1 where n=1′;
6 my_task_name := dbms_sqltune.create_tuning_task(
7 sql_text => my_sqltext,
8 user_name => ‘SYS’,
9 scope => ‘COMPREHENSIVE’,
10 time_limit => 60,
11 task_name => ‘my_sql_tuning_task_2’,
12 description =>’TASK to tune a query on table t1′);
13 END;
14 /
PL/SQL procedure successfully completed.
然后执行上述自动调整任务
SQL> begin
2 dbms_sqltune.execute_tuning_task(task_name => ‘my_sql_tuning_task_2’);
3 end;
4 /
PL/SQL procedure successfully completed.
然后我们就可以使用 DBMS_SQLTUNE.REPORT_TUNING_TASK 来查看上述自动调整任务的调整结果：
SQL> set long 9000
SQL> set longchunksize 1000
SQL> set linesize 800
SQL> select dbms_sqltune.report_tuning_task(‘my_sql_tuning_task_2’) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
————————————————————————————————-
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 04/13/2016 23:08:28
Completed at : 04/13/2016 23:08:28
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
—————————————————————————————————
Schema Name: SYS
SQL ID : 4bh6sn1zvpgq7
SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-
1- SQL Profile Finding (see explain plans section below)
——————————————————–
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
—————————————————————————–
Recommendation (estimated benefit: 90.91%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘my_sql_tuning_task_2’, task_owner => ‘SYS’, replace => TRUE);
Validation results
——————
The SQL profile was tested by executing both its plan and the original plan
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
————————————————————————————————
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
—–
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
————————————————————————————-
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original With Adjusted Cost
——————————
Plan hash value: 3617692013
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“N”=1)
2- Using SQL Profile
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
————————————————————————————————-
Plan hash value: 1369807930
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
————————————————————————————————
1 – access(“N”=1)
——————————————————————————-
从上面的调整结果，我们可以看到，他已经为我们目标 SQL 找到了更好的执行计划，并且也完成了针对该 SQL 的 Automatic 类型的 SQL Profile，如果我们使用 execute dbms_sqltune.accept_sql_profile(task_name => ‘my_sql_tuning_task_2’, task_owner => ‘SYS’, replace => TRUE);
相应时间将会有 89.9% 的改善，逻辑读将会有 90.9 % 的改善，并且接受后将会有全表扫描改变为 IDX_T1 的索引范围扫描。
然后我们按照 oracle 提示接受这个 SQL profile，并重新查看执行计划
SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘my_sql_tuning_task_2’, task_owner => ‘SYS’, replace => TRUE);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
———-
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID 1kg76709mx29d, child number 0
————————————-
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 1369807930
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
—————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
PLAN_TABLE_OUTPUT
———————————————————————————————————–
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
————————————————
1 – access(“N”=1)
Column Projection Information (identified by operation id):
———————————————————–
1 – “N”[NUMBER,22]
Note
—–
PLAN_TABLE_OUTPUT
————————————————-
– SQL profile SYS_SQLPROF_0154103a51870000 used for this statement
46 rows selected.
我们可以看到 Note 部分 SQL profile SYS_SQLPROF_0154103a51870000 used for this statement，这说明我们刚才接受的 SQL Profile 已经生效了，这同时也说明 Automatic 类型的 SQL Profile 确实可以再不改变目标 SQL 的 SQl 文本的情况下更改其执行计划
接下来我们尝试将 where 的条件从 n = 1 改变为 n =2, 并查看执行计划 SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
———-
2
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 36wrvgrswajnh, child number 0
————————————-
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 3617692013
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
——————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “T1″@”SEL$1”)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
——————————————————————————–
1 – filter(“N”=2)
Column Projection Information (identified by operation id):
———————————————————–
1 – “N”[NUMBER,22]
42 rows selected.
我们发现还是走了全表扫描，要想使上面的 SQL_PROFILE 生效，我们需要加上 FORCE_MATCH=TRUE,true 的含义，就是 where 条件中值发生变化，但是 SQL_Profile 仍然有效
SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘my_sql_tuning_task_2’, task_owner => ‘SYS’, replace => TRUE,force_match => true);
PL/SQL procedure successfully completed.
再次查看相对应的执行计划
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
———-
2
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID c4j6hxkqudj1s, child number 0
————————————-
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 1369807930
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
——————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
——————————————————————————–
1 – access(“N”=2)
Column Projection Information (identified by operation id):
———————————————————–
1 – “N”[NUMBER,22]
Note
—–
PLAN_TABLE_OUTPUT
———————————————
– SQL profile SYS_SQLPROF_015410470fa40001 used for this statement
46 rows selected.
这是我们可以发现这次的执行计划走的是索引，为了再次验证新生成的 SQL_Profile 对其他值也有效，我们再次尝试 n =3
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=3;
N
———-
3
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 0zz8t0qnm15hj, child number 0
————————————-
select /*+ no_index(t1 idx_t1) */ * from t1 where n=3
Plan hash value: 1369807930
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
——————————————————————————–
—————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)
DB_VERSION(‘11.2.0.4’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
——————————————————————————–
—————————————————
1 – access(“N”=3)
Column Projection Information (identified by operation id):
———————————————————–
1 – “N”[NUMBER,22]
Note
—–
PLAN_TABLE_OUTPUT
——————————————————————————–
– SQL profile SYS_SQLPROF_015410470fa40001 used for this statement
46 rows selected.
结论是仍旧有效。
–End.
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-04/130334.htm
