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

71次阅读
没有评论

共计 11084 个字符，预计需要花费 28 分钟才能阅读完成。

本文丸趣 TV 小编为大家详细介绍“如何利用 sqlprofile 固定执行计划并将执行计划导入到新库”，内容详细，步骤清晰，细节处理妥当，希望这篇“如何利用 sqlprofile 固定执行计划并将执行计划导入到新库”文章能帮助大家解决疑惑，下面跟着丸趣 TV 小编的思路慢慢深入，一起来学习新知识吧。

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;

------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
C_OBJ#
2 2 CLUSTER

------------------------------ ---------- -------------- -------------------
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.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
--------
 - 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;
授权成功。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)
 
-----
 - SQL profile  TT_LEI_20170510  used for this statement
已选择 17 行。

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

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

正文完
g production 导入到
发表至： 数据库
2023-07-13
 
丸趣
版权声明：本站原创文章，由 丸趣 2023-07-13发表，共计11084字。
转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。
navicat如何查看表结构
navicat导出sql sever脚本文件的方法
Linux服务器上如何启动和停止MySQL服务
如何使用变量对SQL进行优化
评论（没有评论）