SQL Profiles-MANUAL

谁践踏了优雅 2022-08-08 05:26 135阅读 0赞

Manual管理SQL Profile步骤  
1、针对目标sql使用脚本coe\_xfr\_sql\_profile.sql产生能生成其Manual类型的SQL Profile 的脚本A  
2、改写目标sql的文本，在其中使用合适的hint，直到加入hint后的sql能走出我们想要的执行计划。然后对加入合适hint后的sql使用脚本coe\_xfr\_sql\_profile.sql，产生能生成其Manual类型的SQL Profile的脚本B。  
3、用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。

4、执行脚本A生成针对原目标的SQL的Manual类型的SQL Profile。

模拟SQL走错执行计划
    SYS@fyl>select /*+ no_index(t ind_n) */ * from t where n=3;
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     2 |    26 |    30   (4)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T    |     2 |    26 |    30   (4)| 00:00:01 |
    --------------------------------------------------------------------------
    
    使用hint改写sql
    SYS@fyl>select /*+ index(t ind_n) */ * from t where n=3; 
             N
    ----------
             3
    SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------
    SQL_ID  37bvrs54d4pu5, child number 0
    -------------------------------------
    select /*+ index(t ind_n) */ * from t where n=3
    Plan hash value: 3063387565
    --------------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
    |*  1 |  INDEX RANGE SCAN| IND_N |     1 |    13 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
       1 - SEL$1 / T@SEL$1
    Outline Data
    -------------
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
          DB_VERSION('11.2.0.1')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))
          END_OUTLINE_DATA
      */
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - access("N"=3)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
       1 - "N"[NUMBER,22]
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    46 rows selected.
    
    SYS@fyl>select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%index%n=3%'; 
    
    SQL_TEXT                                                     SQL_ID        VERSION_COUNT
    ------------------------------------------------------------ ------------- -------------
    select /*+ index(t ind_n) */ * from t where n=3              37bvrs54d4pu5             1
    select /*+ no_index(t ind_n) */ * from t where n=3           9wypc3n066q48             1
    
    查询加入hint后的sql的plan_hash_value
    SYS@fyl>select plan_hash_value from v$sql where sql_id='37bvrs54d4pu5';
    PLAN_HASH_VALUE
    ---------------
         3063387565
    查询错误的sql的plan_hash_value
    SYS@fyl>select plan_hash_value from v$sql where sql_id='9wypc3n066q48';
    PLAN_HASH_VALUE
    ---------------
         1601196873

1、针对原目标sql使用脚本coe\_xfr\_sql\_profile.sql产生能生成其Manual类型的SQL Profile 的脚本，执行coe\_xfr\_sql\_profile.sql的过程输入错误sql的sql\_id和PLAN\_HASH\_VALUE。

SYS@fyl>@coe_xfr_sql_profile.sql
    
    Parameter 1:
    SQL_ID (required)
    
    Enter value for 1: 9wypc3n066q48
    
    
    PLAN_HASH_VALUE AVG_ET_SECS
    --------------- -----------
         1601196873        .058
    
    Parameter 2:
    PLAN_HASH_VALUE (required)
    
    Enter value for 2: 1601196873
    
    Values passed to coe_xfr_sql_profile:
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    SQL_ID : "9wypc3n066q48"
    PLAN_HASH_VALUE: "1601196873"
    
    SQL>BEGIN
      2    IF :sql_text IS NULL THEN
      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).');
      4    END IF;
      5  END;
      6  /
    SQL>SET TERM OFF;
    SQL>BEGIN
      2    IF :other_xml IS NULL THEN
      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).');
      4    END IF;
      5  END;
      6  /
    SQL>SET TERM OFF;
    
    Execute coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
    on TARGET system in order to create a custom SQL Profile
    with plan 1601196873 linked to adjusted sql_text.
    
    
    COE_XFR_SQL_PROFILE completed.

2、对加入合适hint--index后的sql使用脚本coe\_xfr\_sql\_profile.sql，产生能生成其Manual类型的SQL Profile的脚本。

