签到成功

知道了

CNDBA社区CNDBA社区

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

2017-05-10 16:35 4106 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/http://www.cndba.cn/Expect-le/article/1909

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

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中的执行计划

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.

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
----------------------------------------------------------
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视图


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视图

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  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);

Parameter

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

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.http://www.cndba.cn/Expect-le/article/1909

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)

sql_text

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

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)

accepted

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

fixed

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

module

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%';

未选定行

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

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

2.5   导入暂存表cndba_stage中

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

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执行计划被使用了。

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

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

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

Expect-乐

关注

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

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1548402次
  • 积分：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