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

本文小编为大家详细介绍“如何利用sqlprofile固定执行计划并将执行计划导入到新库”，内容详细，步骤清晰，细节处理妥当，希望这篇“如何利用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

目标库：

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 - Production1.1 创建表SQL> conn lei/lei Connected. SQL> create table ttas 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; Exlained. 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        51.5 查看outlineSQL> explain planfor 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) 27rows selected.1.6 生成sql profileSQL> 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   10PL/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

名称随便。

更多关于DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF的说明，请查看官方文档：

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACBCEEH2.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:122.3 导入到新环境2.3.1 创建用户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_ROL 处理对象类型 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_STAISTICS 处理对象类型 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 过程已成功完成。2.3.4 再次查看执行计划SQL> explain planfor select * from tt where object_id=2; 已解释。 SQL>  select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hashvalue: 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行。

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

到此实验结束。

读到这里，这篇“如何利用sqlprofile固定执行计划并将执行计划导入到新库”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注行业资讯频道。