签到成功

知道了

CNDBA社区CNDBA社区

利用sqlprofile固定执行计划并将执行计划导入到新库

2017-05-10 12:36 3044 0 原创 Oracle 11G
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/1908

本文主要讲的是如何利用sqlprofile固定执行计划，已经执行计划的导出导入。对于数据迁移导致执行计划不稳定有一定的帮助。

1  实验环境

Linux 11G R2 导入到 windows 11G R2

源库：

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

目标库：

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

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 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

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

1.1   创建表

SQL> conn lei/lei

Connected.

SQL> create table tt as select * from dba_objects;

 

Table created.

 

SQL> create index idex_01 on tt(object_id);

 

Index created.

1.2   收集统计信息

SQL> exec dbms_stats.gather_table_stats('LEI','TT',cascade=>true);

 

PL/SQL procedure successfully completed.

1.3   生成执行计划

SQL> explain plan for select object_NAME FROM TT WHERE object_id=2;

 

Explained.

 

SQL>  select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2974445191
--------------------------------------------------------------------------------
| Id  | Operation	    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	      |     1 |    30 |     2	(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    30 |     2	(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDEX_01 |     1 |       |     1	(0)| 00:00:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("OBJECT_ID"=2)

14 rows selected.

可以看到是走索引的。

1.4   使用HINT改变执行计划

SQL> select /*+ full(tt) */* from tt where object_id=2;


OWNER

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

OBJECT_NAME

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

SUBOBJECT_NAME	OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

CREATED   LAST_DDL_ TIMESTAMP	STATUS	T G S  NAMESPACE

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

EDITION_NAME

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

SYS

C_OBJ#

2	       2 CLUSTER


OWNER

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

OBJECT_NAME

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

SUBOBJECT_NAME	OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

CREATED   LAST_DDL_ TIMESTAMP	STATUS	T G S  NAMESPACE

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

EDITION_NAME

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

24-AUG-13 24-AUG-13 2013-08-24:11:37:35 VALID	N N N	       5

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

1.5   查看outline

SQL> explain plan for select /*+ full(tt) */* from tt where object_id=2;

Explained.


SQL> select * from table(dbms_xplan.display(null,null,'outline'));


PLAN_TABLE_OUTPUT

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

Plan hash value: 264906180

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    98 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT	 |     1 |    98 |   344   (1)| 00:00:05 |
--------------------------------------------------------------------------
PLAN_TABLE_OUTPUT

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

  /*+

      BEGIN_OUTLINE_DATA

      FULL(@"SEL$1" "TT"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=2)

27 rows selected.

1.6   生成sql profile

SQL> declare

        v_hints sys.sqlprof_attr;

  begin

        v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "TT"@"SEL$1")');   --从上面获得

        dbms_sqltune.import_sql_profile('select * from tt where object_id= 2',    --sql语句

         v_hints, 'TT_LEI_20170510',   --profile名称

        force_match => true);

  end;

 /  8    9   10  

 

PL/SQL procedure successfully completed.

1.7   查看profile是否生效

SQL> explain plan for select * from tt where object_id=2;

Explained.

SQL> select * from table(dbms_xplan.display);


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

Note
--------
   - SQL profile "TT_LEI_20170510" used for this statement

17 rows selected.

可以看到已经生效了。

2  导出表和打包执行计划

2.1   打包执行计划

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'TEST_TT_PROFILE1',schema_name=>'LEI');

 

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name

=>'TEST_TT_PROFILE1',profile_name=>'TT_LEI_20170510');

 

PL/SQL procedure successfully completed

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

名称随便。

关于DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF参数说明

Parameter

Description

table_name

The name of the table to create (case-insensitive unless double quoted). Required.

schema_name

The schema to create the table in, or NULL for current schema (case-insensitive unless double quoted)

tablespace_name

The tablespace to store the staging table within, or NULL for current user's default tablespace

(case-insensitive unless double quoted)

更多关于该包的信息，请查看官方文档：http://www.cndba.cn/Expect-le/article/1908

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACBCEEH

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

 

2.2   导出用户LEI


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

 

Export: Release 11.2.0.4.0 - Production on Wed May 10 20:09:28 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_SCHEMA_01":  system/******** dumpfile=tt.dmp directory=lei_dir schemas=lei

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 10.18 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "LEI"."TT"                                  8.366 MB   86269 rows

. . exported "LEI"."TEST_TT_PROFILE1"                    22.02 KB       1 rows

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

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/backup/tt.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 10 20:09:40 2017 elapsed 0 00:00:12

2.3   导入到新环境

2.3.1  创建用户

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

SQL> create user lei identified by lei;

 

用户已创建。

 

SQL> grant dba,resource,connect to lei;

 

授权成功。

 

SQL>

C:/Users/Administrator>impdp system/oracle dumpfile=tt.dmp directory=lei_dir schemas=LEI

 

Import: Release 11.2.0.4.0 - Production on 星期三 5月 10 12:05:09 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_SCHEMA_01"

启动 "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=tt.dmp directory=lei_dir schemas=LEI

处理对象类型 SCHEMA_EXPORT/USER

ORA-31684: 对象类型 USER:"LEI" 已存在

处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT

处理对象类型 SCHEMA_EXPORT/ROLE_GRANT

处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . 导入了 "LEI"."TT"                                  8.366 MB   86269 行

. . 导入了 "LEI"."TEST_TT_PROFILE1"                    22.02 KB       1 行

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

作业 "SYSTEM"."SYS_IMPORT_SCHEMA_01" 已经完成, 但是有 1 个错误 (于 星期三 5月 10 12:05:12 2017 elapsed 0 00:00:03 完成)

2.3.2  查看新库中的执行计划

SQL> conn lei/lei

已连接。

SQL> explain plan for select * from tt where object_id=2;

 

已解释。

SQL>  select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2974445191
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDEX_01 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

 

已选择14行。

可以看到默认还是走索引。

2.3.3  解包sqlprofile,执行计划变更为与源库一样的执行计划。

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST_TT_PROFILE1');

PL/SQL 过程已成功完成。

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

2.3.4  再次查看执行计划

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

SQL> explain plan for select * from tt where object_id=2;

已解释。

SQL>  select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    98 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT   |     1 |    98 |   344   (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=2)
 
Note
-----
   - SQL profile "TT_LEI_20170510" used for this statement


已选择17行。

 

SQL>

可以看到，执行计划已经使用profile，走了全表扫描。

到此实验结束。

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

固定，迁移执行计划

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

Expect-乐

关注

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

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