请求各位看看这条SQL优化是否还有另外更好的方案 ？

kelvin_lok

请求各位看看这条SQL优化是否还有另外更好的方案 ？  边幅过大，2楼继续

原SQL语句：   
SELECT   NVL (STU.STUD_AREA, ' ') AS STUD_AREA,  
           NVL (STU.COMPANY, ' ') AS COMPANY,  
           NVL (STU.LMS_NO, ' ') AS LMS_NO,  
           NVL (STU.MOBILE_NO, ' ') AS MOBILE_NO,  
           NVL (STU.EMAIL, ' ') AS EMAIL,  
           LSR.USER_NAME AS FIELD1,  
           NVL (LSR.GROUP_NAME, ' ') AS FIELD2,  
           NVL (LSR.ACT_POINT01, ' ') AS FIELD3,  
           NVL (LSR.ACT_POINT02, ' ') AS FIELD4,  
           NVL (LSR.ACT_POINT03, ' ') AS FIELD5,  
           NVL (LSR.ACT_POINT04, ' ') AS FIELD6,  
           NVL (LSR.ACT_POINT05, ' ') AS FIELD7,  
           NVL (LSR.ACT_POINT06, ' ') AS FIELD8,  
           NVL (LSR.ACT_SUMPOINT01, ' ') AS FIELD23,  
           NVL (LSR.ACT_SUMPOINT02, ' ') AS FIELD24,  
           NVL (LSR.ACT_SUMPOINT03, ' ') AS FIELD25,  
           NVL (LSR.ACT_SUMPOINT04, ' ') AS FIELD26,  
           NVL (LSR.ACT_SUMPOINT05, ' ') AS FIELD27,  
           NVL (LSR.ACT_SUMPOINT06, ' ') AS FIELD28,  
           LSR.SUMPOINT AS FIELD43,  
           NVL (  
              (SELECT   TO_CHAR (LTU.TEST_POINT) TEST_POINT  
                 FROM   LCMS_TEST_USER LTU,  
                        LCMS_TEST_TESTINFO LTI,  
                        LCMS_TERMCOURSE_TASK LTT  
                WHERE       LTU.ISDELETED = 'N'  
                        AND LTI.ISDELETED = 'N'  
                        AND LTT.ISDELETED = 'N'  
                        AND LTI.ENTITY_ID = LTT.TASK_ID  
                        AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
                        AND LTT.TERMCOURSE_ID =  
                              '4a592995ac1086a52f3c2f3c964bf835'  
                        AND LTI.TEST_ID = LTU.TEST_ID  
                        AND LTU.USER_ID = LSR.USER_ID  
                        AND LTT.ORDER_NO = '1'),  
              ' '  
           )  
              TASK1,  
           NVL (  
              (SELECT   TO_CHAR (LTU.TEST_POINT) TEST_POINT  
                 FROM   LCMS_TEST_USER LTU,  
                        LCMS_TEST_TESTINFO LTI,  
                        LCMS_TERMCOURSE_TASK LTT  
                WHERE       LTU.ISDELETED = 'N'  
                        AND LTI.ISDELETED = 'N'  
                        AND LTT.ISDELETED = 'N'  
                        AND LTI.ENTITY_ID = LTT.TASK_ID  
                        AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
                        AND LTT.TERMCOURSE_ID =  
                              '4a592995ac1086a52f3c2f3c964bf835'  
                        AND LTI.TEST_ID = LTU.TEST_ID  
                        AND LTU.USER_ID = LSR.USER_ID  
                        AND LTT.ORDER_NO = '2'),  
              ' '  
           )  
              TASK2,  
           NVL (  
              (SELECT   TO_CHAR (LTU.TEST_POINT) TEST_POINT  
                 FROM   LCMS_TEST_USER LTU,  
                        LCMS_TEST_TESTINFO LTI,  
                        LCMS_TERMCOURSE_TASK LTT  
                WHERE       LTU.ISDELETED = 'N'  
                        AND LTI.ISDELETED = 'N'  
                        AND LTT.ISDELETED = 'N'  
                        AND LTI.ENTITY_ID = LTT.TASK_ID  
                        AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
                        AND LTT.TERMCOURSE_ID =  
                              '4a592995ac1086a52f3c2f3c964bf835'  
                        AND LTI.TEST_ID = LTU.TEST_ID  
                        AND LTU.USER_ID = LSR.USER_ID  
                        AND LTT.ORDER_NO = '3'),  
              ' '  
           )  
              TASK3,  
           NVL (  
              (SELECT   TO_CHAR (LTU.TEST_POINT) TEST_POINT  
                 FROM   LCMS_TEST_USER LTU,  
                        LCMS_TEST_TESTINFO LTI,  
                        LCMS_TERMCOURSE_TASK LTT  
                WHERE       LTU.ISDELETED = 'N'  
                        AND LTI.ISDELETED = 'N'  
                        AND LTT.ISDELETED = 'N'  
                        AND LTI.ENTITY_ID = LTT.TASK_ID  
                        AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
                        AND LTT.TERMCOURSE_ID =  
                              '4a592995ac1086a52f3c2f3c964bf835'  
                        AND LTI.TEST_ID = LTU.TEST_ID  
                        AND LTU.USER_ID = LSR.USER_ID  
                        AND LTT.ORDER_NO = '4'),  
              ' '  
           )  
              TASK4,  
           NVL (  
              (SELECT   TO_CHAR (LTU.TEST_POINT) TEST_POINT  
                 FROM   LCMS_TEST_USER LTU,  
                        LCMS_TEST_TESTINFO LTI,  
                        LCMS_TERMCOURSE_TASK LTT  
                WHERE       LTU.ISDELETED = 'N'  
                        AND LTI.ISDELETED = 'N'  
                        AND LTT.ISDELETED = 'N'  
                        AND LTI.ENTITY_ID = LTT.TASK_ID  
                        AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
                        AND LTT.TERMCOURSE_ID =  
                              '4a592995ac1086a52f3c2f3c964bf835'  
                        AND LTI.TEST_ID = LTU.TEST_ID  
                        AND LTU.USER_ID = LSR.USER_ID  
                        AND LTT.ORDER_NO = '5'),  
              ' '  
           )  
              TASK5,  
           (SELECT   COUNT (LFF.FORUM_REPLY_ID)  
              FROM   LCMS_FORUM_REPLY LFF  
             WHERE       LFF.ISDELETED = 'N'  
                     AND LFF.USER_ID = LUC.STUD_ID  
                     AND LFF.REMARK LIKE '%distillate%'  
                     AND LFF.FORUM_ID IN  
                              (SELECT   LTA.ACT_ID  
                                 FROM   LCMS_TERMCOURSE_ACT LTA,  
                                        LCMS_TERMCOURSE_TASK LTT  
                                WHERE       LTA.ISDELETED = 'N'  
                                        AND LTT.ISDELETED = 'N'  
                                        AND LTA.TASK_ID = LTT.TASK_ID  
                                        AND LTT.TERMCOURSE_ID =  
                                              '4a592995ac1086a52f3c2f3c964bf835'  
                                        AND LTT.CLASS_ID =  
                                              '6d71f4547f00000122d555b2b063d27c'))  
              ELITESUMFOURM,  
           (SELECT   COUNT (LFF.FORUM_REPLY_ID)  
              FROM   LCMS_FORUM_REPLY LFF  
             WHERE   LFF.ISDELETED = 'N' AND LFF.USER_ID = LUC.STUD_ID  
                     AND LFF.FORUM_ID IN  
                              (SELECT   LTA.ACT_ID  
                                 FROM   LCMS_TERMCOURSE_ACT LTA,  
                                        LCMS_TERMCOURSE_TASK LTT  
                                WHERE       LTA.TASK_ID = LTT.TASK_ID  
                                        AND LTA.ISDELETED = 'N'  
                                        AND LTT.ISDELETED = 'N'  
                                        AND (LTA.CHAPTER_ID <> ''  
                                             OR LTA.CHAPTER_ID IS NOT NULL)  
                                        AND LTT.TERMCOURSE_ID =  
                                              '4a592995ac1086a52f3c2f3c964bf835'  
                                        AND LTT.CLASS_ID =  
                                              '6d71f4547f00000122d555b2b063d27c'))  
              OPTIONALSUMFOURM  
    FROM   LCMS_STUD_RESULTS LSR, LCMS_USER_STUD STU, LCMS_USER_CHOOSE LUC  
   WHERE       STU.ISDELETED = 'N'  
           AND LUC.ISDELETED = 'N'  
           AND STU.STUD_ID = LUC.STUD_ID  
           AND LUC.STUD_ID = LSR.USER_ID  
           AND LUC.TERMCOURSE_ID = LSR.TERMCOURSE_ID  
           AND LUC.CLASS_ID = LSR.CLASS_ID  
           AND LSR.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'  
           AND LSR.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'  
