♣题目部分
在Oracle中，coe_xfr_sql_profile.sql脚本的作用是什么？
♣答案部分
使用coe_xfr_sql_profile.sql脚本生成sqlprof_attr数据
最麻烦的sqlprof_attr('FULL(t1@SEL$1)')是这里的格式如何写，在Mos上的文章note 215187.1中的sqlt.zip的目录utl中提供了脚本coe_xfr_sql_profile.sql可以生成这些信息。
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 processed3.查询上面两个语句的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 5 2 where sql_text like 'select * from scott.test where object_id=10%'; 6 7SQL_TEXT SQL_ID PLAN_HASH_VALUE 8---------------------------------------------------------------------------------------------------- ------------- --------------- 9select * from scott.test where object_id=10 cpk9jsg2qt52r 3384190782 10 11LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql 12 2 where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%'; 13 14SQL_TEXT SQL_ID PLAN_HASH_VALUE 15---------------------------------------------------------------------------------------------------- ------------- --------------- 16select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 217508114
4.把coe_xfr_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。
5.对上面的两个SQL产生outline data的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 15SYS@dlhr> @$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql cpk9jsg2qt52r 3384190782 16 17Parameter 1: 18SQL_ID (required) 19 20 21 22PLAN_HASH_VALUE AVG_ET_SECS 23--------------- ----------- 24 3384190782 .046 25 26Parameter 2: 27PLAN_HASH_VALUE (required) 28 29 30Values passed to coe_xfr_sql_profile: 31~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 32SQL_ID : "cpk9jsg2qt52r" 33PLAN_HASH_VALUE: "3384190782" 34 35SQL>BEGIN 36 2 IF :sql_text IS NULL THEN 37 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 38 4 END IF; 39 5 END; 40 6 / 41SQL>SET TERM OFF; 42SQL>BEGIN 43 2 IF :other_xml IS NULL THEN 44 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 45 4 END IF; 46 5 END; 47 6 / 48SQL>SET TERM OFF; 49 50Execute coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql 51on TARGET system in order to create a custom SQL Profile 52with plan 3384190782 linked to adjusted sql_text. 53 54 55COE_XFR_SQL_PROFILE completed. 56 57SQL>@$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql 06c2mucgn6t5g 217508114 58 59Parameter 1: 60SQL_ID (required) 61 62 63 64PLAN_HASH_VALUE AVG_ET_SECS 65--------------- ----------- 66 217508114 .113 67 68Parameter 2: 69PLAN_HASH_VALUE (required) 70 71 72Values passed to coe_xfr_sql_profile: 73~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 74SQL_ID : "06c2mucgn6t5g" 75PLAN_HASH_VALUE: "217508114" 76 77SQL>BEGIN 78 2 IF :sql_text IS NULL THEN 79 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 80 4 END IF; 81 5 END; 82 6 / 83SQL>SET TERM OFF; 84SQL>BEGIN 85 2 IF :other_xml IS NULL THEN 86 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 87 4 END IF; 88 5 END; 89 6 / 90SQL>SET TERM OFF; 91 92Execute coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql 93on TARGET system in order to create a custom SQL Profile 94with plan 217508114 linked to adjusted sql_text. 95 96 97COE_XFR_SQL_PROFILE completed.
6.替换文件coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql中的SYS.SQLPROF_ATTR部分，把它更改为coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql中产生的SYS.SQLPROF_ATTR部分，其中：
coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql的SYS.SQLPROF_ATTR：
1h := SYS.SQLPROF_ATTR(
2q'[BEGIN_OUTLINE_DATA]',
3q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
4q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
5q'[DB_VERSION('11.2.0.4')]',
6q'[ALL_ROWS]',
7q'[OUTLINE_LEAF(@"SEL$1")]',
8q'[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',
9q'[END_OUTLINE_DATA]');
10
11--coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql的SYS.SQLPROF_ATTR：
12h := SYS.SQLPROF_ATTR(
13q'[BEGIN_OUTLINE_DATA]',
14q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
15q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
16q'[DB_VERSION('11.2.0.4')]',
17q'[ALL_ROWS]',
18q'[OUTLINE_LEAF(@"SEL$1")]',
19q'[FULL(@"SEL$1" "TEST"@"SEL$1")]',
20q'[END_OUTLINE_DATA]');
21生成的文件在当前目录：7.执行替换过SYS.SQLPROF_ATTR的SQL，coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
SQL> @/tmp/coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
1SQL>@coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
2SQL>REM
3SQL>REM $Header: 215187.1 coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql 11.4.4.4 2016/05/26 carlos.sierra $
4SQL>REM
5SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
6SQL>REM
7SQL>REM AUTHOR
8SQL>REM carlos.sierra@oracle.com
9SQL>REM
10SQL>REM SCRIPT
11SQL>REM coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
12SQL>REM
13SQL>REM DESCRIPTION
14SQL>REM This script is generated by coe_xfr_sql_profile.sql
15SQL>REM It contains the SQL*Plus commands to create a custom
16SQL>REM SQL Profile for SQL_ID cpk9jsg2qt52r based on plan hash
17SQL>REM value 3384190782.
18SQL>REM The custom SQL Profile to be created by this script
19SQL>REM will affect plans for SQL commands with signature
20SQL>REM matching the one for SQL Text below.
21SQL>REM Review SQL Text and adjust accordingly.
22SQL>REM
23SQL>REM PARAMETERS
24SQL>REM None.
25SQL>REM
26SQL>REM EXAMPLE
27SQL>REM SQL> START coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql;
28SQL>REM
29SQL>REM NOTES
30SQL>REM 1. Should be run as SYSTEM or SYSDBA.
31SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
32SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
33SQL>REM 4. To drop this custom SQL Profile after it has been created:
34SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cpk9jsg2qt52r_3384190782');
35SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
36SQL>REM for the Oracle Tuning Pack.
37SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
38SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
39SQL>REM By doing so you can create a custom SQL Profile for the original
40SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
41SQL>REM
42SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
43SQL>REM
44SQL>VAR signature NUMBER;
45SQL>VAR signaturef NUMBER;
46SQL>REM
47SQL>DECLARE
48 2 sql_txt CLOB;
49 3 h SYS.SQLPROF_ATTR;
50 4 PROCEDURE wa (p_line IN VARCHAR2) IS
51 5 BEGIN
52 6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
53 7 END wa;
54 8 BEGIN
55 9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
56 10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
57 11 -- SQL Text pieces below do not have to be of same length.
58 12 -- So if you edit SQL Text (i.e. removing temporary Hints),
59 13 -- there is no need to edit or re-align unmodified pieces.
60 14 wa(q'[select * from scott.test where object_id=10]');
61 15 DBMS_LOB.CLOSE(sql_txt);
62 16 h := SYS.SQLPROF_ATTR(
63 17 q'[BEGIN_OUTLINE_DATA]',
64 18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
65 19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
66 20 q'[DB_VERSION('11.2.0.4')]',
67 21 q'[ALL_ROWS]',
68 22 q'[OUTLINE_LEAF(@"SEL$1")]',
69 23 q'[FULL(@"SEL$1" "TEST"@"SEL$1")]',
70 24 q'[END_OUTLINE_DATA]');
71 25 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
72 26 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
73 27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
74 28 sql_text => sql_txt,
75 29 profile => h,
76 30 name => 'coe_cpk9jsg2qt52r_3384190782',
77 31 description => 'coe cpk9jsg2qt52r 3384190782 '||:signature||' '||:signaturef||'',
78 32 category => 'DEFAULT',
79 33 validate => TRUE,
80 34 replace => TRUE,
81 35 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
82 36 DBMS_LOB.FREETEMPORARY(sql_txt);
83 37 END;
84 38 /
85
86PL/SQL procedure successfully completed.
87
88SQL>WHENEVER SQLERROR CONTINUE
89SQL>SET ECHO OFF;
90
91 SIGNATURE
92---------------------
93 10910590721604799112
94
95
96 SIGNATUREF
97---------------------
98 15966118871002195466
99
100
101... manual custom SQL Profile has been created
102
103
104COE_XFR_SQL_PROFILE_cpk9jsg2qt52r_3384190782 completed
1058.查看产生的sql profile,此时原语句在不加hint的情况下也走全表扫了select * from dba_sql_profiles;
1SYS@dlhr> col sql_text for a50
2SYS@dlhr> col hints for a50
3SYS@dlhr> SELECT b.name,to_char(d.sql_text) sql_text, extractvalue(value(h),'.') as hints
4 2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,
5 3 SYS.SQLOBJ$ B,
6 4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
7 5 '/outline_data/hint'))) h
8 6 where a.signature = b.signature
9 7 and a.category = b.category
10 8 and a.obj_type = b.obj_type
11 9 and a.plan_id = b.plan_id
12 10 and a.signature=d.signature
13 11 and D.name = 'coe_cpk9jsg2qt52r_3384190782';
14
15NAME SQL_TEXT HINTS
16------------------------------ -------------------------------------------------- --------------------------------------------------
17coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 BEGIN_OUTLINE_DATA
18coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS
19coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
20coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 DB_VERSION('11.2.0.4')
21coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 ALL_ROWS
22coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 OUTLINE_LEAF(@"SEL$1")
23coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1")
24coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 END_OUTLINE_DATA
25
268 rows selected.9.验证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 "coe_cpk9jsg2qt52r_3384190782" used for this statement
26
27
28Statistics
29----------------------------------------------------------
30 0 recursive calls
31 0 db block gets
32 6973 consistent gets
33 0 physical reads
34 0 redo size
35 4159482 bytes sent via SQL*Net to client
36 64375 bytes received via SQL*Net from client
37 5807 SQL*Net roundtrips to/from client
38 0 sorts (memory)
39 0 sorts (disk)
40 87076 rows processed
注意:
① 这个测试只是为了演示通过coe_xfr_sql_profile.sql实现手动加hint的方法，实际上面的语句问题的处理最佳的方法应该是重新收集SCOTT.TEST的统计信息才对。
② 当一条SQL既有Sql Profile又有Stored Outline时，优化器优先选择stored outline。
③ 通过Sql Profile手动加Hint的方法很简单，而为SQL添加最合理的Hint才是关键。
④ 测试完后，可以通过exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' );删除这个Sql Profile。
⑤ 执行coe_xfr_sql_profile.sql脚本的时候用户需要对当前目录有生成文件的权限，最好当前目录是/tmp。