SQL>@coe_xfr_sql_profile.sql
    
    Parameter 1:
    SQL_ID (required)
    
    Enter value for 1: 37bvrs54d4pu5
    
    
    PLAN_HASH_VALUE AVG_ET_SECS
    --------------- -----------
         3063387565         .01
    
    Parameter 2:
    PLAN_HASH_VALUE (required)
    
    Enter value for 2: 3063387565
    
    Values passed to coe_xfr_sql_profile:
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    SQL_ID : "37bvrs54d4pu5"
    PLAN_HASH_VALUE: "3063387565"
    
    SQL>BEGIN
      2    IF :sql_text IS NULL THEN
      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).');
      4    END IF;
      5  END;
      6  /
    SQL>SET TERM OFF;
    SQL>BEGIN
      2    IF :other_xml IS NULL THEN
      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).');
      4    END IF;
      5  END;
      6  /
    SQL>SET TERM OFF;
    
    Execute coe_xfr_sql_profile_37bvrs54d4pu5_3063387565.sql
    on TARGET system in order to create a custom SQL Profile
    with plan 3063387565 linked to adjusted sql_text.
    
    
    COE_XFR_SQL_PROFILE completed.
    
    错误sql的sql profile内容coe_xfr_sql_profile_9wypc3n066q48_1601196873.sql
    。。。
    DECLARE
    sql_txt CLOB;
    h SYS.SQLPROF_ATTR;
    BEGIN
    sql_txt := q'[
    select /*+ no_index(t ind_n)
    */ * from t where n=3
    ]';
    h := SYS.SQLPROF_ATTR(
    q'[BEGIN_OUTLINE_DATA]',
    q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
    q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
    q'[DB_VERSION('11.2.0.1')]',
    q'[ALL_ROWS]',
    q'[OUTLINE_LEAF(@"SEL$1")]',
    q'[FULL(@"SEL$1" "T"@"SEL$1")]',
    q'[END_OUTLINE_DATA]');
    :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
    sql_text => sql_txt,
    profile => h,
    name => 'coe_9wypc3n066q48_1601196873',
    description => 'coe 9wypc3n066q48 1601196873 '||:signature||'',
    category => 'DEFAULT',
    validate => TRUE,
    replace => TRUE,
    force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
    END;
    /
    。。。
    
    改写加入hint后的sql profile内容coe_xfr_sql_profile_37bvrs54d4pu5_3063387565.sql
    。。。
    DECLARE
    sql_txt CLOB;
    h SYS.SQLPROF_ATTR;
    BEGIN
    sql_txt := q'[
    select /*+ index(t ind_n)
    */ * from t where n=3
    ]';
    h := SYS.SQLPROF_ATTR(
    q'[BEGIN_OUTLINE_DATA]',
    q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
    q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
    q'[DB_VERSION('11.2.0.1')]',
    q'[ALL_ROWS]',
    q'[OUTLINE_LEAF(@"SEL$1")]',
    q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))]',
    q'[END_OUTLINE_DATA]');
    :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
    sql_text => sql_txt,
    profile => h,
    name => 'coe_37bvrs54d4pu5_3063387565',
    description => 'coe 37bvrs54d4pu5 3063387565 '||:signature||'',
    category => 'DEFAULT',
    validate => TRUE,
    replace => TRUE,
    force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURS
    OR_SHARING) */ );
    END;
    /
    。。。

3、用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。  
用改写后的sql profile的hint组合替换原来sql profile的hint，并将原sql profile的force\_match改为TRUE

4、执行脚本A生成针对原目标的SQL的Manual类型的SQL Profile。

