ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > cursor_sharing=force导致sql profile部分hint失效

cursor_sharing=force导致sql profile部分hint失效

原创 Oracle 作者：liiinuuux 时间：2015-01-06 14:47:46 0 删除 编辑
SQL文本
（部分表名字段名做了替换）
select *
  from (select prop1 ,
               prop2,
               (select codename
                  from code d1
                 where '1418892021000' = '1418892021000'
                   and d1.codetype = 'edornotype'
                   and d1.code = Rpad(prop3, 20, ' ' )),
               prop11,
               (case ( select count (1)
                    from t_pol
                   where polno = mainpolno
                     and contno = rpad(prop2, 20, ' ' ))
                   when 1 then
                    (select paytodate
                       from t_pol c
                      where polno = mainpolno
                        and contno = rpad(prop2, 20, ' ' )
                        And Rownum = 1)
                   else
                    (select max(paytodate)
                       from t_pol c
                      where polno = mainpolno
                        and payintv > 0
                        and contno = rpad(prop2, 20, ' ' )
                        and appflag = '1' )
               end) paytodate ,
               (select codename
                  from code d2
                 where d2.codetype = 'edorapptype'
                   and d2.code = Rpad(prop5, 20, ' ' )),
               (SELECT EDORNAME
                  FROM item
                 WHERE APPOBJ <> 'G'
                   AND EDORCODE IN
                       (SELECT EDORTYPE
                          FROM pitem
                         WHERE EDORACCEPTNO = rpad(prop1, 20, ' '))
                   AND ROWNUM = 1),
               nvl((SELECT SUM(getmoney)
                     FROM pitem
                    WHERE EDORACCEPTNO = rpad(prop1, 20, ' ')
                      and edorstate = '2' ),
                   0),
               (Select Edorpopedom
                  From user
                 Where Usercode = Rpad(Createoperator, 20, ' ' )
                   And Usertype = '1'
                   And Userstate = '0' ),
               prop7,
               Createoperator,
               makedate,
               missionid,
               submissionid,
               activityid,
               (select nvl(urgenttype, '1')
                  from app
                 where edoracceptno = rpad(prop1, 20)) as urgenttype,
               (case
                   when prop5 = '2' then
                    (select codename
                       from code
                      where codetype = 'agentlevel'
                        and code =
                            (select rpad(agentstar, 20)
                               from app
                              where edoracceptno = rpad(prop1, 20)))
                   else
                    ''
               end),
               (select codename
                  from code
                 where codetype = 'CustomCheckResult'
                   and code =
                       (select customcheckresult
                          from app
                         where edoracceptno = rpad(prop1, 20))),
               greatest(makedate,
                        nvl((select max(makedate)
                              from doc_main
                             where doccode = mission.prop1),
                            date '1900-01-01' )) as greatdate,
               greatest(to_char( makedate, 'yyyymmdd') || maketime,
                        (select nvl(max(to_char( makedate, 'yyyymmdd') ||
                                        maketime),
                                    '1900010100:00:00')
                           from doc_main
                          where doccode = prop1)) as greattime,
               (case
                   when prop5 = '2' then
                    (select agentstar
                       from app
                      where edoracceptno = rpad(prop1, 20))
                   else
                    ''
               end) as agentstar
          from mission
         where 1 = 1
           and activityid = '0000000007'
           and processid = '0000000000'
           and exists
         (select 1
                  from app
                 where (Preinputflag Is Null Or Preinputflag = '0')
                   and edoracceptno = rpad(mission.prop1, 20, ' '))
           and defaultoperator is null
           and exists ( select 1
                  from app
                 where applyfortype <> '2'
                   and edoracceptno = rpad(prop1, 20))
           and exists
         (select 1
                  from pitem
                 where EdorAcceptNo = rpad(prop1, 20, ' ')
                   and edortype in
                       (select edorcode
                          from popedom
                         where AppDateModifyFlag >=
                               (select min(outdate)
                                  from bmission
                                 where activityid = '0000000003'
                                   and bmission.prop1 =
                                       mission.prop1) -
                               (select distinct edorappdate
                                  from pitem
                                 where edorstate = '2'
                                   and edoracceptno =
                                       rpad(mission.prop1, 20 , ' '))
                           and LowerAppDate <=
                               (select min(outdate)
                                  from bmission
                                 where activityid = '0000000003'
                                   and bmission.prop1 =
                                       mission.prop1) -
                               (select distinct edorappdate
                                  from pitem
                                 where edorstate = '2'
                                   and edoracceptno =
                                       rpad(mission.prop1, 20 , ' '))
                           and LimitGetMoney +
                               nvl((SELECT sum(getmoney)
                                     FROM pitem
                                    WHERE EDORACCEPTNO =
                                          rpad(prop1, 20 , ' ')
                                      and edorstate = '2' ),
                                   0) >= 0
                           and LowerLimitGetMoney +
                               nvl((SELECT sum(getmoney)
                                     FROM pitem
                                    WHERE EDORACCEPTNO =
                                          rpad(prop1, 20 , ' ')
                                      and edorstate = '2' ),
                                   0) < 0
                           and edorcode in ('AG',
                                            'CT',
                                            'PT',
                                            'CG',
                                            'LO',
                                            'PG',
                                            'LN',
                                            'RL',
                                            'XT')
                           and ApproveFlag = '1'
                           and Exists
                         (Select 1
                                  From user
                                 Where Usercode = 'zy1zgs'
                                   And Usertype = '1'
                                   And Userstate = '0'
                                   And Edorpopedom = popedom.Edorpopedom )
                        union
                        select edorcode
                          from popedom
                         where AppDateModifyFlag >=
                               (select min(outdate)
                                  from bmission
                                 where activityid = '0000000003'
                                   and bmission.prop1 =
                                       mission.prop1) -
                               (select distinct edorappdate
                                  from pitem
                                 where edorstate = '2'
                                   and edoracceptno =
                                       rpad(mission.prop1, 20 , ' '))
                           and LowerAppDate <=
                               (select min(outdate)
                                  from bmission
                                 where activityid = '0000000003'
                                   and bmission.prop1 =
                                       mission.prop1) -
                               (select distinct edorappdate
                                  from pitem
                                 where edorstate = '2'
                                   and edoracceptno =
                                       rpad(mission.prop1, 20 , ' '))
                           and edorcode not in ( 'AG',
                                                'CT',
                                                'PT',
                                                'CG',
                                                'LO',
                                                'PG',
                                                'LN',
                                                'RL',
                                                'XT')
                           and ApproveFlag = '1'
                           and Exists
                         (Select 1
                                  From user
                                 Where Usercode = 'zy1zgs'
                                   And Usertype = '1'
                                   And Userstate = '0'
                                   and Edorpopedom = popedom.Edorpopedom )))
           And Exists
         (Select 1
                  From rrela a, mrela b
                 Where a.Areaid = b.Areaid
                   And b.Comcode in
                       (Rpad (Substr(prop7, 1, 4), 10 ),
                        Rpad(Substr (prop7 , 1, 6 ), 10))
                   And a.Usercode = 'zy1zgs'
                   and not exists
                 (select 1
                          from ldcom
                         where comcode = rpad(prop7, 10)
                           and substr (comcode , 1, 6 ) in
                               (select trim(code)
                                  from code
                                 where rpad (codealias , 10) = b.Comcode
                                   and codetype = 'RiseCom' )))
           and (CreateOperator <> 'zy1zgs' or
               (select edortype
                   from pitem
                  where edoracceptno = rpad(prop1, 20, ' ')
                    and rownum = 1) in ('AC' , 'BB', 'CD', 'PC' ))
           And Exists
         (Select 1
                  From doc_Pages
                 Where Exists ( Select 1
                          From doc_Main
                         Where Docid = doc_Pages.Docid
                           And Doccode = prop1))
           And Exists
         (Select 1
                  From Lccont
                 Where Contno =
                       (Select Contno
                          From pitem
                         Where Edoracceptno = Rpad(prop1, 20, ' ')
                           And Rownum = 1)
                   and Conttype != 2 )
         order by urgenttype desc ,
                  greatdate,
                  agentstar  desc nulls last ,
                  greattime)
 where rownum <= 10


故事从最早说起，这个SQL有一条执行突然变得很慢。以前几秒，现在几十秒。
把生产的统计信息导出，放在审计库（和生产差一个月数据）上研究了一下，问题应该是出在柱状图上
对比审计库本身的 10053和将生产统计信息导入到审计库后的 10053


索引方面
审计库统计信息
  Index: IDX_pitem_EDORACCEPTNO  Col#: 1
LVLS: 3  #LB: 533158  #DK: 62078976  LB/K: 1.00  DB/K: 1.00  CLUF: 46340376.00
  Index: pitem_INDEX_3  Col#: 13
LVLS: 2  #LB: 235330  #DK: 12  LB/K: 19610.00  DB/K: 318001.00  CLUF: 3816015.00
生产统计信息
  Index: IDX_pitem_EDORACCEPTNO  Col#: 1
LVLS: 3  #LB: 530957  #DK: 63950848  LB/K: 1.00  DB/K: 1.00  CLUF: 45405526.00
  Index: pitem_INDEX_3  Col#: 13
LVLS: 3  #LB: 248069  #DK: 12  LB/K: 20672.00  DB/K: 310030.00  CLUF: 3720364.00


审计库柱状图信息
  Column (#1): EDORACCEPTNO(
    AvgLen: 21 NDV: 62078976 Nulls: 0 Density: 0.000005
    Histogram: Freq  #Bkts: 255  UncompBkts: 254  EndPtVals: 255
  Column (#13): EDORSTATE(
    AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
    Histogram: Freq  #Bkts: 10  UncompBkts: 20023  EndPtVals: 10
生产柱状图信息
  Column (#1): EDORACCEPTNO(
    AvgLen: 21 NDV: 63950848 Nulls: 0 Density: 0.000005
    Histogram: Freq  #Bkts: 255  UncompBkts: 254  EndPtVals: 255
  Column (#13): EDORSTATE(
    AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
Histogram: Freq  #Bkts: 10  UncompBkts: 20632  EndPtVals: 10


上面看到统计信息表明看起来没什么区别，但是成本估算差距很大。
注意下面两个索引的成本对比
审计库成本估算
  Access Path: index (AllEqRange)
    Index: IDX_pitem_EDORACCEPTNO
    resc_io: 221.00  resc_cpu: 0
    ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 221.00  Resp: 221.00  Degree: 1
  Access Path: index (AllEqRange)
    Index: pitem_INDEX_3
    resc_io: 813.00  resc_cpu: 0
    ix_sel: 0.000200  ix_sel_with_filters: 0.000200
Cost: 813.00  Resp: 813.00  Degree: 1
生产成本估算
  Access Path: index (AllEqRange)
    Index: IDX_pitem_EDORACCEPTNO
    resc_io: 245.00  resc_cpu: 0
    ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 245.00  Resp: 245.00  Degree: 1
  Access Path: index (AllEqRange)
    Index: pitem_INDEX_3
    resc_io: 5.00  resc_cpu: 0
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000
Cost: 5.00  Resp: 5.00  Degree: 1


生产的执行计划里所有pitem表都错误地选择了pitem_INDEX_3。可以发现对 IDX_pitem_EDORACCEPTNO的估算比较真实，但是对 pitem_INDEX_3，也就是 EDORSTATE（流程节点状态）的估算差距非常大。
oracle认为EDORSTATE=’2’ 的值非常非常少。这个 10053没有报谓词越界，问题应该出在柱状图上


查看柱状图
CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER)
    RETURN VARCHAR2
AS
    l_str      LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x'));
    l_return   VARCHAR2 (4000);
BEGIN
    WHILE (l_str IS NOT NULL)
    LOOP
        l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx'));
        l_str := SUBSTR (l_str, 3);
    END LOOP;


    RETURN (SUBSTR (l_return, 1, 6));
END;
/


查看这张表第 13列的柱状图。
这里只能显示每个 bucket末端的值。可以看到两个 bucket都是 ’1’，第三个是 ’2’。最大的可能是第三个 bucket里的大多数也是 ’1’
SQL> select BUCKET, HEXSTR(ENDPOINT) value from histgrm$ where obj# = 94494 and COL# = 13;


    BUCKET VALUE
---------- ----------------
     20392 0
     20446 1
     20447 1
     20448 2
     20536 3
     20537 6
     20579 7
     20621 8
     20630 `
     20632 a


柱状图信息和真实值差距过大的结果就是对取值返回的结果集大小估算有偏差
在两个库上执行下面语句
explain plan for
select * from pitem where EDORSTATE = '2';
select * from table(dbms_xplan.display(null, null, 'OUTLINE'));


昨天生产的统计信息
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2906569252


----------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   337 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| pitem         |     1 |   337 |     5 |
|*  2 |   INDEX RANGE SCAN          | pitem_INDEX_3 |     1 |       |     4 |
----------------------------------------------------------------------------------


今天生产的统计信息
Plan hash value: 2906569252


----------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    | 10433 |  3433K|   572 |
|   1 |  TABLE ACCESS BY INDEX ROWID| pitem         | 10433 |  3433K|   572 |
|*  2 |   INDEX RANGE SCAN          | pitem_INDEX_3 | 10433 |       |    39 |
----------------------------------------------------------------------------------


考虑到这个 SQL时查询流程节点出在某一状态的，并且采用抢占的方式处理流程。
可以做如下分析：
1 SQL中的'0000000000' 号流程的'0000000007'号节点处于 ’2’状态的在周末基本都已经被处理完了。
2 30号收集统计信息时发现几乎没有状态为 ’2’的这个节点。
3 星期一早上，这个表的状态字段发生了大量的修改，节点状态发生了变化：
Segments by DB Blocks Changes        DB/Inst: PRODB/PRODB2  Snaps: 47177-47179
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot


           Tablespace                      Subobject  Obj.      DB Block    % of
Owner         Name    Object Name            Name     Type       Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
LIS        LIS        pitem                      TABLE  256,322,560   55.21
LIS        LISINDX    pitem_INDEX_3              INDEX  205,673,696   44.30
LIS        LIS        LDMAXNO                         TABLE      156,144     .03
LIS        LIS        app                       TABLE      135,376     .03
LIS        LIS        mission                       TABLE      132,912     .03
          -------------------------------------------------------------

但是oracle采用了周末收集的统计信息，走了 pitem_INDEX_3


4 随着大家又开始处理流程了，因此 EDORSTATE=’2’ 的数据开始减少。扫描pitem_INDEX_3的成本逐渐提高，因此就有了现在生产上后来的默认执行计划：
走两个索引，做 AND-EQUAL。说明两个索引的成正在逐渐接近。


后来决定用sql profile固定执行计划
declare
sql_txt clob;
h sys.sqlprof_attr;
begin
h := sys.sqlprof_attr (
'BEGIN_OUTLINE_DATA',
'INDEX(@"SEL$44" "pitem"@"SEL$44" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
'        "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'USE_NL(@"SEL$245B400D" "code"@"SEL$39")' ,
'LEADING(@"SEL$245B400D" "LDCOM"@"SEL$38" "code"@"SEL$39")' ,
'INDEX_RS_ASC(@"SEL$245B400D" "code"@"SEL$39" ("code"."CODETYPE" "code"."CODE"))',
'INDEX(@"SEL$245B400D" "LDCOM"@"SEL$38" ("LDCOM"."COMCODE"))' ,
'INDEX_RS_ASC(@"SEL$32" "bmission"@"SEL$32" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$33")' ,
'INDEX_RS_ASC(@"SEL$33" "pitem"@"SEL$33" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$34" "bmission"@"SEL$34" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$35")' ,
'INDEX_RS_ASC(@"SEL$35" "pitem"@"SEL$35" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$24" "bmission"@"SEL$24" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$25")' ,
'INDEX_RS_ASC(@"SEL$25" "pitem"@"SEL$25" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$26" "bmission"@"SEL$26" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$27")' ,
'INDEX_RS_ASC(@"SEL$27" "pitem"@"SEL$27" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$28" "pitem"@"SEL$28" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$29" "pitem"@"SEL$29" ("pitem"."EDORACCEPTNO"))' ,
'USE_NL(@"SEL$EE91C113" "popedom"@"SEL$23")' ,
'LEADING(@"SEL$EE91C113" "user"@"SEL$30" "popedom"@"SEL$23")' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "popedom"@"SEL$23" ("popedom"."EDORPOPEDOM"',
'        "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
'        "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "user"@"SEL$30" ("user"."USERCODE" "user"."USERTYPE"))',
'USE_NL(@"SEL$69C7062B" "popedom"@"SEL$31")' ,
'LEADING(@"SEL$69C7062B" "user"@"SEL$36" "popedom"@"SEL$31")' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "popedom"@"SEL$31" ("popedom"."EDORPOPEDOM"',
'        "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
'        "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "user"@"SEL$36" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$16" "app"@"SEL$16" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$14" "app"@"SEL$14" ("app"."EDORACCEPTNO"))' ,
'INDEX(@"SEL$9" "pitem"@"SEL$9" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO" "pitem"."POLNO"',
'        "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'INDEX_RS_ASC(@"SEL$3" "D1"@"SEL$3" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$4" "t_pol"@"SEL$4" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$5" "C"@"SEL$5" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$6" "C"@"SEL$6" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$7" "D2"@"SEL$7" ("code"."CODETYPE" "code"."CODE"))' ,
'FULL(@"SEL$8" "item"@"SEL$8")' ,
'INDEX_RS_ASC(@"SEL$10" "pitem"@"SEL$10" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$11" "user"@"SEL$11" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$12" "app"@"SEL$12" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SEL$14")' ,
'INDEX_RS_ASC(@"SEL$13" "code"@"SEL$13" ("code"."CODETYPE" "code"."CODE"))' ,
'PUSH_SUBQ(@"SEL$16")' ,
'INDEX_RS_ASC(@"SEL$15" "code"@"SEL$15" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$17" "doc_MAIN"@"SEL$17" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
'        "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$18" "doc_MAIN"@"SEL$18" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
'        "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$19" "app"@"SEL$19" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SET$1")' ,
'INDEX(@"SEL$22" "pitem"@"SEL$22" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
'        "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$245B400D")' ,
'USE_NL(@"SEL$37" "B"@"SEL$37")' ,
'LEADING(@"SEL$37" "A"@"SEL$37" "B"@"SEL$37")' ,
'INDEX(@"SEL$37" "B"@"SEL$37" ("mrela"."COMCODE" "mrela"."AREAID"))',
'INDEX(@"SEL$37" "A"@"SEL$37" ("rrela"."USERCODE" "rrela"."AREAID"',
'        "rrela"."COMCODE"))' ,
'INDEX(@"SEL$40" "pitem"@"SEL$40" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
'        "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$44")' ,
'INDEX_RS_ASC(@"SEL$43" "LCCONT"@"SEL$43" ("LCCONT"."CONTNO"))' ,
'USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")' ,
'LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")' ,
'INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))' ,
'INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
'        "doc_MAIN"."SUBTYPE"))' ,
'USE_NL(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$20")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$21")' ,
'LEADING(@"SEL$D8F4EB61" "mission"@"SEL$2" "app"@"SEL$21" "app"@"SEL$20"',
'        "VW_SQ_1"@"SEL$CEAA7324")' ,
'NO_ACCESS(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$20" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$21" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "mission"@"SEL$2" ("mission"."ACTIVITYID"))' ,
'NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")' ,
'OUTLINE(@"SEL$42")' ,
'OUTLINE(@"SEL$41")' ,
'OUTLINE(@"SEL$2")' ,
'UNNEST(@"SEL$42")' ,
'OUTLINE(@"SEL$28583BE2")' ,
'OUTLINE(@"SEL$21")' ,
'OUTLINE(@"SEL$20")' ,
'OUTLINE(@"SEL$CEAA7324")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE(@"SEL$D8F4EB61")' ,
'OUTLINE(@"SEL$1B0ECD80")' ,
'OUTLINE(@"SEL$39")' ,
'OUTLINE(@"SEL$38")' ,
'OUTLINE(@"SEL$36")' ,
'OUTLINE(@"SEL$31")' ,
'OUTLINE(@"SEL$30")' ,
'OUTLINE(@"SEL$23")' ,
'OUTLINE_LEAF(@"SEL$1")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE_LEAF(@"SEL$D8F4EB61")' ,
'PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)' ,
'OUTLINE_LEAF(@"SEL$700B301E")' ,
'OUTLINE_LEAF(@"SEL$43")' ,
'OUTLINE_LEAF(@"SEL$44")' ,
'OUTLINE_LEAF(@"SEL$40")' ,
'OUTLINE_LEAF(@"SEL$37")' ,
'UNNEST(@"SEL$39")' ,
'OUTLINE_LEAF(@"SEL$245B400D")' ,
'OUTLINE_LEAF(@"SEL$22")' ,
'OUTLINE_LEAF(@"SET$1")' ,
'UNNEST(@"SEL$36")' ,
'OUTLINE_LEAF(@"SEL$69C7062B")' ,
'OUTLINE_LEAF(@"SEL$35")' ,
'OUTLINE_LEAF(@"SEL$34")' ,
'OUTLINE_LEAF(@"SEL$33")' ,
'OUTLINE_LEAF(@"SEL$32")' ,
'UNNEST(@"SEL$30")' ,
'OUTLINE_LEAF(@"SEL$EE91C113")' ,
'OUTLINE_LEAF(@"SEL$29")' ,
'OUTLINE_LEAF(@"SEL$28")' ,
'OUTLINE_LEAF(@"SEL$27")' ,
'OUTLINE_LEAF(@"SEL$26")' ,
'OUTLINE_LEAF(@"SEL$25")' ,
'OUTLINE_LEAF(@"SEL$24")' ,
'OUTLINE_LEAF(@"SEL$19")' ,
'OUTLINE_LEAF(@"SEL$18")' ,
'OUTLINE_LEAF(@"SEL$17")' ,
'OUTLINE_LEAF(@"SEL$15")' ,
'OUTLINE_LEAF(@"SEL$16")' ,
'OUTLINE_LEAF(@"SEL$13")' ,
'OUTLINE_LEAF(@"SEL$14")' ,
'OUTLINE_LEAF(@"SEL$12")' ,
'OUTLINE_LEAF(@"SEL$11")' ,
'OUTLINE_LEAF(@"SEL$10")' ,
'OUTLINE_LEAF(@"SEL$8")' ,
'OUTLINE_LEAF(@"SEL$9")' ,
'OUTLINE_LEAF(@"SEL$7")' ,
'OUTLINE_LEAF(@"SEL$6")' ,
'OUTLINE_LEAF(@"SEL$5")' ,
'OUTLINE_LEAF(@"SEL$4")' ,
'OUTLINE_LEAF(@"SEL$3")' ,
'ALL_ROWS',
'DB_VERSION(''11.2.0.3'')' ,
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')' ,
'OPT_PARAM(''_optimizer_use_feedback'' ''false'')' ,
'OPT_PARAM(''_optimizer_adaptive_cursor_sharing'' ''false'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'')' ,
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')' ,
'IGNORE_OPTIM_EMBEDDED_HINTS' ,
'END_OUTLINE_DATA'
);
dbms_sqltune.import_sql_profile (
sql_text    => 'select * from (select prop1, prop2,  (select codename from code d1 where ''1418892021000''=''1418892021000'' and  d1.codetype = ''edornotype'' and d1.code = Rpad(prop3, 20, '' '') ),  prop11,  (case (select count(1) from t_pol where polno =mainpolno and contno = rpad(prop2,20,'' '')) when 1 then (select paytodate from t_pol c where polno = mainpolno and contno = rpad(prop2,20,'' '') And Rownum = 1) else (select max(paytodate) from t_pol c where polno = mainpolno and payintv>0 and contno = rpad(prop2,20,'' '') and appflag =''1'') end) paytodate,  (select codename from code d2 where d2.codetype = ''edorapptype'' and d2.code = Rpad(prop5, 20, '' '') ),  (SELECT EDORNAME FROM item WHERE APPOBJ<>''G'' AND EDORCODE IN (SELECT EDORTYPE FROM pitem WHERE EDORACCEPTNO=rpad(prop1,20,'' '')) AND ROWNUM=1),  nvl((SELECT SUM(getmoney) FROM pitem WHERE  EDORACCEPTNO=rpad(prop1,20,'' '') and edorstate=''2''),0),  (Select Edorpopedom From user  Where Usercode = Rpad(Createoperator, 20, '' '') And Usertype = ''1'' And Userstate = ''0''),  prop7,  Createoperator, makedate, missionid, submissionid, activityid, (select nvl(urgenttype, ''1'') from app where edoracceptno = rpad(prop1, 20)) as urgenttype, (case when prop5 = ''2'' then (select codename from code  where codetype = ''agentlevel'' and code = (select rpad(agentstar, 20) from app where edoracceptno=rpad(prop1, 20))) else '''' end),(select codename from code where codetype= ''CustomCheckResult'' and code = (select customcheckresult from app where edoracceptno = rpad(prop1, 20))), greatest(makedate, nvl((select max(makedate) from doc_main where doccode = mission.prop1), date ''1900-01-01'')) as greatdate, greatest(to_char(makedate, ''yyyymmdd'') || maketime, (select nvl(max(to_char(makedate, ''yyyymmdd'') || maketime), ''1900010100:00:00'') from doc_main where doccode = prop1)) as greattime,(case when prop5 = ''2'' then (select  agentstar from app where  edoracceptno = rpad(prop1, 20)) else '''' end) as agentstar  from mission where 1=1   and activityid = ''0000000007''  and processid = ''0000000000''  and exists (select 1 from app where (Preinputflag Is Null Or Preinputflag=''0'') and edoracceptno=rpad(mission.prop1,20,'' ''))  and defaultoperator is null  and exists (select 1 from app where applyfortype <> ''2'' and edoracceptno = rpad(prop1, 20)) and exists (select 1 from pitem where EdorAcceptNo = rpad(prop1, 20, '' '') and edortype in (select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) >= 0 and LowerLimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) < 0 and edorcode in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' And Edorpopedom = popedom.Edorpopedom) union select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and edorcode not in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' and  Edorpopedom = popedom.Edorpopedom)))   And Exists  (Select 1  From rrela a, mrela b Where a.Areaid = b.Areaid And b.Comcode in (Rpad(Substr(prop7, 1, 4), 10), Rpad(Substr(prop7, 1, 6), 10)) And a.Usercode =''zy1zgs'' and not exists (select 1 from ldcom where comcode = rpad(prop7, 10) and substr(comcode, 1, 6) in (select trim(code) from code where rpad(codealias,10) = b.Comcode and codetype = ''RiseCom''))) and (CreateOperator <> ''zy1zgs'' or (select edortype from pitem where edoracceptno = rpad(prop1, 20, '' '') and rownum =1 ) in (''AC'', ''BB'', ''CD'',''PC''))    And Exists (Select 1   From doc_Pages  Where Exists (Select 1   From doc_Main  Where Docid = doc_Pages.Docid  And Doccode = prop1))    And Exists (Select 1  From Lccont  Where Contno = (Select Contno  From pitem  Where Edoracceptno = Rpad(prop1, 20, '' '') And Rownum = 1) and Conttype != 2) order by urgenttype desc, greatdate, agentstar desc nulls last, greattime) where rownum<=10',
profile     => h,
name        => '59zyu0jtuz0v7',
description => '59zyu0jtuz0v7',
category    => 'default',
validate    => true,
replace     => true,
force_match => true );
end;
/


正确的执行计划如下
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |       |       |  5880 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |     1   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE                       |                             |     1 |    63 |            |          |
|*  4 |   TABLE ACCESS BY INDEX ROWID         | t_pol                       |     1 |    63 |     5   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | t_pol_INDEX_7               |     2 |       |     4   (0)| 00:00:01 |
|*  6 |    COUNT STOPKEY                      |                             |       |       |            |          |
|*  7 |     TABLE ACCESS BY INDEX ROWID       | t_pol                       |     1 |    71 |     5   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN                 | t_pol_INDEX_7               |     2 |       |     4   (0)| 00:00:01 |
|   9 |     SORT AGGREGATE                    |                             |     1 |    76 |            |          |
|* 10 |      TABLE ACCESS BY INDEX ROWID      | t_pol                       |     1 |    76 |     5   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN                | t_pol_INDEX_7               |     2 |       |     4   (0)| 00:00:01 |
|  12 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |     2   (0)| 00:00:01 |
|* 13 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |     1   (0)| 00:00:01 |
|* 14 |  COUNT STOPKEY                        |                             |       |       |            |          |
|* 15 |   FILTER                              |                             |       |       |            |          |
|* 16 |    TABLE ACCESS FULL                  | item                  |    83 |  1660 |     3   (0)| 00:00:01 |
|* 17 |    INDEX RANGE SCAN                   | PK_pitem               |     1 |    24 |     4   (0)| 00:00:01 |
|  18 |  SORT AGGREGATE                       |                             |     1 |    27 |            |          |
|* 19 |   TABLE ACCESS BY INDEX ROWID         | pitem                  |     1 |    27 |     5   (0)| 00:00:01 |
|* 20 |    INDEX RANGE SCAN                   | PK_pitem               |     1 |       |     4   (0)| 00:00:01 |
|* 21 |  TABLE ACCESS BY INDEX ROWID          | user                  |     1 |    28 |     2   (0)| 00:00:01 |
|* 22 |   INDEX UNIQUE SCAN                   | P_KEY_2                     |     1 |       |     1   (0)| 00:00:01 |
|  23 |  TABLE ACCESS BY INDEX ROWID          | app                   |     1 |    23 |     4   (0)| 00:00:01 |
|* 24 |   INDEX UNIQUE SCAN                   | PK_app                |     1 |       |     3   (0)| 00:00:01 |
|  25 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |     2   (0)| 00:00:01 |
|* 26 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |     1   (0)| 00:00:01 |
|  27 |    TABLE ACCESS BY INDEX ROWID        | app                   |     1 |    23 |     4   (0)| 00:00:01 |
|* 28 |     INDEX UNIQUE SCAN                 | PK_app                |     1 |       |     3   (0)| 00:00:01 |
|  29 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |     2   (0)| 00:00:01 |
|* 30 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |     1   (0)| 00:00:01 |
|  31 |    TABLE ACCESS BY INDEX ROWID        | app                   |     1 |    23 |     4   (0)| 00:00:01 |
|* 32 |     INDEX UNIQUE SCAN                 | PK_app                |     1 |       |     3   (0)| 00:00:01 |
|  33 |  SORT AGGREGATE                       |                             |     1 |    23 |            |          |
|  34 |   TABLE ACCESS BY INDEX ROWID         | doc_MAIN                 |     2 |    46 |     6   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN                   | IDX_doc_MAIN_DOCCD       |     2 |       |     4   (0)| 00:00:01 |
|  36 |  SORT AGGREGATE                       |                             |     1 |    32 |            |          |
|  37 |   TABLE ACCESS BY INDEX ROWID         | doc_MAIN                 |     2 |    64 |     6   (0)| 00:00:01 |
|* 38 |    INDEX RANGE SCAN                   | IDX_doc_MAIN_DOCCD       |     2 |       |     4   (0)| 00:00:01 |
|  39 |  TABLE ACCESS BY INDEX ROWID          | app                   |     1 |    23 |     4   (0)| 00:00:01 |
|* 40 |   INDEX UNIQUE SCAN                   | PK_app                |     1 |       |     3   (0)| 00:00:01 |
|* 41 |  COUNT STOPKEY                        |                             |       |       |            |          |
|  42 |   VIEW                                |                             |     1 |   589 |  5880   (1)| 00:01:11 |
|* 43 |    SORT ORDER BY STOPKEY              |                             |     1 |   178 |  5880   (1)| 00:01:11 |
|* 44 |     FILTER                            |                             |       |       |            |          |
|  45 |      NESTED LOOPS SEMI                |                             |     1 |   178 |  5809   (1)| 00:01:10 |
|  46 |       NESTED LOOPS SEMI               |                             |     1 |   176 |  5800   (1)| 00:01:10 |
|  47 |        NESTED LOOPS SEMI              |                             |     1 |   153 |  5797   (1)| 00:01:10 |
|* 48 |         TABLE ACCESS BY INDEX ROWID   | mission                   |  1265 |   160K|  1999   (1)| 00:00:24 |
|* 49 |          INDEX RANGE SCAN             | IDX_mission_ACTID         |  3006 |       |    17   (0)| 00:00:01 |
|* 50 |         TABLE ACCESS BY INDEX ROWID   | app                   |     1 |    23 |     3   (0)| 00:00:01 |
|* 51 |          INDEX UNIQUE SCAN            | PK_app                |     1 |       |     2   (0)| 00:00:01 |
|* 52 |        TABLE ACCESS BY INDEX ROWID    | app                   |     1 |    23 |     3   (0)| 00:00:01 |
|* 53 |         INDEX UNIQUE SCAN             | PK_app                |     1 |       |     2   (0)| 00:00:01 |
|  54 |       VIEW PUSHED PREDICATE           | VW_SQ_1                     |     1 |     2 |     9   (0)| 00:00:01 |
|  55 |        NESTED LOOPS                   |                             |     1 |    29 |     9   (0)| 00:00:01 |
|  56 |         TABLE ACCESS BY INDEX ROWID   | doc_MAIN                 |     1 |    22 |     6   (0)| 00:00:01 |
|* 57 |          INDEX RANGE SCAN             | IDX_doc_MAIN_DOCCD       |     2 |       |     4   (0)| 00:00:01 |
|* 58 |         INDEX RANGE SCAN              | IDX_doc_PAGdoc        |     1 |     7 |     3   (0)| 00:00:01 |
|* 59 |      INDEX RANGE SCAN                 | PK_pitem               |     1 |    24 |     4   (0)| 00:00:01 |
|  60 |       SORT UNIQUE                     |                             |     2 |    90 |    52  (43)| 00:00:01 |
|  61 |        UNION-ALL                      |                             |       |       |            |          |
|* 62 |         FILTER                        |                             |       |       |            |          |
|* 63 |          FILTER                       |                             |       |       |            |          |
|  64 |           NESTED LOOPS                |                             |     1 |    49 |     4   (0)| 00:00:01 |
|* 65 |            TABLE ACCESS BY INDEX ROWID| user                  |     1 |    28 |     2   (0)| 00:00:01 |
|* 66 |             INDEX UNIQUE SCAN         | P_KEY_2                     |     1 |       |     1   (0)| 00:00:01 |
|* 67 |            TABLE ACCESS BY INDEX ROWID| popedom               |     1 |    21 |     2   (0)| 00:00:01 |
|* 68 |             INDEX RANGE SCAN          | P_KEY_1                     |     1 |       |     1   (0)| 00:00:01 |
|  69 |          SORT AGGREGATE               |                             |     1 |    27 |            |          |
|* 70 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    27 |     5   (0)| 00:00:01 |
|* 71 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
|  72 |          SORT AGGREGATE               |                             |     1 |    27 |            |          |
|* 73 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    27 |     5   (0)| 00:00:01 |
|* 74 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
|  75 |          SORT AGGREGATE               |                             |     1 |    36 |            |          |
|* 76 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |     8   (0)| 00:00:01 |
|* 77 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |     4   (0)| 00:00:01 |
|  78 |          HASH UNIQUE                  |                             |     1 |    31 |     6  (17)| 00:00:01 |
|* 79 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |     5   (0)| 00:00:01 |
|* 80 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
|  81 |          SORT AGGREGATE               |                             |     1 |    36 |            |          |
|* 82 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |     8   (0)| 00:00:01 |
|* 83 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |     4   (0)| 00:00:01 |
|  84 |          HASH UNIQUE                  |                             |     1 |    31 |     6  (17)| 00:00:01 |
|* 85 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |     5   (0)| 00:00:01 |
|* 86 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
|* 87 |         FILTER                        |                             |       |       |            |          |
|* 88 |          FILTER                       |                             |       |       |            |          |
|  89 |           NESTED LOOPS                |                             |     1 |    41 |     4   (0)| 00:00:01 |
|* 90 |            TABLE ACCESS BY INDEX ROWID| user                  |     1 |    28 |     2   (0)| 00:00:01 |
|* 91 |             INDEX UNIQUE SCAN         | P_KEY_2                     |     1 |       |     1   (0)| 00:00:01 |
|* 92 |            TABLE ACCESS BY INDEX ROWID| popedom               |     1 |    13 |     2   (0)| 00:00:01 |
|* 93 |             INDEX RANGE SCAN          | P_KEY_1                     |     1 |       |     1   (0)| 00:00:01 |
|  94 |          SORT AGGREGATE               |                             |     1 |    36 |            |          |
|* 95 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |     8   (0)| 00:00:01 |
|* 96 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |     4   (0)| 00:00:01 |
|  97 |          HASH UNIQUE                  |                             |     1 |    31 |     6  (17)| 00:00:01 |
|* 98 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |     5   (0)| 00:00:01 |
|* 99 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
| 100 |          SORT AGGREGATE               |                             |     1 |    36 |            |          |
|*101 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |     8   (0)| 00:00:01 |
|*102 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |     4   (0)| 00:00:01 |
| 103 |          HASH UNIQUE                  |                             |     1 |    31 |     6  (17)| 00:00:01 |
|*104 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |     5   (0)| 00:00:01 |
|*105 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |     4   (0)| 00:00:01 |
| 106 |      NESTED LOOPS                     |                             |     1 |    54 |     3   (0)| 00:00:01 |
|*107 |       INDEX RANGE SCAN                | rrela_KEY_1        |     2 |    64 |     1   (0)| 00:00:01 |
| 108 |       INLIST ITERATOR                 |                             |       |       |            |          |
|*109 |        INDEX UNIQUE SCAN              | mrela_KEY_1         |     1 |    22 |     1   (0)| 00:00:01 |
| 110 |         NESTED LOOPS                  |                             |     1 |    58 |     3   (0)| 00:00:01 |
|*111 |          INDEX UNIQUE SCAN            | PK_LDCOM                    |     1 |    11 |     1   (0)| 00:00:01 |
|*112 |          TABLE ACCESS BY INDEX ROWID  | code                      |     1 |    47 |     2   (0)| 00:00:01 |
|*113 |           INDEX RANGE SCAN            | PK_code                   |     1 |       |     1   (0)| 00:00:01 |
|*114 |      TABLE ACCESS BY INDEX ROWID      | LCCONT                      |     1 |    23 |     4   (0)| 00:00:01 |
|*115 |       INDEX UNIQUE SCAN               | PK_LCCONT                   |     1 |       |     3   (0)| 00:00:01 |
|*116 |        COUNT STOPKEY                  |                             |       |       |            |          |
|*117 |         INDEX RANGE SCAN              | PK_pitem               |     1 |    42 |     4   (0)| 00:00:01 |
|*118 |      COUNT STOPKEY                    |                             |       |       |            |          |
|*119 |       INDEX RANGE SCAN                | PK_pitem               |     1 |    24 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------


前面都是铺垫，主角来了
后来有一天，需要在登陆触发器里为部分客户机的session设置cursor_sharing=force。
结果这条SQL就有了两个版本，一个是之前的非绑定变了的SQL，一个是oracle改写的强制绑定变量版本。
被oracle改写为绑定变量版本的SQL统统执行不出来了。


执行计划如下
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |       |       |       |  2292K(100)|          |
|*  1 |  FILTER                               |                             |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID         | code                      |     1 |    59 |       |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                  | PK_code                   |     1 |       |       |     1   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE                       |                             |     1 |    63 |       |            |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID         | t_pol                       |     1 |    63 |       |     5   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN                   | t_pol_INDEX_7               |     2 |       |       |     4   (0)| 00:00:01 |
|*  7 |    COUNT STOPKEY                      |                             |       |       |       |            |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID       | t_pol                       |     1 |    71 |       |     5   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                 | t_pol_INDEX_7               |     2 |       |       |     4   (0)| 00:00:01 |
|  10 |     SORT AGGREGATE                    |                             |     1 |    76 |       |            |          |
|* 11 |      TABLE ACCESS BY INDEX ROWID      | t_pol                       |     1 |    76 |       |     5   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN                | t_pol_INDEX_7               |     2 |       |       |     4   (0)| 00:00:01 |
|  13 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |       |     2   (0)| 00:00:01 |
|* 14 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |       |     1   (0)| 00:00:01 |
|* 15 |  COUNT STOPKEY                        |                             |       |       |       |            |          |
|* 16 |   FILTER                              |                             |       |       |       |            |          |
|* 17 |    TABLE ACCESS FULL                  | item                  |    83 |  1660 |       |     3   (0)| 00:00:01 |
|* 18 |    INDEX RANGE SCAN                   | PK_pitem               |     1 |    24 |       |     4   (0)| 00:00:01 |
|  19 |  SORT AGGREGATE                       |                             |     1 |    27 |       |            |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID         | pitem                  |     1 |    27 |       |     5   (0)| 00:00:01 |
|* 21 |    INDEX RANGE SCAN                   | PK_pitem               |     1 |       |       |     4   (0)| 00:00:01 |
|* 22 |  TABLE ACCESS BY INDEX ROWID          | user                  |     1 |    28 |       |     2   (0)| 00:00:01 |
|* 23 |   INDEX UNIQUE SCAN                   | P_KEY_2                     |     1 |       |       |     1   (0)| 00:00:01 |
|  24 |  TABLE ACCESS BY INDEX ROWID          | app                   |     1 |    23 |       |     4   (0)| 00:00:01 |
|* 25 |   INDEX UNIQUE SCAN                   | PK_app                |     1 |       |       |     3   (0)| 00:00:01 |
|  26 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |       |     2   (0)| 00:00:01 |
|* 27 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |       |     1   (0)| 00:00:01 |
|  28 |    TABLE ACCESS BY INDEX ROWID        | app                   |     1 |    23 |       |     4   (0)| 00:00:01 |
|* 29 |     INDEX UNIQUE SCAN                 | PK_app                |     1 |       |       |     3   (0)| 00:00:01 |
|  30 |  TABLE ACCESS BY INDEX ROWID          | code                      |     1 |    59 |       |     2   (0)| 00:00:01 |
|* 31 |   INDEX UNIQUE SCAN                   | PK_code                   |     1 |       |       |     1   (0)| 00:00:01 |
|  32 |    TABLE ACCESS BY INDEX ROWID        | app                   |     1 |    23 |       |     4   (0)| 00:00:01 |
|* 33 |     INDEX UNIQUE SCAN                 | PK_app                |     1 |       |       |     3   (0)| 00:00:01 |
|  34 |  SORT AGGREGATE                       |                             |     1 |    23 |       |            |          |
|  35 |   TABLE ACCESS BY INDEX ROWID         | doc_MAIN                 |     2 |    46 |       |     6   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN                   | IDX_doc_MAIN_DOCCD       |     2 |       |       |     4   (0)| 00:00:01 |
|  37 |  SORT AGGREGATE                       |                             |     1 |    32 |       |            |          |
|  38 |   TABLE ACCESS BY INDEX ROWID         | doc_MAIN                 |     2 |    64 |       |     6   (0)| 00:00:01 |
|* 39 |    INDEX RANGE SCAN                   | IDX_doc_MAIN_DOCCD       |     2 |       |       |     4   (0)| 00:00:01 |
|  40 |  TABLE ACCESS BY INDEX ROWID          | app                   |     1 |    23 |       |     4   (0)| 00:00:01 |
|* 41 |   INDEX UNIQUE SCAN                   | PK_app                |     1 |       |       |     3   (0)| 00:00:01 |
|* 42 |  COUNT STOPKEY                        |                             |       |       |       |            |          |
|  43 |   VIEW                                |                             |     1 |   638 |       |  2292K  (1)| 07:38:26 |
|* 44 |    SORT ORDER BY STOPKEY              |                             |     1 |   191 |       |  2292K  (1)| 07:38:26 |
|* 45 |     FILTER                            |                             |       |       |       |            |          |
|  46 |      NESTED LOOPS SEMI                |                             |     1 |   191 |       |  2292K  (1)| 07:38:25 |
|  47 |       NESTED LOOPS SEMI               |                             |     1 |   176 |       |  5070   (1)| 00:01:01 |
|  48 |        NESTED LOOPS SEMI              |                             |     1 |   153 |       |  5067   (1)| 00:01:01 |
|* 49 |         TABLE ACCESS BY INDEX ROWID   | mission                   |  1022 |   129K|       |  1999   (1)| 00:00:24 |
|* 50 |          INDEX RANGE SCAN             | IDX_mission_ACTID         |  3006 |       |       |    17   (0)| 00:00:01 |
|* 51 |         TABLE ACCESS BY INDEX ROWID   | app                   |     1 |    23 |       |     3   (0)| 00:00:01 |
|* 52 |          INDEX UNIQUE SCAN            | PK_app                |     1 |       |       |     2   (0)| 00:00:01 |
|* 53 |        TABLE ACCESS BY INDEX ROWID    | app                   |     1 |    23 |       |     3   (0)| 00:00:01 |
|* 54 |         INDEX UNIQUE SCAN             | PK_app                |     1 |       |       |     2   (0)| 00:00:01 |
|* 55 |       VIEW                            | VW_SQ_1                     |   378M|  5411M|       |  2286K  (1)| 07:37:24 |
|* 56 |        FILTER                         |                             |       |       |       |            |          |
|* 57 |         HASH JOIN                     |                             |   460M|    12G|  5377M|  2286K  (1)| 07:37:24 |
|  58 |          TABLE ACCESS FULL            | doc_MAIN                 |   165M|  3479M|       |   827K  (1)| 02:45:33 |
|  59 |          INDEX FAST FULL SCAN         | IDX_doc_PAGdoc        |   474M|  3167M|       |   757K  (1)| 02:31:32 |
|* 60 |      INDEX RANGE SCAN                 | PK_pitem               |     1 |    24 |       |     4   (0)| 00:00:01 |
|  61 |       SORT UNIQUE                     |                             |     2 |    90 |       |    52  (43)| 00:00:01 |
|  62 |        UNION-ALL                      |                             |       |       |       |            |          |
|* 63 |         FILTER                        |                             |       |       |       |            |          |
|* 64 |          FILTER                       |                             |       |       |       |            |          |
|  65 |           NESTED LOOPS                |                             |     1 |    49 |       |     4   (0)| 00:00:01 |
|* 66 |            TABLE ACCESS BY INDEX ROWID| user                  |     1 |    28 |       |     2   (0)| 00:00:01 |
|* 67 |             INDEX UNIQUE SCAN         | P_KEY_2                     |     1 |       |       |     1   (0)| 00:00:01 |
|* 68 |            TABLE ACCESS BY INDEX ROWID| popedom               |     1 |    21 |       |     2   (0)| 00:00:01 |
|* 69 |             INDEX RANGE SCAN          | P_KEY_1                     |     1 |       |       |     1   (0)| 00:00:01 |
|  70 |          SORT AGGREGATE               |                             |     1 |    27 |       |            |          |
|* 71 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    27 |       |     5   (0)| 00:00:01 |
|* 72 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
|  73 |          SORT AGGREGATE               |                             |     1 |    27 |       |            |          |
|* 74 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    27 |       |     5   (0)| 00:00:01 |
|* 75 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
|  76 |          SORT AGGREGATE               |                             |     1 |    36 |       |            |          |
|* 77 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |       |     8   (0)| 00:00:01 |
|* 78 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |       |     4   (0)| 00:00:01 |
|  79 |          HASH UNIQUE                  |                             |     1 |    31 |       |     6  (17)| 00:00:01 |
|* 80 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |       |     5   (0)| 00:00:01 |
|* 81 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
|  82 |          SORT AGGREGATE               |                             |     1 |    36 |       |            |          |
|* 83 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |       |     8   (0)| 00:00:01 |
|* 84 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |       |     4   (0)| 00:00:01 |
|  85 |          HASH UNIQUE                  |                             |     1 |    31 |       |     6  (17)| 00:00:01 |
|* 86 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |       |     5   (0)| 00:00:01 |
|* 87 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
|* 88 |         FILTER                        |                             |       |       |       |            |          |
|* 89 |          FILTER                       |                             |       |       |       |            |          |
|  90 |           NESTED LOOPS                |                             |     1 |    41 |       |     4   (0)| 00:00:01 |
|* 91 |            TABLE ACCESS BY INDEX ROWID| user                  |     1 |    28 |       |     2   (0)| 00:00:01 |
|* 92 |             INDEX UNIQUE SCAN         | P_KEY_2                     |     1 |       |       |     1   (0)| 00:00:01 |
|* 93 |            TABLE ACCESS BY INDEX ROWID| popedom               |     1 |    13 |       |     2   (0)| 00:00:01 |
|* 94 |             INDEX RANGE SCAN          | P_KEY_1                     |     1 |       |       |     1   (0)| 00:00:01 |
|  95 |          SORT AGGREGATE               |                             |     1 |    36 |       |            |          |
|* 96 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |       |     8   (0)| 00:00:01 |
|* 97 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |       |     4   (0)| 00:00:01 |
|  98 |          HASH UNIQUE                  |                             |     1 |    31 |       |     6  (17)| 00:00:01 |
|* 99 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |       |     5   (0)| 00:00:01 |
|*100 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
| 101 |          SORT AGGREGATE               |                             |     1 |    36 |       |            |          |
|*102 |           TABLE ACCESS BY INDEX ROWID | bmission                   |     1 |    36 |       |     8   (0)| 00:00:01 |
|*103 |            INDEX RANGE SCAN           | IDX_bmission_MP1           |     6 |       |       |     4   (0)| 00:00:01 |
| 104 |          HASH UNIQUE                  |                             |     1 |    31 |       |     6  (17)| 00:00:01 |
|*105 |           TABLE ACCESS BY INDEX ROWID | pitem                  |     1 |    31 |       |     5   (0)| 00:00:01 |
|*106 |            INDEX RANGE SCAN           | IDX_pitem_EDORACCEPTNO |     1 |       |       |     4   (0)| 00:00:01 |
| 107 |      NESTED LOOPS                     |                             |     1 |    54 |       |     3   (0)| 00:00:01 |
|*108 |       INDEX RANGE SCAN                | rrela_KEY_1        |     2 |    64 |       |     1   (0)| 00:00:01 |
| 109 |       INLIST ITERATOR                 |                             |       |       |       |            |          |
|*110 |        INDEX UNIQUE SCAN              | mrela_KEY_1         |     1 |    22 |       |     1   (0)| 00:00:01 |
| 111 |         NESTED LOOPS                  |                             |     1 |    58 |       |     3   (0)| 00:00:01 |
|*112 |          INDEX UNIQUE SCAN            | PK_LDCOM                    |     1 |    11 |       |     1   (0)| 00:00:01 |
|*113 |          TABLE ACCESS BY INDEX ROWID  | code                      |     1 |    47 |       |     2   (0)| 00:00:01 |
|*114 |           INDEX RANGE SCAN            | PK_code                   |     1 |       |       |     1   (0)| 00:00:01 |
|*115 |      TABLE ACCESS BY INDEX ROWID      | LCCONT                      |     1 |    23 |       |     4   (0)| 00:00:01 |
|*116 |       INDEX UNIQUE SCAN               | PK_LCCONT                   |     1 |       |       |     3   (0)| 00:00:01 |
|*117 |        COUNT STOPKEY                  |                             |       |       |       |            |          |
|*118 |         INDEX RANGE SCAN              | PK_pitem               |     1 |    42 |       |     4   (0)| 00:00:01 |
|*119 |      COUNT STOPKEY                    |                             |       |       |       |            |          |
|*120 |       INDEX RANGE SCAN                | PK_pitem               |     1 |    24 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
......
......
......
Note
-----
   - SQL profile 59zyu0jtuz0v7 used for this statement


看来还是用了sql profile的，但问题是存储照片影像信息的两张大表走的是全表扫描和索引FFS，而且是在循环里做这件事。
不但跑不出来，而且大大消耗临时表空间，致使数据库一些SQL直接报临时表空间不足。


执行计划主要区别
正常的执行计划（cursor_sharing=exact）
|  54 |       VIEW PUSHED PREDICATE           | VW_SQ_1                     |     1 |     2 |     9   (0)| 00:00:01 |
|  55 |        NESTED LOOPS                   |                             |     1 |    29 |     9   (0)| 00:00:01 |
|  56 |         TABLE ACCESS BY INDEX ROWID   | doc_MAIN                 |     1 |    22 |     6   (0)| 00:00:01 |
|* 57 |          INDEX RANGE SCAN             | IDX_doc_MAIN_DOCCD       |     2 |       |     4   (0)| 00:00:01 |
|* 58 |         INDEX RANGE SCAN              | IDX_doc_PAGdoc        |     1 |     7 |     3   (0)| 00:00:01 |

异常的执行计划（cursor_sharing=force）
|* 55 |       VIEW                            | VW_SQ_1                     |   378M|  5411M|       |  2286K  (1)| 07:37:24 |
|* 56 |        FILTER                         |                             |       |       |       |            |          |
|* 57 |         HASH JOIN                     |                             |   460M|    12G|  5377M|  2286K  (1)| 07:37:24 |
|  58 |          TABLE ACCESS FULL            | doc_MAIN                 |   165M|  3479M|       |   827K  (1)| 02:45:33 |
|  59 |          INDEX FAST FULL SCAN         | IDX_doc_PAGdoc        |   474M|  3167M|       |   757K  (1)| 02:31:32 |


很奇怪为什么profile里的hint被忽略了。
总之就是为非绑定变量SQL创建的sql profile，对oracle自己改写的SQL支持有问题。
于是plan A来了
创建一个针对oracle强制绑定变量改写的SQL的sql profile，设置force_match为false。这样两个SQL两个sql profile不就好了么。
结果，强制绑定变量的SQL按新的sql profile执行，非常爽。
但是由于两种SQL的force_matching_signature一样，因此当创建第二个sql profile的时候，之前那个force_match为true的sql profile消失了。

既然一个force_matching_signature只能有一个sql profile，而且强制绑定变量的SQL已经有一个理想的执行计划在共享池了，干脆把它做成sql baseline把。
于是有了plan B：
1 将强制绑定变量的SQL的理想执行计划做成sql baseline
2 重新创建早先给非绑定变量版本用的sql profile
结果，现实太残酷了，sql profile的优先级高于sql baseline。强制绑定变量的SQL还是走了sql profile，然后跑不出结果了。问题依旧。


于是痛定思痛，仔细对比outline发现区别
正常执行计划的outline
PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)
INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE" "doc_MAIN"."SUBTYPE"))
INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")


异常执行计划的outline
FULL(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42")
INDEX_FFS(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_HASH(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41")


原来hint里代码块的别名在设置cursor_sharing=force后发生了变化。从700B301E变成1B0ECD80了，最重要的还是异常执行计划里没有谓词推入PUSH_PRED。
经过在审计库（与生产库只差一个月数据）反复测试，就算用700B301E和1B0ECD80两个别名写两段正确的OUTLINE放到sql profile里，还是不行，根本不起作用。
最后的解决方案是直接让这个子查询no_unnest，倒是把问题解决了。。。
顺便把有时候走and-equal的那个索引也给禁了，保证它只走一个最好的索引，并且禁用了索引转位图。


最终在sql profile里加上这段，绑定变量和非绑定变量的两个SQL终于可以适用一个sql profile了。
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'no_unnest(@sel$41)',
'no_index(@"SEL$8"   "pitem"@"SEL$8" pitem_INDEX_3)',
'no_index(@"SEL$9"   "pitem"@"SEL$9" pitem_INDEX_3)',
'no_index(@"SEL$25" "pitem"@"SEL$25" pitem_INDEX_3)',
'no_index(@"SEL$27" "pitem"@"SEL$27" pitem_INDEX_3)',
'no_index(@"SEL$28" "pitem"@"SEL$28" pitem_INDEX_3)',
'no_index(@"SEL$29" "pitem"@"SEL$29" pitem_INDEX_3)',
'no_index(@"SEL$33" "pitem"@"SEL$33" pitem_INDEX_3)',
'no_index(@"SEL$35" "pitem"@"SEL$35" pitem_INDEX_3)',
'no_index(@"SEL$44" "pitem"@"SEL$44" pitem_INDEX_3)',
'no_index(@"SEL$40" "pitem"@"SEL$40" pitem_INDEX_3)',
'no_index(@"SEL$22" "pitem"@"SEL$22" pitem_INDEX_3)',
'no_index(@"SEL$10" "pitem"@"SEL$10" pitem_INDEX_3)',

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

0
0
上一篇： 用push_subq优化批处理统计SQL
下一篇： 大表范围扫描走SORT MERGE JOIN的SQL优化
请登录后发表评论 登录
全部评论
liiinuuux

注册时间：2012-11-12

  • 博文量
    94
  • 访问量
    353438

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593