在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
♣答案部分
可以使用coe_load_sql_profile.sql脚本直接固定执行计划，该脚本也可以实现直接把sqlprofile直接迁移到其它库中。
很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定SQL执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间SQL执行计划的固定。最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
需要注意的是，该脚本不能以SYS用户执行，否则会报如下的错误：
1DECLARE 2* 3ERROR at line 1: 4ORA-19381: cannot create staging table in SYS schema 5ORA-06512: at "SYS.DBMS_SMB", line 313 6ORA-06512: at "SYS.DBMS_SQLTUNE", line 6306 7ORA-06512: at line 64
示例如下：
1.建立测试表和数据
1SYS@dlhr> select * from v$version; 2 3BANNER 4-------------------------------------------------------------------------------- 5Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 6PL/SQL Release 11.2.0.4.0 - Production 7CORE 11.2.0.4.0 Production 8TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production 9NLSRTL Version 11.2.0.4.0 - Production 10 11 12LHR@dlhr> create table scott.test as select * from dba_objects; 13 14Table created. 15 16LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id); 17 18Index created. 19 20LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true); 21 22PL/SQL procedure successfully completed. 23 24LHR@dlhr> update scott.test set object_id=10 where object_id>10; 25 26 27LHR@dlhr> commit; 28Commit complete. 29 30 31 32LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID; 33 34 OBJECT_ID COUNT(1) 35---------- ---------- 36 6 1 37 7 1 38 5 1 39 8 1 40 3 1 41 2 1 42 10 87076 43 4 1 44 9 1 45 469 rows selected.
2.执行查询语句
执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。
1LHR@dlhr> set autot traceonly explain stat 2LHR@dlhr> 3LHR@dlhr> select * from scott.test where object_id=10; 4 587076 rows selected. 6 7 8Execution Plan 9---------------------------------------------------------- 10Plan hash value: 3384190782 11 12------------------------------------------------------------------------------------------- 13| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 14------------------------------------------------------------------------------------------- 15| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | 16| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 | 17|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 | 18------------------------------------------------------------------------------------------- 19 20Predicate Information (identified by operation id): 21--------------------------------------------------- 22 23 2 - access("OBJECT_ID"=10) 24 25 26Statistics 27---------------------------------------------------------- 28 0 recursive calls 29 0 db block gets 30 13060 consistent gets 31 0 physical reads 32 0 redo size 33 9855485 bytes sent via SQL*Net to client 34 64375 bytes received via SQL*Net from client 35 5807 SQL*Net roundtrips to/from client 36 0 sorts (memory) 37 0 sorts (disk) 38 87076 rows processed 39 40LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10; 41 4287076 rows selected. 43 44 45Execution Plan 46---------------------------------------------------------- 47Plan hash value: 217508114 48 49-------------------------------------------------------------------------- 50| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 51-------------------------------------------------------------------------- 52| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 | 53|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 | 54-------------------------------------------------------------------------- 55 56Predicate Information (identified by operation id): 57--------------------------------------------------- 58 59 1 - filter("OBJECT_ID"=10) 60 61 62Statistics 63---------------------------------------------------------- 64 1 recursive calls 65 0 db block gets 66 6973 consistent gets 67 0 physical reads 68 0 redo size 69 4159482 bytes sent via SQL*Net to client 70 64375 bytes received via SQL*Net from client 71 5807 SQL*Net roundtrips to/from client 72 0 sorts (memory) 73 0 sorts (disk) 74 87076 rows processed
3.查询上面两个语句的SQL_ID、PLAN_HASH_VALUE
1LHR@dlhr> set autot off 2LHR@dlhr> 3LHR@dlhr> col sql_text format a100 4LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from scott.test where object_id=10%'; 5 6SQL_TEXT SQL_ID PLAN_HASH_VALUE 7---------------------------------------------------------------------------------------------------- ------------- --------------- 8select * from scott.test where object_id=10 cpk9jsg2qt52r 2317948335 9 10LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%'; 11 12SQL_TEXT SQL_ID PLAN_HASH_VALUE 13---------------------------------------------------------------------------------------------------- ------------- --------------- 14select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 1357081020
4.把coe_load_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。
5.使用coe_load_sql_profile.sql脚本
1[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp 2[ZHLHRSPMDB2:oracle]:/tmp> 3[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba 4 5SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016 6 7Copyright (c) 1982, 2013, Oracle. All rights reserved. 8 9 10Connected to: 11Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 12With the Partitioning, Real Application Clusters, OLAP, Data Mining 13and Real Application Testing options 14 15LHR@dlhr > @/home/oracle/coe_load_sql_profile.sql 16 17Parameter 1: 18ORIGINAL_SQL_ID (required) 19 20Enter value for 1: cpk9jsg2qt52r 21 22Parameter 2: 23MODIFIED_SQL_ID (required) 24 25Enter value for 2: 06c2mucgn6t5g 26 27 28 PLAN_HASH_VALUE AVG_ET_SECS 29-------------------- -------------------- 30 1357081020 .058 31 32Parameter 3: 33PLAN_HASH_VALUE (required) 34 35Enter value for 3: 1357081020 36 37Values passed to coe_load_sql_profile: 38~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 39ORIGINAL_SQL_ID: "cpk9jsg2qt52r" 40MODIFIED_SQL_ID: "06c2mucgn6t5g" 41PLAN_HASH_VALUE: "1357081020" 42 43SQL>BEGIN 44 2 IF :sql_text IS NULL THEN 45 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 46 4 END IF; 47 5 END; 48 6 / 49SQL>SET TERM OFF; 50SQL>BEGIN 51 2 IF :other_xml IS NULL THEN 52 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 53 4 END IF; 54 5 END; 55 6 / 56SQL> 57SQL>SET ECHO OFF; 580001 BEGIN_OUTLINE_DATA 590002 IGNORE_OPTIM_EMBEDDED_HINTS 600003 OPTIMIZER_FEATURES_ENABLE('11.2.0.3') 610004 DB_VERSION('11.2.0.3') 620005 ALL_ROWS 630006 OUTLINE_LEAF(@"SEL$1") 640007 FULL(@"SEL$1" "TEST"@"SEL$1") 650008 END_OUTLINE_DATA 66dropping staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" 67staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" did not exist 68creating staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" 69packaging new sql profile into staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" 70 71PROFILE_NAME 72------------------------------ 73CPK9JSG2QT52R_1357081020 74SQL>REM 75SQL>REM SQL Profile 76SQL>REM ~~~~~~~~~~~ 77SQL>REM 78SQL>SELECT signature, name, category, type, status 79 2 FROM dba_sql_profiles WHERE name = :name; 80 81 SIGNATURE NAME CATEGORY TYPE STATUS 82-------------------- ------------------------------ ------------------------------ ------- -------- 8310910590721604799112 CPK9JSG2QT52R_1357081020 DEFAULT MANUAL ENABLED 84SQL>SELECT description 85 2 FROM dba_sql_profiles WHERE name = :name; 86 87DESCRIPTION 88-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 89ORIGINAL:CPK9JSG2QT52R MODIFIED:06C2MUCGN6T5G PHV:1357081020 SIGNATURE:10910590721604799112 CREATED BY COE_LOAD_SQL_PROFILE.SQL 90SQL>SET ECHO OFF; 91 92**************************************************************************** 93* Enter LHR password to export staging table STGTAB_SQLPROF_cpk9jsg2qt52r 94**************************************************************************** 95 96Export: Release 11.2.0.3.0 - Production on Tue Sep 12 10:39:16 2017 97 98Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 99 100Password: 101 102Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 103With the Partitioning, Automatic Storage Management, OLAP, Data Mining 104and Real Application Testing options 105Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set 106Note: grants on tables/views/sequences/roles will not be exported 107Note: indexes on tables will not be exported 108Note: constraints on tables will not be exported 109 110About to export specified tables via Conventional Path ... 111. . exporting table STGTAB_SQLPROF_CPK9JSG2QT52R 1 rows exported 112Export terminated successfully without warnings. 113 114 115If you need to implement this Custom SQL Profile on a similar system, 116import and unpack using these commands: 117 118imp LHR file=STGTAB_SQLPROF_cpk9jsg2qt52r.dmp tables=STGTAB_SQLPROF_cpk9jsg2qt52r ignore=Y 119 120BEGIN 121DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( 122profile_name => 'CPK9JSG2QT52R_1357081020', 123replace => TRUE, 124staging_table_name => 'STGTAB_SQLPROF_cpk9jsg2qt52r', 125staging_schema_owner => 'LHR' ); 126END; 127/ 128 129 adding: coe_load_sql_profile_cpk9jsg2qt52r.log (deflated 76%) 130 adding: STGTAB_SQLPROF_cpk9jsg2qt52r.dmp (deflated 89%) 131 adding: coe_load_sql_profile.log (deflated 62%) 132 133deleting: coe_load_sql_profile.log 134 135 136coe_load_sql_profile completed. 137SQL>
6.查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了select * from dba_sql_profiles;
1SQL>set line 9999 2SQL> SELECT b.name,to_char(d.sql_text) sql_text, extractvalue(value(h),'.') as hints 3 2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A, 4 SYS.SQLOBJ$ B, 5 4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), 6 5 '/outline_data/hint'))) h 7 where a.signature = b.signature 8 7 and a.category = b.category 9 8 and a.obj_type = b.obj_type 10 9 and a.plan_id = b.plan_id 11 10 and a.signature=d.signature 12 11 and D.name = 'CPK9JSG2QT52R_1357081020'; 13 14NAME SQL_TEXT HINTS 15------------------------------ -------------------------------------------------- -------------------------------------------------- 16CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 BEGIN_OUTLINE_DATA 17CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS 18CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('11.2.0.3') 19CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 DB_VERSION('11.2.0.3') 20CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 ALL_ROWS 21CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OUTLINE_LEAF(@"SEL$1") 22CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1") 23CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 END_OUTLINE_DATA
7.验证SQL Profile是否生效
1SYS@dlhr> set autot traceonly explain stat 2SYS@dlhr> select * from scott.test where object_id=10; 3 487076 rows selected. 5 6 7Execution Plan 8---------------------------------------------------------- 9Plan hash value: 217508114 10 11-------------------------------------------------------------------------- 12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 13-------------------------------------------------------------------------- 14| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 | 15|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 | 16-------------------------------------------------------------------------- 17 18Predicate Information (identified by operation id): 19--------------------------------------------------- 20 21 1 - filter("OBJECT_ID"=10) 22 23Note 24----- 25 - SQL profile "CPK9JSG2QT52R_1357081020" used for this statement 26 27Statistics 28---------------------------------------------------------- 29 0 recursive calls 30 0 db block gets 31 6973 consistent gets 32 0 physical reads 33 0 redo size 34 4159482 bytes sent via SQL*Net to client 35 64375 bytes received via SQL*Net from client 36 5807 SQL*Net roundtrips to/from client 37 0 sorts (memory) 38 0 sorts (disk) 39 87076 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
