在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私聊。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
最后修改时间：2020-01-10 19:13:20
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
