【DB笔试面试606】在Oracle中，coe_xfr_sql_profile.sql脚本的作用是什么？

640?wx_fmt=gif

♣题目部分

在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 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  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 105

8.查看产生的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。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。