ORDER BY   GROUP_NAME, SUMPOINT;



边幅过大了，2楼继续

[ 本帖最后由 kelvin_lok 于 2012-7-13 23:00 编辑 ]

kelvin_lok

本人修改后SQL语句
SELECT /*+ INDEX_JOIN(LUC) */ NVL(STU.STUD_AREA, ' ') AS STUD_AREA,

       NVL(STU.COMPANY, ' ') AS COMPANY,
       NVL(STU.LMS_NO, ' ') AS LMS_NO,
       NVL(STU.MOBILE_NO, ' ') AS MOBILE_NO,
       NVL(STU.EMAIL, ' ') AS EMAIL,
       LSR.USER_NAME AS FIELD1,
       NVL(LSR.GROUP_NAME, ' ') AS FIELD2,
       NVL(LSR.ACT_POINT01, ' ') AS FIELD3,
       NVL(LSR.ACT_POINT02, ' ') AS FIELD4,
       NVL(LSR.ACT_POINT03, ' ') AS FIELD5,
       NVL(LSR.ACT_POINT04, ' ') AS FIELD6,
       NVL(LSR.ACT_POINT05, ' ') AS FIELD7,
       NVL(LSR.ACT_POINT06, ' ') AS FIELD8,
       NVL(LSR.ACT_SUMPOINT01, ' ') AS FIELD23,
       NVL(LSR.ACT_SUMPOINT02, ' ') AS FIELD24,
       NVL(LSR.ACT_SUMPOINT03, ' ') AS FIELD25,
       NVL(LSR.ACT_SUMPOINT04, ' ') AS FIELD26,
       NVL(LSR.ACT_SUMPOINT05, ' ') AS FIELD27,
       NVL(LSR.ACT_SUMPOINT06, ' ') AS FIELD28,
       LSR.SUMPOINT AS FIELD43,
       NVL((SELECT TO_CHAR(LTU.TEST_POINT) TEST_POINT
               FROM LCMS_TEST_USER LTU,
                    LCMS_TEST_TESTINFO LTI,
                    LCMS_TERMCOURSE_TASK LTT
              WHERE LTU.ISDELETED = 'N'
                AND LTI.ISDELETED = 'N'
                AND LTT.ISDELETED = 'N'
                AND LTI.ENTITY_ID = LTT.TASK_ID
                AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
                AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                AND LTI.TEST_ID = LTU.TEST_ID
                AND LTU.USER_ID = LSR.USER_ID
                AND LTT.ORDER_NO = '1'),
       ' ') TASK1,
       NVL((SELECT TO_CHAR(LTU.TEST_POINT) TEST_POINT
               FROM LCMS_TEST_USER LTU,
                    LCMS_TEST_TESTINFO LTI,
                    LCMS_TERMCOURSE_TASK LTT
              WHERE LTU.ISDELETED = 'N'
                AND LTI.ISDELETED = 'N'
                AND LTT.ISDELETED = 'N'
                AND LTI.ENTITY_ID = LTT.TASK_ID
                AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
                AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                AND LTI.TEST_ID = LTU.TEST_ID
                AND LTU.USER_ID = LSR.USER_ID
                AND LTT.ORDER_NO = '2'),
       ' ') TASK2,
       NVL((SELECT TO_CHAR(LTU.TEST_POINT) TEST_POINT
               FROM LCMS_TEST_USER LTU,
                    LCMS_TEST_TESTINFO LTI,
                    LCMS_TERMCOURSE_TASK LTT
              WHERE LTU.ISDELETED = 'N'
                AND LTI.ISDELETED = 'N'
                AND LTT.ISDELETED = 'N'
                AND LTI.ENTITY_ID = LTT.TASK_ID
                AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
                AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                AND LTI.TEST_ID = LTU.TEST_ID
                AND LTU.USER_ID = LSR.USER_ID
                AND LTT.ORDER_NO = '3'),
       ' ') TASK3,
       NVL((SELECT TO_CHAR(LTU.TEST_POINT) TEST_POINT
               FROM LCMS_TEST_USER LTU,
                    LCMS_TEST_TESTINFO LTI,
                    LCMS_TERMCOURSE_TASK LTT
              WHERE LTU.ISDELETED = 'N'
                AND LTI.ISDELETED = 'N'
                AND LTT.ISDELETED = 'N'
                AND LTI.ENTITY_ID = LTT.TASK_ID
                AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
                AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                AND LTI.TEST_ID = LTU.TEST_ID
                AND LTU.USER_ID = LSR.USER_ID
                AND LTT.ORDER_NO = '4'),
       ' ') TASK4,
       NVL((SELECT TO_CHAR(LTU.TEST_POINT) TEST_POINT
               FROM LCMS_TEST_USER LTU,
                    LCMS_TEST_TESTINFO LTI,
                    LCMS_TERMCOURSE_TASK LTT
              WHERE LTU.ISDELETED = 'N'
                AND LTI.ISDELETED = 'N'
                AND LTT.ISDELETED = 'N'
                AND LTI.ENTITY_ID = LTT.TASK_ID
                AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
                AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                AND LTI.TEST_ID = LTU.TEST_ID
                AND LTU.USER_ID = LSR.USER_ID
                AND LTT.ORDER_NO = '5'),
       ' ') TASK5,
       (SELECT COUNT(LFF.FORUM_REPLY_ID)
          FROM LCMS_FORUM_REPLY LFF
         WHERE LFF.ISDELETED = 'N'
           AND LFF.USER_ID = LUC.STUD_ID
           AND LFF.REMARK LIKE '%distillate%'
           AND LFF.FORUM_ID IN (SELECT LTA.ACT_ID
                                  FROM LCMS_TERMCOURSE_ACT LTA,
                                       LCMS_TERMCOURSE_TASK LTT
                                 WHERE LTA.ISDELETED = 'N'
                                   AND LTT.ISDELETED = 'N'
                                   AND LTA.TASK_ID = LTT.TASK_ID
                                   AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                                   AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c')) ELITESUMFOURM,
       (SELECT COUNT(LFF.FORUM_REPLY_ID)
          FROM LCMS_FORUM_REPLY LFF
         WHERE LFF.ISDELETED = 'N'
           AND LFF.USER_ID = LUC.STUD_ID
           AND LFF.FORUM_ID IN (SELECT LTA.ACT_ID
                                  FROM LCMS_TERMCOURSE_ACT LTA,
                                       LCMS_TERMCOURSE_TASK LTT
                                 WHERE LTA.TASK_ID = LTT.TASK_ID
                                   AND LTA.ISDELETED = 'N'
                                   AND LTT.ISDELETED = 'N'
                                   AND (LTA.CHAPTER_ID <> ''
                                         OR LTA.CHAPTER_ID IS NOT NULL)
                                   AND LTT.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
                                   AND LTT.CLASS_ID = '6d71f4547f00000122d555b2b063d27c')) OPTIONALSUMFOURM
  FROM LCMS_STUD_RESULTS LSR,
       LCMS_USER_STUD STU,
       LCMS_USER_CHOOSE LUC
WHERE STU.ISDELETED = 'N'
--AND LUC.ISDELETED = 'N'
   AND STU.STUD_ID = LUC.STUD_ID
   AND LUC.STUD_ID = LSR.USER_ID
   AND LUC.TERMCOURSE_ID = LSR.TERMCOURSE_ID
   AND LUC.CLASS_ID = LSR.CLASS_ID
   AND LSR.TERMCOURSE_ID = '4a592995ac1086a52f3c2f3c964bf835'
   AND LSR.CLASS_ID = '6d71f4547f00000122d555b2b063d27c'
ORDER BY GROUP_NAME, SUMPOINT;

Maclean Liu(刘相兵

猜测这个SQL是 水晶报表弄出来的吧？

对于这种复杂SQL语句，为了节省时间我一般不会仔细看执行计划

action plan:


1. 找出该SQL的SQL_ID

2.
执行 以下脚本填入上面得到的SQL_ID

set linesize 200 pagesize 1400
@?/rdbms/admin/sqltrpt

贴出所得结果

3.

加入/*+ RULE */ hint 并 计时执行：

set timing on;

select /*+ RULE */   ..................



请给出以上输出

kelvin_lok

首先谢谢老大的支持，谢谢！

第1个就是sqltrpt 结果 ，请看

sqltrpt_结果.txt (34.87 KB, 下载次数: 642)

2012-7-16 15:07 上传

点击文件名下载附件

第2个是set timing on; 输出的时间


115 rows selected.
Elapsed: 00:00:21.50

Maclean Liu(刘相兵

这条SQL没用任何HINT之前单次耗时多少？


考虑accept sql tuning advisor的SQL PROFILE并观察实际运行情况， 这么长SQL的调优实在是很蛋疼。。

kelvin_lok

谢谢老大再次支持，SQL没用任何HINT之前单次耗时是以下。

115 rows selected.

Elapsed: 00:00:20.36