对超长的SQL，调优该如何下手

kewin

我摘出部分SQL 做了个查询：
SQL>  SELECT jb.emplid
  2       FROM SYSADM.ps_job jb
    WHERE jb.effdt =
             (SELECT MAX (jb_ed.effdt)
                FROM SYSADM.ps_job jb_ed
               WHERE jb.emplid = jb_ed.emplid
                 AND jb.empl_rcd = jb_ed.empl_rcd
                 AND jb_ed.effdt <= SYSDATE)
      AND jb.effseq =
             (SELECT MAX (a2.effseq)
                FROM sysadm.ps_job a2
               WHERE a2.emplid = jb.emplid
                 AND a2.empl_rcd = jb.empl_rcd
                 AND a2.effdt = jb.effdt)
      AND jb.hr_status = 'A'
      AND jb.company = 'SPT'  3    4    5    6    7    8    9   10   11   12   13   14   15   16  ;

15157 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 512158586

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    27 |  1724K  (1)| 25:44:07 |
|*  1 |  FILTER              |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL  | PS_JOB |   601K|    15M| 14429   (1)| 00:12:56 |
|   3 |   SORT AGGREGATE     |        |     1 |    18 |            |          |
|*  4 |    INDEX RANGE SCAN  | PSAJOB |    21 |   378 |     3   (0)| 00:00:01 |
|   5 |     SORT AGGREGATE   |        |     1 |    21 |            |          |
|*  6 |      INDEX RANGE SCAN| PSAJOB |     1 |    21 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JB"."EFFDT"= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_J
OB"

              "JB_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND
              "JB_ED"."EMPL_RCD"=:B1 AND "JB_ED"."EMPLID"=:B2 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!) AND
              "JB"."EFFSEQ"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ
")))

              FROM "SYSADM"."PS_JOB" "A2" WHERE SYS_OP_DESCEND("EFFDT")=SYS_OP_D
ESCEND

              (:B3) AND "A2"."EMPL_RCD"=:B4 AND "A2"."EMPLID"=:B5 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B6))
   2 - filter("JB"."COMPANY"='SPT' AND "JB"."HR_STATUS"='A')
   4 - access("JB_ED"."EMPLID"=:B1 AND "JB_ED"."EMPL_RCD"=:B2 AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
   6 - access("A2"."EMPLID"=:B1 AND "A2"."EMPL_RCD"=:B2 AND
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B3))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     890077  consistent gets
      95324  physical reads
          0  redo size
     324888  bytes sent via SQL*Net to client
      11597  bytes received via SQL*Net from client
       1012  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      15157  rows processed

physical read /consistent get 都很高。

dingjun123

|*  2 |   TABLE ACCESS FULL  | PS_JOB |   601K|    15M| 14429   (1)| 00:12:56 |
AND jb.hr_status = 'A'
      AND jb.company = 'SPT'  

这两个列选择性如何啊，选择性好是否可以考虑建个索引
你用的是相关子查询，这个如果主表访问效率低，对整个查询影响就大了，计划走filter,类似于nested loop,对每个选择到的行访问都会触发一次子查询。

不能建立索引，考虑从业务上改写下，比如改写成join之类的

比如谓词选择性不好，像
SELECT jb.emplid
       FROM SYSADM.ps_job jb
    WHERE jb.effdt =
             (SELECT MAX (jb_ed.effdt)
                FROM SYSADM.ps_job jb_ed
               WHERE jb.emplid = jb_ed.emplid
                 AND jb.empl_rcd = jb_ed.empl_rcd
                 AND jb_ed.effdt <= SYSDATE)

考虑改写成
SELECT jb.emplid
       FROM SYSADM.ps_job jb,(SELECT jb_ed.emplid,jb_ed.empl_rcd,MAX (jb_ed.effdt) max_effdt
                FROM SYSADM.ps_job jb_ed where jb_ed.effdt <= SYSDATE
                GROUP BY jb_ed.emplid,jb_ed.empl_rcd) jb_ed_t
WHERE jb.effdt=jb_ed_t.max_effdt and jb......

Yong Huang

I haven't looked at your detailed analysis in the later messages. But to answer "对这些超长的SQL ，调优步骤是什么？ 有什么好的建议？", here's my suggestion: focus on the biggest cost jump or jumps. In this case, your execution plan in message #4 has this part:


|  HASH JOIN                                                   |                     |     1 |  199 |    254 |
|   NESTED LOOPS                                               |                     |     1 |  180 |     14 |
...
|   TABLE ACCESS FULL                                          |PS_PER_ORG_ASGN      |    12K|  237K|    239 |


The total cost 283 is mostly the hash join shown above, with a cost of 254. Of this 254, 239 is contributed by the FTS of PS_PER_ORG_ASGN. So check to see if this FTS can be eliminated.

Of course this assumes your actual plan is indeed this one and cost is accurate (in the sense that it reflects the relative buffer gets among all the lines). A more accurate check of the lines in the plan is to find the line specific buffer gets:

alter session set "_rowsource_execution_statistics"=true;
--run the SQL
select * from table(dbms_xplan.display_cursor('', '', 'allstats'));

Yong Huang

[ 本帖最后由 Yong Huang 于 2011-8-19 12:38 编辑 ]

lbs1988

呵呵 超大的SQL一样搞，没啥难度

javaxf

建立几个临时表，然后用临时表再去关联，效果会好些！

Coast_lichao

初看你第一个执行计划，统计信息有问题，收集下，另外你贴出来的那小部分的sql可以考虑改写：SELECT jb.emplid
        FROM SYSADM.ps_job jb
    WHERE jb.effdt =
             (SELECT MAX (jb_ed.effdt)
                FROM SYSADM.ps_job jb_ed,SYSADM.ps_job sh
               WHERE sh.emplid = jb_ed.emplid
                 AND sh.empl_rcd = jb_ed.empl_rcd
                 AND jb_ed.effdt <= SYSDATE)
      AND jb.effseq =
             (SELECT MAX (a2.effseq)
                FROM sysadm.ps_job a2,SYSADM.ps_job sh
               WHERE a2.emplid = sh.emplid
                 AND a2.empl_rcd = sh.empl_rcd
                 AND a2.effdt = sh.effdt)
      AND jb.hr_status = 'A'
      AND jb.company = 'SPT' 
你如果查看一下执行统计，前面执行计划中的nested loop肯定有些执行了成千上万次，不慢才怪。就着一小部分，where的视图部分，内部应用的表都要到外部去join，你把这些外部的表写到视图里面，效果会好很多的。试一试啊。

bitizerg

你搜索一下MERGE JOIN CARTESIAN，把它disable后再跑