oracle如何捕获sql,[读基于oracle的sql优化笔记]Sql Plan Management-自动捕获示例

oracle 11g及其以上得版本中，有如下两种方法可以产生目标sql的sql plan baseline:

。自动捕获。

。手工生成、批量导入(批量导入尤其适用于oracle数据库大版本的升级，它可以确保升级后原有系统所有sql得执行计划不会变更)。

我们来看一个自动捕获sql plan baseline并据此来稳定执行计划的实例。

oracle 11g中optimizer_capture_sql_plan_baselines的默认值为false，optimizer_use_sql_plan_baselines的默认值为true：

22:10:16 SQL> show parameter sql_plan

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean     FALSE

optimizer_use_sql_plan_baselines     boolean     TRUE

在当前session中禁掉spm并同时开启自动捕获sql plan baseline：

22:16:51 SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

Elapsed: 00:00:00.00

22:17:35 SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

Elapsed: 00:00:00.00

创建测试表cap_spm表：

22:17:53 SQL> create table cap_spm as select * from dba_objects;

Table created.

Elapsed: 00:00:00.92

22:19:21 SQL> create index idx_cs on cap_spm(object_id);

Index created.

Elapsed: 00:00:00.23

22:23:13 SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'CAP_SPM',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.71

22:28:34 SQL>  select object_id,object_name from cap_spm where object_id between 20 and 28;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

20 ICOL$

21 COL$

22 USER$

23 PROXY_DATA$

24 I_PROXY_DATA$

25 PROXY_ROLE_DATA$

26 I_PROXY_ROLE_DATA$_1

27 I_PROXY_ROLE_DATA$_2

28 CON$

9 rows selected.

Elapsed: 00:00:00.04

22:30:57 SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

SQL_ID    3v3s3h87rj717, child number 0

-------------------------------------

select object_id,object_name from cap_spm where object_id between 20

and 28

Plan hash value: 3149005651

