签到成功

知道了

CNDBA社区CNDBA社区

SPM(SQL Plan Management)介绍及利用SPM迁移执行计划

2017-05-10 16:35 4156 0 原创 Oracle 11G
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/1909

本文主要介绍手动的加在SQL计划基线以，演变执行计划和导出，导入SQL计划基线

更多关于SPM的详细信息参考官方文档：

https://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABBAFAH

小麦苗大神的博客有更详细的介绍：http://blog.itpub.net/26736162/viewspace-2107604/

1  准备实验环境

1.1   数据库版本

源库：Linux

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

目标库：Windows

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

1.2   创建表

SQL> create table cndba as select * from dba_objects;

Table created.

SQL> EXEC DBMS_STATS.gather_table_stats('LEI', 'CNDBA', cascade=>TRUE);

PL/SQL procedure successfully completed.

1.3   执行下面SQL，得到执行计划

SQL> select object_name from cndba where object_id=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3204404738
---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    30 |   344   (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CNDBA | 1 | 30 | 344 (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------

  1  recursive calls
  0  db block gets
1238 consistent gets
  0  physical reads
  0  redo size
533  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

1.4   查看当前SQL ID

SQL> set autot off
col SQL_TEXT format a100
select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
WHERE a.SQL_TEXT like '%elect object_name from cndba where object_id=2%'
and a.SQL_TEXT not like '%v$sql%'
AND    sql_text NOT LIKE '%EXPLAIN%';SQL> SQL>   2    3    4  

SQL_ID	      SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
059f7zymuf7u1 select object_name from cndba where object_id=2

http://www.cndba.cn/Expect-le/article/1909

1.5   手动捕获执行计划

输入SQL ID

http://www.cndba.cn/Expect-le/article/1909 

SQL> SET SERVEROUTPUT ON

DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
   sql_id => '&sql_id');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;

/SQL>   2    3    4    5    6    7    8  
Enter value for sql_id: 059f7zymuf7u1
old   5:    sql_id => '&sql_id');
new   5:    sql_id => '059f7zymuf7u1');
Plans Loaded: 1
 

PL/SQL procedure successfully completed.

1.6   查看DBA_SQL_PLAN_BASELINES视图查看SPM 信息


SQL> set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%cndba%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';SQL> SQL> SQL> SQL>   2    3    4  

SQL_HANDLE	    PLAN_NAME	ORIGIN	           ENA    ACC   FIX
----------------------------------- ----------------------------------- -------------- --- --- 
SQL_810e45e574cea692  SQL_PLAN_823k5wpucx9nkb6bb2477	MANUAL-LOAD    YES    YES     NO

---再次查看执行计划，看到了SPM中的执行计划

http://www.cndba.cn/Expect-le/article/1909

SQL> set autot trace

SQL> select object_name from cndba where object_id=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3204404738
---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    30 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CNDBA |	1 |    30 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=2)
Note
-----
   - SQL plan baseline "SQL_PLAN_823k5wpucx9nkb6bb2477" used for this statement
Statistics
----------------------------------------------------------
539  recursive calls
15   db block gets
1830 consistent gets
0    physical reads
3040 redo size
533  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
 42  sorts (memory)
  0  sorts (disk)
  1  rows processed


1.7   创建索引，收集统计信息

SQL> set autot off
SQL> create index ind_01 on cndba(object_id);

Index created.

http://www.cndba.cn/Expect-le/article/1909

SQL> EXEC DBMS_STATS.gather_table_stats(USER,'CNDBA', cascade=>TRUE);

PL/SQL procedure successfully completed.

--查看执行计划

SQL> set autot trace
SQL> select object_name from cndba where object_id=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3204404738
---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    30 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CNDBA |	1 |    30 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=2)
Note
-----
   - SQL plan baseline "SQL_PLAN_823k5wpucx9nkb6bb2477" used for this statement
Statistics
----------------------------------------------------------
643  recursive calls
 27  db block gets
       1684  consistent gets
  1  physical reads
      11456  redo size
533  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
 14  sorts (memory)
  0  sorts (disk)
  1  rows processed

创建了索引，还是走了全表扫描。很明显这不是一个最好的方案。

---查看dba_sql_plan_baselines视图


http://www.cndba.cn/Expect-le/article/1909

SQL> set autot off
col sql_handle for a35
col plan_name for a35
set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%cndba%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';SQL> SQL> SQL> SQL>   2    3    4  

