sql profile

一个SQL语句对于按日分区表查询，其中某个日期特别慢20多分钟，其他都几十秒到2分钟左右，统计信息和执行计划有些差异，创建sql profile固化一下，问题解决：

 

SPO sqlt_s36096_p3830036895_sqlprof.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 sqlt_s36096_p3830036895_sqlprof.sql 11.4.4.0 2011/12/08 $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM  

REM
REM SCRIPT
REM   sqlt_s36096_p3830036895_sqlprof.sql
REM
REM SOURCE
REM   Host    : bip-ndb01
REM   DB Name : ora11g
REM   Platform. Linux
REM   Product : Oracle Database 11g Enterprise Edition (64bit Production)
REM   Version : 11.2.0.2.0
REM   Language: US:AMERICAN_AMERICA.AL32UTF8
REM   EBS     : NO
REM   Siebel  : NO
REM
REM DESCRIPTION
REM   This script. is generated automatically by the SQLT tool.
REM   It contains the SQL*Plus commands to create a custom
REM   SQL Profile based on plan hash value 3830036895.
REM   The custom SQL Profile to be created by this script
REM   will affect plans for SQL commands with signature
REM   matching the one for SQL Text below.
REM   Review SQL Text and adjust accordingly.
REM
REM PARAMETERS
REM   None.
REM
REM EXAMPLE
REM   SQL> START sqlt_s36096_p3830036895_sqlprof.sql;
REM
REM NOTES
REM   1. Should be run as SYSTEM or SYSDBA.
REM   2. User must have CREATE ANY SQL PROFILE privilege.
REM   3. SOURCE and TARGET systems can be the same or similar.
REM   4. To drop this custom SQL Profile after it has been created:
REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('sqlt_s36096_p3830036895');
REM   5. Be aware that using DBMS_SQLTUNE requires a license
REM      for the Oracle Tuning Pack.
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;

VAR signature NUMBER;

DECLARE
  sql_txt CLOB;
  h       SYS.SQLPROF_ATTR;
BEGIN
  sql_txt := q'[select /*+ parallel(t3,8) parallel(t4,8) parallel(t5,8) leading(t1)*/
       count(distinct t1.username),
       count(distinct t3.username),
       count(distinct t4.username),
       count(distinct t5.username)
  from (select /*+ parallel(t1,8) parallel(t2,8) */
        distinct t1.workd, t1.username
          from bip.og_webfish_login_d01 t1, bip.dim_webfish_roles t2
         where t1.workd = '20111105'
           and t1.username = t2.bindusername
           and t1.workd = t2.registerdate) t1,
       bip.og_webfish_login_d01 t3,
       bip.og_webfish_login_d01 t4,
       bip.og_webfish_login_d01 t5
 where t3.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 1, 'yyyymmdd')
   and t4.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 2, 'yyyymmdd')
   and t5.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 6, 'yyyymmdd')
   and t1.username = t3.username(+)
   and t1.username = t4.username(+)
   and t1.username = t5.username(+)]';

  h := SYS.SQLPROF_ATTR(
  q'[BEGIN_OUTLINE_DATA]',
  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]',
  q'[DB_VERSION('11.2.0.2')]',
  q'[ALL_ROWS]',
  q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
  q'[MERGE(@"SEL$2")]',
  q'[OUTLINE(@"SEL$1")]',
  q'[OUTLINE(@"SEL$2")]',
  q'[FULL(@"SEL$F5BB74E1" "T2"@"SEL$2")]',
  q'[FULL(@"SEL$F5BB74E1" "T1"@"SEL$2")]',
  q'[FULL(@"SEL$F5BB74E1" "T3"@"SEL$1")]',
  q'[FULL(@"SEL$F5BB74E1" "T4"@"SEL$1")]',
  q'[FULL(@"SEL$F5BB74E1" "T5"@"SEL$1")]',
  q'[LEADING(@"SEL$F5BB74E1" "T2"@"SEL$2" "T1"@"SEL$2" "T3"@"SEL$1" "T4"@"SEL$1" "T5"@"SEL$1")]',
  q'[USE_HASH(@"SEL$F5BB74E1" "T1"@"SEL$2")]',
  q'[USE_HASH(@"SEL$F5BB74E1" "T3"@"SEL$1")]',
  q'[USE_HASH(@"SEL$F5BB74E1" "T4"@"SEL$1")]',
  q'[USE_HASH(@"SEL$F5BB74E1" "T5"@"SEL$1")]',
  q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T1"@"SEL$2" BROADCAST NONE)]',
  q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T3"@"SEL$1" HASH HASH)]',
  q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T4"@"SEL$1" HASH HASH)]',
  q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T5"@"SEL$1" HASH HASH)]',
  q'[SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "T5"@"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        => 'sqlt_s36096_p3830036895',
    description => 's36096_ora11g_bipndb01 08hcfgsgr5bjt 3830036895 '||:signature,
    category    => 'DEFAULT',
    validate    => TRUE,
    replace     => TRUE,
    force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/

WHENEVER SQLERROR CONTINUE;
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO SQLPROFILE completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-713990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-713990/