Automatic的SQL Profile来稳定执行计划

145次阅读

共计 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

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

正文完