---------------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| CAP_SPM |     8 |   240 |     3    (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN        | IDX_CS  |     8 |       |     2    (0)| 00:00:01 |

---------------------------------------------------------------------------------------

从上述显示内容中可以看出，现在目标sql的执行计划走的是对索引IDX_CS的索引范围扫描。

因为目标sql只执行过一次，所以oracle现在不会自动捕获其sql plan baseline。从如下查询结果中可以看出，该sql限制确实没有对应的sql plan baseline：

22:37:33 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

no rows selected

Elapsed: 00:00:00.03

22:37:46 SQL> 23:14:42 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

20 ICOL$

21 COL$

22 USER$

23 PROXY_DATA$

24 I_PROXY_DATA$

25 PROXY_ROLE_DATA$

26 I_PROXY_ROLE_DATA$_1

27 I_PROXY_ROLE_DATA$_2

28 CON$

9 rows selected.

Elapsed: 00:00:00.06

其执行计划并未发现任何改变，走的还是对索引IDX_CS的索引范围扫描：

22:38:14 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

SQL_ID    3v3s3h87rj717, child number 0

-------------------------------------

select object_id,object_name from cap_spm where object_id between 20

and 28

Plan hash value: 3149005651

---------------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| CAP_SPM |     8 |   240 |     3    (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN        | IDX_CS  |     8 |       |     2    (0)| 00:00:01 |

---------------------------------------------------------------------------------------

因为目标sql已经重复执行，所以现在oracle就回自动捕获sql plan baseline了。从如下查询结果中可以看出，oracle已经针对上述执行计划(即对索引IDX_CS的索引范围扫描)产生了一个sql plan baseline，其enable和accepted的值均为“yes”：

22:38:45 SQL>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

SQL_HANDLE               PLAN_NAME              ORIGIN         ENA ACC SQL_TEXT

------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE   YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

Elapsed: 00:00:00.02

这里我们将索引IDX_CS的聚族因子修改为2400万，目的为了能让目标sql的执行计划为对表cap_spm的全部扫描：

22:46:30 SQL> exec dbms_stats.set_index_stats(ownname=>'SYS',indname=>'IDX_CS',clstfct=>24000000,no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

从如下查询结果里可以看出，索引idx_cs的聚族因子确实已修改为2400万：

22:48:31 SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_CS';

INDEX_NAME               CLUSTERING_FACTOR

------------------------------ -----------------

IDX_CS                    24000000

Elapsed: 00:00:00.09

重新执行目标sql：

22:48:39 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

20 ICOL$

28 CON$

25 PROXY_ROLE_DATA$

26 I_PROXY_ROLE_DATA$_1

21 COL$

23 PROXY_DATA$

24 I_PROXY_DATA$

22 USER$

27 I_PROXY_ROLE_DATA$_2

9 rows selected.

Elapsed: 00:00:00.08

其执行计划为如下所示：

23:25:46 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

SQL_ID    3v3s3h87rj717, child number 0

-------------------------------------

select object_id,object_name from cap_spm where object_id between 20

and 28

Plan hash value: 619725477

-----------------------------------------------------------------------------

| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |        |        |    297 (100)|        |

|*  1 |  TABLE ACCESS FULL| CAP_SPM |      8 |    240 |    297   (1)| 00:00:04 |

-----------------------------------------------------------------------------

从上述显示内容中可以看出，现在该sql的执行计划已经从对索引IDX_CS的索引范围扫描变为对表cap_spm的全表扫描，即执行计划已经发生了变更。

因为目标sql已经重复执行且同时又产生了一个新的执行计划，索引现在oracle就回自动捕获并创建这个新的执行计划对应的sql plan baseline了。

从如下查询如果中可以看出，oracle已经针对上述执行计划(即对表cap_spm得全表扫描)产生了一个新的sql plan baseline，其enabled的值依然为yes，但accepted的值变为了“no”:

23:25:48 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

SQL_HANDLE               PLAN_NAME              ORIGIN         ENA ACC SQL_TEXT

------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE   YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE   YES NO  select object_id,object_name from cap_spm where object_id be

tween 20 and 28

Elapsed: 00:00:00.03

然后我们对当前session关闭自动捕获sql plan baseline并同时开启wpm，即相当于恢复了oracle11g中的默认设置：

23:35:40 SQL> alter session set optimizer_capture_sql_plan_baselines=false;

Session altered.

Elapsed: 00:00:00.00

23:35:53 SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

Elapsed: 00:00:00.00

现在索引idx_cs的聚族因子依然是2400万：

23:36:06 SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_CS';

INDEX_NAME               CLUSTERING_FACTOR

------------------------------ -----------------

IDX_CS                    24000000

Elapsed: 00:00:00.11

再次执行目标sql：

23:37:10 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

20 ICOL$

21 COL$

22 USER$

23 PROXY_DATA$

24 I_PROXY_DATA$

25 PROXY_ROLE_DATA$

26 I_PROXY_ROLE_DATA$_1

27 I_PROXY_ROLE_DATA$_2

28 CON$

9 rows selected.

Elapsed: 00:00:00.01

执行计划如下：

23:37:26 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------

SQL_ID    3v3s3h87rj717, child number 2

-------------------------------------

select object_id,object_name from cap_spm where object_id between 20

and 28

Plan hash value: 3149005651

---------------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |       |       |  2520 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| CAP_SPM |     8 |   240 |  2520    (1)| 00:00:31 |

|*  2 |   INDEX RANGE SCAN        | IDX_CS  |     8 |       |     2    (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

，，，，，，，，，，，，

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------

2 - "CAP_SPM".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note

-----

- SQL plan baseline SQL_PLAN_5kbt7kwjds1kad7d172c1 used for this statement

50 rows selected.

Elapsed: 00:00:00.12

从上述显示内容中可以看出，现在目标sql的执行计划已经从对表cap_spm的全表扫描恢复为对表索引idx_cs的索引范围扫描。注意到上述执行计划中的note部分有如下内容：”SQL plan baseline SQL_PLAN_5kbt7kwjds1kad7d172c1 used for this statement”，这表明在wpm以开启的情况下，即使目标sql产生了新的执行计划，oracle依然只会应用该sql的enabled和accepted的值均为”yes“的sql plan baseline。

从如下查询结果中可以看出，目标sql所应用的sql plan baseline名为SQL_PLAN_5kbt7kwjds1kad7d172c1，所对应的enabled和accepted的值确实是”yes“(它就是目标sql第一次执行时走索引范围扫描的执行计划所对应的sql plan baseline):

23:37:34 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

SQL_HANDLE               PLAN_NAME              ORIGIN         ENA ACC SQL_TEXT

------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE  YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE   YES NO  select object_id,object_name from cap_spm where object_id be

tween 20 and 28

Elapsed: 00:00:00.01

从上述测试结果中可以很清晰地看到，spm确实能够稳定目标sql的执行计划。当启用了spm后，它确实能够保证只有被验证过的执行计划(即enabled和accepted的值均为”yes“的sql plan baseline所对应的执行计划)才会被启用，当由于某种原因(比如统计信息的变更)而导致目标sql产生了新的执行计划后，这个新的执行计划并不会被oracle启用。

如果想启用目标sql新的执行计划(即对表cap_spm得全表扫描)，应该如何做呢？

针对不同的oracle数据库版本，会有不同的处理办法。比如这里想启用目标sql新的执行计划(即对表cap_spm得全表扫描)，如果是oracle11gr1的环境，则只需将目标sql所采用的名为SQL_PLAN_5kbt7kwjds1kad7d172c1的sql plan baseline(即对索引idx_cs的索引范围扫描所对应的sql plan baseline)的accepted值设为”no”就可以了。但遗憾的是，上述sql的执行环境是oracle 11gr2，所以以如下方式执行dbms_spm.alter_sql_plan_baseline时oracle会报错(因为在oracle11gr2中，所有已被accepted的sql plan baseline的accepted值将不再能够被设置为“no”)：

23:55:55 SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'accepted',attribute_value=>'NO');

BEGIN :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'accepted',attribute_value=>'NO'); END;

*

ERROR at line 1:

ORA-38136: invalid attribute name ACCEPTED specified

ORA-06512: at "SYS.DBMS_SPM", line 2532

ORA-06512: at line 1

Elapsed: 00:00:00.12

在oracle11gr2中，我们可以联合使用dbms_spm.evolve_sql_plan_baseline和dbms_spm.alter_sql_plan_baseline达到启用目标sql新的执行计划的目的。

先使用dbms_spm.evolve_sql_plan_baseline将目标sql新的执行计划(即对表cap_spm的全表扫描)所对应的名为SQL_PLAN_5kbt7kwjds1kaf4dd99e6的sql plan baseline的accepted值设为“yes”：

00:01:40 SQL> exec :temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kaf4dd99e6',verify=>'NO',commit=>'YES');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

从如下查询结果可以看到，名为SQL_PLAN_5kbt7kwjds1kaf4dd99e6的sql plan baseline的accepted值确实已经从之前的“no”变为了“yes”

00:03:47 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

SQL_HANDLE               PLAN_NAME              ORIGIN         ENA ACC SQL_TEXT

------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE   YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE   YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

Elapsed: 00:00:00.01

然后我们再使用dbms_spm.alter_sql_plan_baseline将原先的执行计划(即对索引idx_cs的索引范围扫描)所对应的sql plan baseline的enabled的值设为“no”：

00:03:50 SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'ENABLED',attribute_value=>'NO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

从如下查询结果中可以看出，原先的执行计划(即对索引idx_cs的索引范围扫描)所对应得sql plan baseline(即SQL_PLAN_5kbt7kwjds1kad7d172c1)的enabled值确实从之前的“yes“变为了”no“：

00:07:57 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';

SQL_HANDLE               PLAN_NAME              ORIGIN         ENA ACC SQL_TEXT

------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE  NO  YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

SQL_592f279722dc064a           SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE   YES YES select object_id,object_name from cap_spm where object_id be

tween 20 and 28

Elapsed: 00:00:00.01

再次执行目标sql：

00:11:05 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

20 ICOL$

28 CON$

25 PROXY_ROLE_DATA$

26 I_PROXY_ROLE_DATA$_1

21 COL$

23 PROXY_DATA$

24 I_PROXY_DATA$

22 USER$

27 I_PROXY_ROLE_DATA$_2

9 rows selected.

Elapsed: 00:00:00.02

执行计划如下：

00:11:09 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------

SQL_ID    3v3s3h87rj717, child number 1

-------------------------------------

select object_id,object_name from cap_spm where object_id between 20

and 28

Plan hash value: 619725477

-----------------------------------------------------------------------------

| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |        |        |    297 (100)|        |

|*  1 |  TABLE ACCESS FULL| CAP_SPM |      8 |    240 |    297   (1)| 00:00:04 |

-----------------------------------------------------------------------------

。。。。。。。。。。

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------

-----

- SQL plan baseline SQL_PLAN_5kbt7kwjds1kaf4dd99e6 used for this statement

47 rows selected.

Elapsed: 00:00:00.04

从上述显示内容中可以看出，现在sql的执行计划已经对索引idx_cs的索引范围扫描变成了对表cap_spm的全表扫描，即我们要启用新的执行计划(对表cap_spm的全表扫描)的目的已经实现。注意到执行计划中的note部分如下内容：”SQL plan baseline SQL_PLAN_5kbt7kwjds1kaf4dd99e6 used for this statement“，这表明目标sql新的执行计划所对应的sql plan baseline已经被成功启用。

从上述测试结果中可以看出，实际上我们可以轻易地在目标sql的多个执行计划中切换，索引spm确实是既能够主动地稳定执行计划，又保留了继续是欧诺个新的执行计划的计划，并且我们很容易能够启用新的执行计划。

参考文献：【基于oracle的sql优化】---崔华