如何利用sqlprofile固定执行计划并将执行计划导入到新库
本文小编为大家详细介绍“如何利用sqlprofile固定执行计划并将执行计划导入到新库”，内容详细，步骤清晰，细节处理妥当，希望这篇“如何利用sqlprofile固定执行计划并将执行计划导入到新库”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。
创新互联-云计算及IDC服务提供商，涵盖公有云、IDC机房租用、联通机房服务器托管、等保安全、私有云建设等企业级互联网基础服务，沟通电话：13518219792
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 - Production
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;
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 5
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
名称随便。
更多关于DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF的说明，请查看官方文档：
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACBCEEH
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 创建用户
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 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行。可以看到，执行计划已经使用profile，走了全表扫描。
到此实验结束。
读到这里，这篇“如何利用sqlprofile固定执行计划并将执行计划导入到新库”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注创新互联行业资讯频道。
名称栏目：如何利用sqlprofile固定执行计划并将执行计划导入到新库
文章位置：http://mswzjz.cn/article/jjggdp.html