sql优化，生产真实案例3

NOTE：
查询某个子cursor的执行计划：

select * from table(dbms_xplan.display_awr(‘sql_id’,‘plan_hash_value’)

待优化SQL_ID : 7frm2t109ra7a
sql语句：

SELECT  mc.*,
        mh.send_date
FROM    MP_CHAT_READED_COUNT mc,
        mp_chat_his mh
WHERE   mc.chatid         = mh.chat_id
        AND mc.fromuserid = :1
        AND mh.chat_id    > :2
        AND mh.chat_id    < :3
UNION
SELECT  mc.*,
        mh.send_date
FROM    MP_CHAT_READED_COUNT mc,
        mp_chat_his_bk mh
WHERE   mc.chatid         = mh.chat_id
        AND mc.fromuserid = :4
        AND mh.chat_id    > :5
        AND mh.chat_id    < :6

1、SQL> @sql_child_cursor_diff.sql

对比libary cache中该sql对应的child cursor的平均执行时间和逻辑读

Enter value for sql_id: 7frm2t109ra7a

PLAN_HASH_VALUE EXECUTIONS AVG_ET_SECS AVG_BUFFER_GETS
--------------- ---------- ----------- ---------------
      860709141          1        .004               8
      799516050          1        .008              12
     3980778508        215        .054             220
     2897804194        149         .25           69787
     2127726783       1120         .31          278209
     3899215044        400        .708          121118

6 rows selected.

对比该sql截止目前的所有child_cursor的平均执行时间

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      860709141        .004
      799516050        .008
     3980778508        .054
     1302333295        .074
     2979373934        .196
     2897804194         .25
     2127726783         .31
     3899215044        .708

8 rows selected.

2、查看执行效率最差的子cursor的child_number

SQL> select child_number from v$sql where sql_id ='7frm2t109ra7a' and plan_hash_value='3899215044';

CHILD_NUMBER
------------
         141

3、查看该child_number的绑定变量值

SQL> select position, datatype_string,last_captured,value_string 
     FROM TABLE (SELECT DBMS_SQLTUNE.extract_binds (bind_data)
     FROM v$sql
     WHERE sql_id = '7frm2t109ra7a' and child_number = 141);    
  
  POSITION DATATYPE_STRING LAST_CAPTURED                            VALUE_STRING
---------- --------------- ---------------------------------------- --------------------
         1 NUMBER          2018-03-08 11:20:33                      1193972
         2 NUMBER          2018-03-08 11:20:33                      24727432
         3 NUMBER          2018-03-08 11:20:33                      24814127
         4 NUMBER          2018-03-08 11:20:33                      1193972
         5 NUMBER          2018-03-08 11:20:33                      24727432
         6 NUMBER          2018-03-08 11:20:33                      24814127

4、代入sql中，

SELECT  /*+ gather_plan_statistics */ mc.*,
        mh.send_date
FROM    pro_db.MP_CHAT_READED_COUNT mc,
        pro_db.mp_chat_his mh
WHERE   mc.chatid         = mh.chat_id
        AND mc.fromuserid = 1193972
        AND mh.chat_id    > 24727432
        AND mh.chat_id    < 24814127
UNION
SELECT  mc.*,
        mh.send_date
FROM    pro_db.MP_CHAT_READED_COUNT mc,
        pro_db.mp_chat_his_bk mh
WHERE   mc.chatid         = mh.chat_id
        AND mc.fromuserid = 1193972
        AND mh.chat_id    > 24727432
        AND mh.chat_id    < 24814127

5、查看效率最差的子cursor的执行计划

SQL> select * from table(dbms_xplan.display_awr('7frm2t109ra7a','3899215044'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |       |       |   187 (100)|          |
|   1 |  SORT UNIQUE                          |                            |     3 |   240 |   187   (7)| 00:00:03 |
|   2 |   UNION-ALL                           |                            |       |       |            |          |
|   3 |    FILTER                             |                            |       |       |            |          |
|   4 |     NESTED LOOPS                      |                            |       |       |            |          |
|   5 |      NESTED LOOPS                     |                            |     2 |   160 |   178   (2)| 00:00:03 |
|   6 |       TABLE ACCESS BY INDEX ROWID     | MP_CHAT_READED_COUNT       |     2 |   132 |   175   (2)| 00:00:03 |
|   7 |        BITMAP CONVERSION TO ROWIDS    |                            |       |       |            |          |
|   8 |         BITMAP AND                    |                            |       |       |            |          |
|   9 |          BITMAP CONVERSION FROM ROWIDS|                            |       |       |            |          |
|  10 |           SORT ORDER BY               |                            |       |       |            |          |
|  11 |            INDEX RANGE SCAN           | IND_MP_CHAT_READED_COUNT_1 |  1216 |       |    10   (0)| 00:00:01 |
|  12 |          BITMAP CONVERSION FROM ROWIDS|                            |       |       |            |          |
|  13 |           SORT ORDER BY               |                            |       |       |            |          |
|  14 |            INDEX RANGE SCAN           | INDEX_CHATID               |  1216 |       |   129   (0)| 00:00:02 |
|  15 |       INDEX UNIQUE SCAN               | PK_MP_CHAT_HIS             |     1 |       |     1   (0)| 00:00:01 |
|  16 |      TABLE ACCESS BY INDEX ROWID      | MP_CHAT_HIS                |     1 |    14 |     2   (0)| 00:00:01 |
|  17 |    FILTER                             |                            |       |       |            |          |
|  18 |     NESTED LOOPS                      |                            |       |       |            |          |
|  19 |      NESTED LOOPS                     |                            |     1 |    80 |     7   (0)| 00:00:01 |
|  20 |       TABLE ACCESS BY INDEX ROWID     | MP_CHAT_HIS_BK             |     1 |    14 |     4   (0)| 00:00:01 |
|  21 |        INDEX RANGE SCAN               | PK_MP_CHAT_HIS_BK          |     1 |       |     3   (0)| 00:00:01 |
|  22 |       INDEX RANGE SCAN                | INDEX_CHATID               |     1 |       |     2   (0)| 00:00:01 |
|  23 |      TABLE ACCESS BY INDEX ROWID      | MP_CHAT_READED_COUNT       |     1 |    66 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
        	

解决：
优化表MP_CHAT_READED_COUNT的索引，建立（chatid,fromuserid）列的组合索引。

create index pro_db.index_comp1 on pro_db.mp_chat_readed_count(chatid,fromuserid) nologging parallel 4;      	
alter index pro_db.index_comp1 logging noparallel;

drop index pro_db.index_chatid;