SYS@fyl>@coe\_xfr\_sql\_profile\_9wypc3n066q48\_1601196873.sql  
SYS@fyl>REM  
SYS@fyl>REM $Header: 215187.1 coe\_xfr\_sql\_profile\_9wypc3n066q48\_1601196873.sql 11.4.3.5 2015/05/05 carlos.sierra $  
SYS@fyl>REM  
SYS@fyl>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.  
SYS@fyl>REM  
SYS@fyl>REM AUTHOR  
SYS@fyl>REM carlos.sierra@oracle.com  
SYS@fyl>REM  
SYS@fyl>REM SCRIPT  
SYS@fyl>REM coe\_xfr\_sql\_profile\_9wypc3n066q48\_1601196873.sql  
SYS@fyl>REM  
SYS@fyl>REM DESCRIPTION  
SYS@fyl>REM This script is generated by coe\_xfr\_sql\_profile.sql  
SYS@fyl>REM It contains the SQL\*Plus commands to create a custom  
SYS@fyl>REM SQL Profile for SQL\_ID 9wypc3n066q48 based on plan hash  
SYS@fyl>REM value 1601196873.  
SYS@fyl>REM The custom SQL Profile to be created by this script  
SYS@fyl>REM will affect plans for SQL commands with signature  
SYS@fyl>REM matching the one for SQL Text below.  
SYS@fyl>REM Review SQL Text and adjust accordingly.  
SYS@fyl>REM  
SYS@fyl>REM PARAMETERS  
SYS@fyl>REM None.  
SYS@fyl>REM  
SYS@fyl>REM EXAMPLE  
SYS@fyl>REM SQL> START coe\_xfr\_sql\_profile\_9wypc3n066q48\_1601196873.sql;  
SYS@fyl>REM  
SYS@fyl>REM NOTES  
SYS@fyl>REM 1. Should be run as SYSTEM or SYSDBA.  
SYS@fyl>REM 2. User must have CREATE ANY SQL PROFILE privilege.  
SYS@fyl>REM 3. SOURCE and TARGET systems can be the same or similar.  
SYS@fyl>REM 4. To drop this custom SQL Profile after it has been created:  
SYS@fyl>REM EXEC DBMS\_SQLTUNE.DROP\_SQL\_PROFILE('coe\_9wypc3n066q48\_1601196873');  
SYS@fyl>REM 5. Be aware that using DBMS\_SQLTUNE requires a license  
SYS@fyl>REM for the Oracle Tuning Pack.  
SYS@fyl>REM  
SYS@fyl>WHENEVER SQLERROR EXIT SQL.SQLCODE;  
SYS@fyl>REM  
SYS@fyl>VAR signature NUMBER;  
SYS@fyl>REM  
SYS@fyl>DECLARE  
  2  sql\_txt CLOB;  
  3  h SYS.SQLPROF\_ATTR;  
  4  BEGIN  
  5  sql\_txt := q'\[  
  6  select /\*+ no\_index(t ind\_n)  
  7  \*/ \* from t where n=3  
  8  \]';  
  9  h := SYS.SQLPROF\_ATTR(  
 10  q'\[BEGIN\_OUTLINE\_DATA\]',  
 11  q'\[IGNORE\_OPTIM\_EMBEDDED\_HINTS\]',  
 12  q'\[OPTIMIZER\_FEATURES\_ENABLE('11.2.0.1')\]',  
 13  q'\[DB\_VERSION('11.2.0.1')\]',  
 14  q'\[ALL\_ROWS\]',  
 15  q'\[OUTLINE\_LEAF(@"SEL$1")\]',  
 16  q'\[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."N"))\]',  
 17  q'\[END\_OUTLINE\_DATA\]');  
 18  :signature := DBMS\_SQLTUNE.SQLTEXT\_TO\_SIGNATURE(sql\_txt);  
 19  DBMS\_SQLTUNE.IMPORT\_SQL\_PROFILE (  
 20  sql\_text => sql\_txt,  
 21  profile => h,  
 22  name => 'coe\_9wypc3n066q48\_1601196873',  
 23  description => 'coe 9wypc3n066q48 1601196873 '||:signature||'',  
 24  category => 'DEFAULT',  
 25  validate => TRUE,  
 26  replace => TRUE,  
 27  force\_match => TRUE /\* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR\_SHARING) \*/ );  
 28  END;  
 29  /  
  
  
PL/SQL procedure successfully completed.  
  
  
SYS@fyl>WHENEVER SQLERROR CONTINUE  
SYS@fyl>SET ECHO OFF;  
  
  
            SIGNATURE  
\---------------------  
 14002184797686193519  
  
  
  
  
... manual custom SQL Profile has been created  
  
  
  
  
COE\_XFR\_SQL\_PROFILE\_9wypc3n066q48\_1601196873 completed  
  
  
验证：  
SYS@fyl>select /\*+ no\_index(t ind\_n) \*/ \* from t where n=3;  
  
  
  
  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 3063387565  
  
  
\--------------------------------------------------------------------------  
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT |       |    82 |  1066 |     1   (0)| 00:00:01 |  
|\*  1 |  INDEX RANGE SCAN| IND\_N |    82 |  1066 |     1   (0)| 00:00:01 |  
\--------------------------------------------------------------------------  
  
  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
  
  
   1 - access("N"=3)  
  
  
Note  
\-----  
   - SQL profile "coe\_9wypc3n066q48\_1601196873" used for this statement