共计 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 行业资讯频道。