SQL_HANDLE	    PLAN_NAME	ORIGIN	          ENA     ACC    FIX
----------------------------------- ----------------------------------- --------------
SQL_810e45e574cea692 SQL_PLAN_823k5wpucx9nk12efa0ed AUTO-CAPTURE YES NO NO
SQL_810e45e574cea692    SQL_PLAN_823k5wpucx9nkb6bb2477	MANUAL-LOAD    YES  YES  NO

这里可以看到，执行计划已经产生了，只是还没被接受(ACCEPT)，所以这个执行计划还不能被使用。

1.8   使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE包演变执行计划

SQL> SET SERVEROUTPUT ON

SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SQL_810e45e574cea692');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/SQL> SQL>   2    3    4    5    6    7    8  
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
  SQL_HANDLE = SQL_810e45e574cea692
  PLAN_NAME  =
  TIME_LIMIT =
DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
Plan: SQL_PLAN_823k5wpucx9nk12efa0ed
------------------------------------
  Plan was verified: Time used .03 seconds.
  Plan passed performance criterion: 412.4 times better than baseline plan.
  Plan was changed to an accepted plan
Baseline Plan	   Test Plan	   Stats Ratio
    -------------      ---------       -----------
  Execution Status:	 COMPLETE	COMPLETE
  Rows Processed:	1	       1
  Elapsed Time(ms):	    2.055	     .01
205.5
  CPU Time(ms): 2	       0
  Buffer Gets:	     1237	       3	    412.33
  Physical Read Requests:	0	       0
  Physical Write Requests:	0	       0
  Physical Read Bytes:	0               0
  Physical Write Bytes: 0	       0
  Executions:	1	       1
-------------------------------------------------------------------------------
 Report
Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1


PL/SQL procedure successfully completed.

--再次查看dba_sql_plan_baselines视图

SQL> set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%cndba%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL> SQL> SQL> SQL>   2    3    4  

SQL_HANDLE	    PLAN_NAME	ORIGIN	           ENA     ACC   FIX
----------------------------------- ----------------------------------- --------------
SQL_810e45e574cea692	SQL_PLAN_823k5wpucx9nk12efa0ed	AUTO-CAPTURE   YES  YES NO
SQL_810e45e574cea692	SQL_PLAN_823k5wpucx9nkb6bb2477	MANUAL-LOAD    YES  YES NO

1.9   再次执行同样的SQL

SQL> set autot trace
select object_name from cndba where object_id=2;SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3938353343
--------------------------------------------------------------------------------------
| Id  | Operation	    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  30 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CNDBA  |	   1 |	  30 |	   2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IND_01 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=2)
Note
-----
   - SQL plan baseline "SQL_PLAN_823k5wpucx9nk12efa0ed" used for this statement
Statistics
----------------------------------------------------------
 24  recursive calls
 15  db block gets
 17  consistent gets
  0  physical reads
       3040  redo size
533  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

这次使用了正确的执行计划。说明只有ENABLE和ACCEPT的执行计划才会被使用。

2  导出和导入 SQL Plan Baselines

2.1   源库使用CREATE_STGTAB_BASELINE过程创建暂存表

语法：

DBMS_SPM.CREATE_STGTAB_BASELINE (
   table_name        IN VARCHAR2,
   table_owner       IN VARCHAR2 := NULL,
   tablespace_name   IN VARCHAR2 := NULL);

http://www.cndba.cn/Expect-le/article/1909

Parameter

Description

table_name

Name of staging table to create for the purpose of packing and unpacking SQL plan baselines

table_owner

Name of owner of the staging table. Default NULL means current schema is the table owner.

tablespace_name

Name of tablespace. Default NULL means create staging table in the default tablespace.

 

--创建暂存表cndba_stage

SQL> BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name => 'cndba_stage');
END;

/  2    3    4    5  

PL/SQL procedure successfully completed.

2.2   使用PACK_STGTAB_BASELINE将SPM导入到暂存吧cndba_stage中

语法：

DBMS_SPM.PACK_STGTAB_BASELINE (
   table_name       IN VARCHAR2,
   table_owner      IN VARCHAR2 := NULL,
   sql_handle       IN VARCHAR2 := NULL,
   plan_name        IN VARCHAR2 := NULL,
   sql_text         IN CLOB     := NULL,
   creator          IN VARCHAR2 := NULL,   
   origin           IN VARCHAR2 := NULL,
   enabled          IN VARCHAR2 := NULL,
   accepted         IN VARCHAR2 := NULL,
   fixed            IN VARCHAR2 := NULL,
   module           IN VARCHAR2 := NULL,
   action           IN VARCHAR2 := NULL)
RETURN NUMBER;

参数说明：

