2013-04-16由MERGE JOIN SEMI引发的性能问题

       检查16号上午8:00-12:00的数据库报告，发现一条SQL语句执行时间为1977s，逻辑读为103,491,288。

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
21,756	16,342	11	1977.81	71.70	bw2py1pg93nrd		SELECT * FROM GG_TASK_WORK_S...
337	313	5,306	0.06	1.11	1tgdyrsn703jk		SELECT PO.* FROM OPERATION...

    SQL语句为：

SELECT *
  FROM GG_WORK_SHEET A
 WHERE (A.IS_COMPLETE = 0 OR A.IS_COMPLETE = 1)
   AND (A.WORK_MASTER_ID LIKE '%00000487%' OR
       A.WORK_MEMBER_ID LIKE '%00000487%')
   AND WORK_SHEET_ID IN
       (SELECT OBJECT_ID FROM GG_FORM_RELATIONTEMPLAT)
   and (EXISTS (select null
                  from GG_form_exeGGrecord   ptfe,
                       GG_form_relationtemplat ptfr
                 where ptfe.rel_temp_id = ptfr.rel_temp_id
                   and ptfe.mobile_type != 1
                   and ptfe.state <> 7
                   and ptfr.object_id = A.WORK_SHEET_ID));         

     优化第一步： 先找现场实施拿回执行计划

    select v.HASH_VALUE,v.CHILD_NUMBER,v.SQL_TEXT from v$sql v where v.SQL_ID='bw2py1pg93nrd';   

    select * from table(dbms_xplan.display_cursor(HASH_VALUE,CHILD_NUMBER,'advanced'));

   优化第二步：GG_FORM_RELATIONTEMPLAT有8万的数据量，GG_FORM_RELATIONTEMPLAT和GG_FORM_EXEGGRECORD大概有6万。发现执行计划中MERGE JOIN SEMI，大量数据下是很耗资源的。

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |       |       |  1207 (100)|          |
|   1 |  MERGE JOIN SEMI             |                                |     1 |   282 |   734   (1)| 00:00:09 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| GG_WORK_SHEET           |   115 | 31625 |   453   (1)| 00:00:06 |
|*  3 |    INDEX FULL SCAN           | PK_GG_WORK_SHEET        |  3535 |       |    36   (0)| 00:00:01 |
|*  4 |     HASH JOIN                |                                |     1 |    85 |   473   (1)| 00:00:06 |
|*  5 |      TABLE ACCESS FULL       | GG_FORM_RELATIONTEMPLAT |     1 |    42 |   278   (1)| 00:00:04 |
|*  6 |      TABLE ACCESS FULL       | GG_FORM_EXEGGRECORD   |  7760 |   325K|   194   (1)| 00:00:03 |
|*  7 |   SORT UNIQUE                |                                | 48606 |   332K|   281   (2)| 00:00:04 |
|   8 |    TABLE ACCESS FULL         | GG_FORM_RELATIONTEMPLAT | 48606 |   332K|   278   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------------
   2 - filter((("A"."WORK_MASTER_ID" LIKE '%00000350%' OR "A"."WORK_MEMBER_ID" LIKE '%00000350%') AND
              (TO_NUMBER("A"."IS_COMPLETE")=0 OR TO_NUMBER("A"."IS_COMPLETE")=1)))
   3 - filter( IS NOT NULL)
   4 - access("PTFE"."REL_TEMP_ID"="PTFR"."REL_TEMP_ID")
   5 - filter(TO_NUMBER("PTFR"."OBJECT_ID")=:B1)
   6 - filter(("PTFE"."MOBILE_TYPE"<>1 AND "PTFE"."STATE"<>7))
   7 - access("WORK_SHEET_ID"=TO_NUMBER("OBJECT_ID"))

      优化第三步：找到优化点

       1. WORK_SHEET_ID IN  (SELECT OBJECT_ID FROM GG_FORM_RELATIONTEMPLAT)可以删除，因为这个条件在exists中有，这个是逻辑错误。这个是优化的最重点，去掉这个，可以消除MERGE JOIN SEMI。

       2.  IS_COMPLETE = 0改为IS_COMPLETE = '0' 

       3.  gg_task_form_relationtemplat.object_id = GG_TASK_WORK_SHEET.WORK_SHEET_ID由于两个字段的类型不一致和object_id没有建索引，导致不能走索引。即使优化，只能有一张表走索引。找现场查了一下数据量：

            GG_TASK_WORK_SHEET（80994）,

            gg_task_form_relationtemplat（59053）

            让object_id做一个转换 使得WORK_SHEET_ID 能够走索引，to_number(ptfr.object_id) = A.WORK_SHEET_ID

      最终优化结果：

       SELECT * FROM GG_TASK_WORK_SHEET A
  WHERE (A.IS_COMPLETE = '0' OR A.IS_COMPLETE = '1')
   AND (A.WORK_MASTER_ID LIKE '%00000709%' OR
       A.WORK_MEMBER_ID LIKE '%00000709%')
    and (EXISTS (select 1
                   from GG_task_form_exetaskrecord   ptfe,
                        GG_task_form_relationtemplat ptfr
                  where ptfe.rel_temp_id = ptfr.rel_temp_id
                    and ptfe.mobile_type != 1
                    and ptfe.state <> 7
                    and to_number(ptfr.object_id) = A.WORK_SHEET_ID));

已用时间:  00: 00: 00.28
执行计划
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  |Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |     1 |288 |   475   (2)|
|   1 |  NESTED LOOPS                |                                |     1 |288 |   475   (2)|
|   2 |   VIEW                       | VW_SQ_1                        |  7842 |99K|   473   (1)|
|   3 |    HASH UNIQUE               |                                |  7842 |650K|            |
|   4 |     HASH JOIN                |                                |  7842 |650K|   473   (1)|
|   5 |      TABLE ACCESS FULL       | PROD_TASK_FORM_EXETASKRECORD   |  7760 |325K|   194   (1)|
|   6 |      TABLE ACCESS FULL       | PROD_TASK_FORM_RELATIONTEMPLAT | 48606 |1993K|   278   (1)|
|   7 |   TABLE ACCESS BY INDEX ROWID| PROD_TASK_WORK_SHEET           |     1 |275 |     1   (0)|
|   8 |    INDEX UNIQUE SCAN         | PK_PROD_TASK_WORK_SHEET        |     1 ||     1   (0)|
----------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      13898  consistent gets
          0  physical reads
          0  redo size
       1944  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed