运行很快的两个单独的SQL在一起运行慢了20多倍

tolywang

Oracle10.2.0.4 ,  自动分析统计 。


/* Formatted on 2009/11/04 10:42 (Formatter Plus v4.8.8) */
SELECT   LEVEL, b.ID, NULL parent_id, b.name_en, b.name_zh_tw, b.name_zh_cn,
         b.seq, b.TYPE, b.description,
         DECODE (b.action,
                 NULL, '',
                 'selectFunction(''' || b.ID || ''',''' || b.action || ''')'
                ) action,
         DECODE (b.TYPE,
                 'A', 'menu.gif',
                 'M', 'folders.gif',
                 'F', 'drafts.gif',
                 'R', 'helper.gif',
                 'W', 'working.gif',
                 'folder.gif'
                ) image
    FROM pf_access_log al, pf_access_node b
   WHERE al.active_flag = '1'
     AND b.active_flag = '1'
     AND al.access_node_id = b.ID
     AND al.cur_id = 'A97229D00AA120B2003FDB294D09A060'
     AND b.ID IN
     
     
     (

            SELECT     a.ID
                  FROM (SELECT DISTINCT ID, app_id, parent_id, seq, name_en,
                                        name_zh_tw, name_zh_cn, TYPE,
                                        description, action
                                   FROM (SELECT     a.*
                                               FROM pf_access_node a
                                              WHERE a.active_flag = '1'
                                         START WITH (   EXISTS (
                                                           SELECT *
                                                             FROM pf_role_user b,
                                                                  pf_role c,
                                                                  pf_role_access d
                                                            WHERE c.active_flag =
                                                                           '1'
                                                              AND b.role_id =
                                                                          c.ID
                                                              AND c.ID =
                                                                     d.role_id
                                                              AND b.user_id =
                                                                     'A97229D00AA120B2003FDB294D09A060'
                                                              AND d.access_node_id =
                                                                          a.ID)
                                                     OR EXISTS (
                                                           SELECT *
                                                             FROM pf_agent
                                                            WHERE active_flag =
                                                                           '1'
                                                              AND agent_type =
                                                                        'FUNC'
                                                              AND agent_id =
                                                                     '9EEF6CDA0A86825101AFF012F9B905C1'
                                                              AND ref_id =
                                                                          a.ID
                                                              AND NVL
                                                                     (start_date,
                                                                      SYSDATE
                                                                     ) <=
                                                                       SYSDATE
                                                              AND NVL
                                                                     (end_date,
                                                                      SYSDATE
                                                                     ) >=
                                                                       SYSDATE)
                                                    )
                                         CONNECT BY PRIOR a.ID = a.parent_id
                                         UNION
                                         SELECT     a.*
                                               FROM pf_access_node a
                                              WHERE a.active_flag = '1'
                                         START WITH (   EXISTS (
                                                           SELECT *
                                                             FROM pf_role_user b,
                                                                  pf_role c,
                                                                  pf_role_access d
                                                            WHERE c.active_flag =
                                                                           '1'
                                                              AND b.role_id =
                                                                          c.ID
                                                              AND c.ID =
                                                                     d.role_id
                                                              AND b.user_id =
                                                                     'A97229D00AA120B2003FDB294D09A060'
                                                              AND d.access_node_id =
                                                                          a.ID)
                                                     OR EXISTS (
                                                           SELECT *
                                                             FROM pf_agent
                                                            WHERE active_flag =
                                                                           '1'
                                                              AND agent_type =
                                                                        'FUNC'
                                                              AND agent_id =
                                                                     '9EEF6CDA0A86825101AFF012F9B905C1'
                                                              AND ref_id =
                                                                          a.ID
                                                              AND NVL
                                                                     (start_date,
                                                                      SYSDATE
                                                                     ) <=
                                                                       SYSDATE
                                                              AND NVL
                                                                     (end_date,
                                                                      SYSDATE
                                                                     ) >=
                                                                       SYSDATE)
                                                    )
                                         CONNECT BY PRIOR a.parent_id = a.ID)
                               ORDER BY parent_id, seq) a
                 WHERE 1 = 1
            START WITH a.parent_id IS NULL
            CONNECT BY PRIOR a.ID = a.parent_id
            
            
            
       )
            
ORDER BY al.access_times   DESC   


整个SQL运行需要 56秒 ， 中间那一段需要 1~2 秒 。最大的table 也就1万笔记录 。其他都是1000多笔记录的小表 。
这里我们暂且不管执行计划 。

执行中间那一段SQL语句，得出ID 为100个记录 。 将100个记录拿出来当作常量来执行，整个SQL运行需要2秒 。




SELECT   LEVEL, b.ID, NULL parent_id, b.name_en, b.name_zh_tw,
                 b.name_zh_cn, b.seq, b.TYPE, b.description,
                 DECODE (b.action,
                         NULL, '',
                            'selectFunction('''
                         || b.ID
                         || ''','''
                         || b.action
                         || ''')'
                        ) action,
                 DECODE (b.TYPE,
                         'A', 'menu.gif',
                         'M', 'folders.gif',
                         'F', 'drafts.gif',
                         'R', 'helper.gif',
                         'W', 'working.gif',
                         'folder.gif'
                        ) image
            FROM pf_access_log a, pf_access_node b
           WHERE a.active_flag = '1'
             AND b.active_flag = '1'
             AND a.access_node_id = b.ID
             AND a.cur_id = 'A97229D00AA120B2003FDB294D09A060'
             AND b.ID IN
            
        (
                           
'1B9C09500AA1213001D36DFEA6344E78',
'1B9DA9130AA1213001D36DFE69D9CB9B',
'6FD84BC10A868294006A3960CF1EF8F7',
'29D693D30AA120D601295FE866598112',
'29D7F3410AA120D601295FE8FEEB8A8A',
'6881642B0A86829400A94237E92B114C',
'6882A5E20A86829400A94237F1AE58D6',
'7D3FCA7D0AA1226F013C550F5230A625',
'6C07DE110A86829400C45EE2F8DCF1C1',
'7D84C6890AA1226F006A63D3188AEA08',
'7D7F64D50AA1226F006A63D3A4C28B4A',
'7D85603D0AA1226F006A63D37F9082E1',
'7D8603C50AA1226F006A63D3EAB9D172',
'97C087040A86308F002069B196C2786C',
'087530D00A86309E003C084AC5C55059',
'221B09DF0A8630A0003F8CD11060590C',
'9071BCCB0A9D8C7601F7309A80858BDB',
'97C125E40A86308F002069B130B5AFA6',
'B00A584E0A863099007DA670627C2E5E',
'B00AE8D60A863099007DA6706D133011',
'B3C3F77B0AA120B2009D92159244984C',
'0CD6787E0A8682940061A2EC98DB7AA2',
'B3C685880AA120B2009D921547EE4C25',
'B3C77FF60AA120B2009D9215DDAC2974',
'B3C926910AA120B2009D9215E5CFF7DE',
'B3CA58E70AA120B2009D921573C49010',
'B3CB88CC0AA120B2009D92157CA5E399',
'B3CD42630AA120B2009D9215461FE014',
'DF19B9C40AA121300094CB8BD1D167EA',
'E45537880AA1226F01BF9B456D87EFDA',
'E85929120AA121300179779569BEF303',
'E9A0E3100AA12130003AFB99A0BDB053',
'B3CD8E7F0AA120B2009D9215A72FD925',
'55B78E0E0AA1213001264EAB86952C5C',
'567E05D60AA12130019A0203F4CBC5A2',
'B7D01CF10AA1226F0087D894DFE1FEEB',
'B850B9B80AA1226F008207B231AC619B',
'B85301A90AA1226F008207B2F7BF3C60',
'B520D2210A0D085601EB5666475C4547',
'B5222C810A0D085601EB566651325E46',
'E4D249CC0AA1226F01E323825A3BF1E7',
'E4D344B00AA1226F01E32382A82BBC62',
'E4D539010AA1226F01E32382621C3A83',
'E4D667C80AA1226F01E32382FCFCF801',
'E4D6B5BD0AA1226F01E323826714663F',
'E4D6F0470AA1226F01E32382856C73C9',
'E4D7403F0AA1226F01E32382CB2F1667',
'E4D7A2580AA1226F01E32382CB3D4909',
'E4D81EFF0AA1226F01E32382B194967E',
'E4D8C42E0AA1226F01E32382863F915C',
'E4D912510AA1226F01E32382F809F5A4',
'F8BEF5020AA1226F00CC74390CD20748',
'BD2D0AC40AA1230000968B350145DA62',
'6B1DB2180AA120C000FA00941C5230C0',
'AB29C1880AA12300007B7A29B98C2F9B',
'AB2D50A20AA12300007B7A29703D1827',
'B20257900AA1226F00859D9872DF9BDC',
'BC2AA31C0A8682940097B815CBDAB94B',
'BD2F883A0AA1230000968B358E2D6EBE',
'140661CB0AA1226F00B27310A76C13BA',
'231422DE0AA1226F007696F0674DC45F',
'9D9F1CCE0AA1226F00853D85A98590D7',
'9DA06EE20AA1226F00853D856A60694D',
'AB305BD50AA12300007B7A29B0FFE0A6',
'AB30E0580AA12300007B7A29380855B8',
'AB31670D0AA12300007B7A291755DB73',
'B6B9629B0AA1226F0087D894DD0193F6',
'BD30BBB90AA1230000968B35C62DCFAF',
'BD30FDC40AA1230000968B35239B8F48',
'BD3132030AA1230000968B3587299488',
'BD31765F0AA1230000968B352BB9B66E',
'C5E1E9AC0A0D08560135324998F023E4',
'C5E251EC0A0D085601353249E4E3458B',
'E0C08A880A0D0858012D64E9FF504D70',
'E4DE14100AA1226F01E3238216F09C3F',
'E4DE65EC0AA1226F01E323826F94379E',
'CD30DEB10AA1226F00C722436198AD7E',
'C0468B0E0A0D085601353249942E457F',
'C04917380A0D08560135324913CAB513',
'CD368F7A0AA1226F00C72243DB150968',
'CD36E7D80AA1226F00C72243D129A2C6',
'CD31F4280AA1226F00C72243011AFC91',
'549753DA0AA120D601DB484D2679547F',
'8CF341120AA1226F01A734FF92C33BA7',
'8CF415310AA1226F01A734FFEE7E35E7',
'8CF492B20AA1226F01A734FF78DF5200',
'8CF4EB830AA1226F01A734FFD4E17111',
'8CF56A4C0AA1226F01A734FF651DA554',
'8CF6193F0AA1226F01A734FF700E6F90',
'8CF66B590AA1226F01A734FF2E6B5006',
'8CF6ED8D0AA1226F01A734FF5E6AF5C9',
'8CF77CC20AA1226F01A734FF16008DD5',
'8CF829440AA1226F01A734FF03C3C815',
'8CF88E3A0AA1226F01A734FF8CFE70BA',
'E4DB1D970AA1226F01E32382C317666E',
'E4DB77D00AA1226F01E32382E6906B6E',
'F1271AB70A9D8C7601DEADF08AE34924',
'F128403C0A9D8C7601DEADF0688D1B9E',
'FEF86DC10A86309E003335AE13E0C1B9',
'FEF8B76C0A86309E003335AE00D478A1'
  )
            
        ORDER BY a.access_times DESC

EnochPersist

你还是把上面两个的执行计划发出来看看吧
另外你把这个地方该以下在执行一下
and b.id is not null
AND b.ID IN （）

棉花糖ONE

应该是子查询内的行数评估不对导致

tolywang

原帖由 棉花糖ONE 于 2009-11-4 13:39 发表
应该是子查询内的行数评估不对导致

用常量取代的那一段子查询单独分析执行计划，Cost=772 , Card=1726 ,  实际得出的值应该是 100 rows . 是这个原因导致 ？

whpzyp

估计执行计划用的连接不对！

tolywang

找到一篇文章， 也是说start with .. connect by 树形查询在评估cardinality时存在着问题 。

http://www.laoxiong.net/take-care-of-using-connect-by-query.html