Parameter

Description

table_name

Name of staging table into which SQL plan baselines will be packed (case insensitive unless double quoted)

table_owner

Name of staging table owner.Default NULL means current schema is the table owner

sql_handle

SQL handle (case sensitive)

plan_name

Plan name (case sensitive, % wildcards accepted)http://www.cndba.cn/Expect-le/article/1909

sql_text

SQL text string (case sensitive, % wildcards accepted)

creator

Creator of SQL plan baseline (case insensitive unless double quoted)

origin

Origin of SQL plan baseline,

 should be 'MANUAL-LOAD','AUTO-CAPTURE', 'MANUAL_SQLTUNE' or 'AUTO-SQLTUNE'(case insensitive)

enabled

Must be 'YES' or 'NO' (case insensitive)http://www.cndba.cn/Expect-le/article/1909

accepted

Must be 'YES' or 'NO' (case insensitive)

fixed

Must be 'YES' or 'NO' (case insensitive)

modulehttp://www.cndba.cn/Expect-le/article/1909

Module (case sensitive)

action

Action (case sensitive)

 

SQL> DECLARE

my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'cndba_stage',
    enabled => 'yes',
    creator => 'lei');
END;
/  2    3    4    5    6    7    8    9  

 
PL/SQL procedure successfully completed.

如果要导入指定执行计划，可以指定dba_sql_plan_baselines视图中的plan name，如果table_owner默认为当前用户。

2.3   使用数据泵导出暂存表cndba_stage

[oracle@dg-p ~]$ expdp system/oracle dumpfile=cndba_stage.dmp directory=lei_dir tables=lei.cndba_stage

 
Export: Release 11.2.0.4.0 - Production on Wed May 10 23:58:33 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=cndba_stage.dmp directory=lei_dir tables=lei.cndba_stage

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "LEI"."CNDBA_STAGE"                         23.36 KB       2 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /u01/backup/cndba_stage.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 10 23:58:42 2017 elapsed 0 00:00:08

2.4   导入到目标库

目标库已经创建好表及索引

2.4.1  查询目标库的dba_sql_plan_baselines视图信息

SQL> set autot off
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%cndba%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

未选定行

可以看到新库中，没有相应的SQL执行计划。

2.5   导入暂存表cndba_stage中

C:/Users/Administrator>impdp system/oracle dumpfile=cndba_stage.dmp directory=lei_dir tables=lei.cndba_stage


Import: Release 11.2.0.4.0 - Production on 星期三 5月 10 15:54:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TABLE_01"

启动 "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** dumpfile=cndba_stage.dmp directory=lei_dir tables=lei.cndba_stage

处理对象类型 TABLE_EXPORT/TABLE/TABLE

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

. . 导入了 "LEI"."CNDBA_STAGE"                         23.36 KB       2 行

作业 "SYSTEM"."SYS_IMPORT_TABLE_01" 已于 星期三 5月 10 15:54:25 2017 elapsed 0 00:00:01 成功完成

2.6   使用UNPACK_STGTAB_BASELINE函数将SQL计划基线从暂存表解压到目标库的SQL 管理库中：

SQL> DECLARE
  2  my_plans number;
  3  BEGIN
  4    my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
  5      table_name => 'cndba_stage');
  6  END;
  7  /


PL/SQL 过程已成功完成。

如果只想导入指定的执行计划，就需要指定plan name，这个参数在上面有说明。

2.7   验证

SQL> set autot off
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%cndba%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE                          PLAN_NAME               ORIGIN         ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- 
SQL_810e45e574cea692       SQL_PLAN_823k5wpucx9nk12efa0ed      AUTO-CAPTURE   YES YES NO
SQL_810e45e574cea692       SQL_PLAN_823k5wpucx9nkb6bb2477      MANUAL-LOAD    YES YES NO

可以看到执行计划已经导入成功

2.8   执行SQL

SQL> set autot trace
SQL> select object_name from cndba where object_id=2;

执行计划
----------------------------------------------------------
Plan hash value: 3938353343
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CNDBA  |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_01 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=2)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline "SQL_PLAN_823k5wpucx9nk12efa0ed" used for this statement
统计信息
----------------------------------------------------------
         34  recursive calls
         16  db block gets
         78  consistent gets
          0  physical reads
       2952  redo size
        534  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
SQL>

 SQL plan baseline执行计划被使用了。http://www.cndba.cn/Expect-le/article/1909

版权声明：本文为博主原创文章，未经博主允许不得转载。

SPM(SQL Plan Management) 导出导入执行计划

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1567129次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