当前位置: 代码迷 >> SQL >> Oracle的SQL优化1
  详细解决方案

Oracle的SQL优化1

热度:98   发布时间:2016-05-05 10:17:24.0
Oracle的SQL优化一
两个SCHEMA,开发反馈数据量相差无几,但一模一样的SQL,A Schema要跑6分钟,B Schema只5秒就出来了。SQL语句如下:
SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select t3.check_show,
                       t1.*,
                  t2.storesid,
                           to_char(rdate, 'yyyy-mm-dd') as to_rdate,
                       to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
                       to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
                       to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
                       to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
                       to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
                       (nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
                       to_char((nvl(debtsum, 0) - nvl(received, 0)),
                               'fm9999999990.00') to_debtsum,
                       nvl(amounttotal, 0) as to_amounttotal,
                       (select a.reduce_points
                          from SchemaB.m_mempoint_logs a
                         where a.billno = t1.billno
                           and a.billtype = 4) as reduce_points
                  from SchemaB.v0bill                   t1,
                       SchemaB.tbl_stores               t2,
                       SchemaB.TBL_BILL_CHECKSTATE_SHOW t3
                 where ((posbillno is not null and BCOMPLETE = 1) or
                       posbillno is null)
                   and t1.StoreRoomID = t2.storesid
                   and t1.billsubcase = t3.billsubcase
                   and t1.check_status = t3.check_status
                   and (instoreroomid in
                       (select storesid
                           from SchemaB.tbl_user_stores
                          where employeeid = 4352) or
                       outstoreroomid in
                       (select storesid
                           from SchemaB.tbl_user_stores
                          where employeeid = 4352))
                   and servicStatus = 1
                   and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
                   and rdate <=
                       to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and (t1.billsubcase = '75' or t1.billsubcase = '711' or
                       (instoreroomid in
                       (select storesid
                            from SchemaB.tbl_stores
                           where areacode like '001%') or
                       outstoreroomid in
                       (select storesid
                            from SchemaB.tbl_stores
                           where areacode like '001%')))
                 ORDER by rdate desc, billingdate desc, billno desc) A
         WHERE ROWNUM <= (1 * 20))
 WHERE RN > ((1 - 1) * 20);
B Schema的执行计划:
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |    20 |   152K|       | 16718   (1)| 00:03:21 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID    | TBL_BILL                 |     1 |    25 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                    | XPK_BILL_P               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|*  3 |  TABLE ACCESS BY INDEX ROWID           | M_MEMPOINT_LOGS          |     1 |    24 |       |     4   (0)| 00:00:01 |       |       |
|*  4 |   INDEX RANGE SCAN                     | IDX_MEMPOINT_LOGS_BILLNO |     1 |       |       |     3   (0)| 00:00:01 |       |       |
|*  5 |  VIEW                                  |                          |    20 |   152K|       | 16718   (1)| 00:03:21 |       |       |
|*  6 |   COUNT STOPKEY                        |                          |       |       |       |            |          |       |       |
|   7 |    VIEW                                |                          |   917 |  6989K|       | 16718   (1)| 00:03:21 |       |       |
|*  8 |     SORT ORDER BY STOPKEY              |                          |   917 |   522K|    57M| 16718   (1)| 00:03:21 |       |       |
|*  9 |      FILTER                            |                          |       |       |       |            |          |       |       |
|* 10 |       HASH JOIN                        |                          | 96250 |    53M|       |  4844   (1)| 00:00:59 |       |       |
|  11 |        TABLE ACCESS FULL               | TBL_BILL_CHECKSTATE_SHOW |   200 |  9600 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |        HASH JOIN                       |                          | 57750 |    29M|       |  4840   (1)| 00:00:59 |       |       |
|  13 |         INDEX FULL SCAN                | XPK_TBL_STORES           |    85 |   340 |       |     1   (0)| 00:00:01 |       |       |
|* 14 |         HASH JOIN RIGHT OUTER          |                          | 57750 |    29M|       |  4838   (1)| 00:00:59 |       |       |
|  15 |          TABLE ACCESS FULL             | TBL_STORES               |    85 |  2550 |       |     4   (0)| 00:00:01 |       |       |
|* 16 |          HASH JOIN RIGHT OUTER         |                          | 57750 |    27M|       |  4834   (1)| 00:00:59 |       |       |
|  17 |           TABLE ACCESS FULL            | TBL_STORES               |    85 |  2550 |       |     4   (0)| 00:00:01 |       |       |
|* 18 |           HASH JOIN RIGHT OUTER        |                          | 57750 |    25M|  4200K|  4829   (1)| 00:00:58 |       |       |
|  19 |            TABLE ACCESS FULL           | M_MEMDETAIL              | 89469 |  3145K|       |   380   (1)| 00:00:05 |       |       |
|* 20 |            HASH JOIN RIGHT OUTER       |                          | 57750 |    24M|       |  3020   (1)| 00:00:37 |       |       |
|  21 |             TABLE ACCESS FULL          | TBL_BILLCASE             |    40 |   840 |       |     4   (0)| 00:00:01 |       |       |
|* 22 |             HASH JOIN RIGHT OUTER      |                          | 57750 |    22M|  3176K|  3015   (1)| 00:00:37 |       |       |
|  23 |              TABLE ACCESS FULL         | M_MEMDETAILCARD          | 90213 |  2114K|       |   310   (1)| 00:00:04 |       |       |
|* 24 |              HASH JOIN RIGHT OUTER     |                          | 57750 |    21M|       |  1450   (1)| 00:00:18 |       |       |
|  25 |               TABLE ACCESS FULL        | TBL_CUSTOM               |   537 | 17184 |       |     6   (0)| 00:00:01 |       |       |
|* 26 |               HASH JOIN RIGHT OUTER    |                          | 57750 |    19M|       |  1443   (1)| 00:00:18 |       |       |
|  27 |                TABLE ACCESS FULL       | TBL_BUSINESS_TYPE        |    38 |   760 |       |     3   (0)| 00:00:01 |       |       |
|  28 |                PARTITION RANGE ITERATOR|                          | 57750 |    18M|       |  1439   (1)| 00:00:18 |   181 |   180 |
|* 29 |                 TABLE ACCESS FULL      | TBL_BILL                 | 57750 |    18M|       |  1439   (1)| 00:00:18 |   181 |   180 |
|* 30 |       TABLE ACCESS FULL                | TBL_USER_STORES          |     1 |     8 |       |     7   (0)| 00:00:01 |       |       |
|* 31 |       TABLE ACCESS FULL                | TBL_USER_STORES          |     1 |     8 |       |     7   (0)| 00:00:01 |       |       |
|* 32 |       TABLE ACCESS BY INDEX ROWID      | TBL_STORES               |     1 |    12 |       |     1   (0)| 00:00:01 |       |       |
|* 33 |        INDEX UNIQUE SCAN               | XPK_TBL_STORES           |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|* 34 |       TABLE ACCESS BY INDEX ROWID      | TBL_STORES               |     1 |    12 |       |     1   (0)| 00:00:01 |       |       |
|* 35 |        INDEX UNIQUE SCAN               | XPK_TBL_STORES           |     1 |       |       |     0   (0)| 00:00:01 |       |       |

-------------------------------------------------------------------------------------------------------------------------------------------

在B schema执行该SQL耗费1万多次逻辑读。

A Schema的执行计划:
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                          |    20 |   152K|       | 50624   (1)| 00:10:08 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID       | TBL_BILL                 |     1 |    31 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                       | XPK_BILL_P               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|*  3 |  TABLE ACCESS BY INDEX ROWID              | M_MEMPOINT_LOGS          |     1 |    47 |       |     1   (0)| 00:00:01 |       |       |
|*  4 |   INDEX RANGE SCAN                        | IDX_MEMPOINT_LOGS_BILLNO |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|*  5 |  VIEW                                     |                          |    20 |   152K|       | 50624   (1)| 00:10:08 |       |       |
|*  6 |   COUNT STOPKEY                           |                          |       |       |       |            |          |       |       |
|   7 |    VIEW                                   |                          |  3600 |    26M|       | 50624   (1)| 00:10:08 |       |       |
|*  8 |     SORT ORDER BY STOPKEY                 |                          |  3600 |  2000K|   210M| 50624   (1)| 00:10:08 |       |       |
|*  9 |      FILTER                               |                          |       |       |       |            |          |       |       |
|* 10 |       HASH JOIN                           |                          |   378K|   205M|       |  5203   (1)| 00:01:03 |       |       |
|  11 |        TABLE ACCESS FULL                  | TBL_BILL_CHECKSTATE_SHOW |   200 |  9600 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |        HASH JOIN                          |                          |   151K|    75M|       |  5199   (1)| 00:01:03 |       |       |
|  13 |         INDEX FAST FULL SCAN              | XPK_TBL_STORES           |   616 |  3080 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |         HASH JOIN RIGHT OUTER             |                          |   151K|    74M|       |  5196   (1)| 00:01:03 |       |       |
|  15 |          TABLE ACCESS FULL                | TBL_STORES               |   616 | 20944 |       |     8   (0)| 00:00:01 |       |       |
|* 16 |          HASH JOIN RIGHT OUTER            |                          |   151K|    69M|       |  5187   (1)| 00:01:03 |       |       |
|  17 |           TABLE ACCESS FULL               | TBL_STORES               |   616 | 20944 |       |     8   (0)| 00:00:01 |       |       |
|* 18 |           HASH JOIN RIGHT OUTER           |                          |   151K|    64M|       |  5178   (1)| 00:01:03 |       |       |
|  19 |            TABLE ACCESS FULL              | TBL_CUSTOM               |    93 |  2139 |       |     4   (0)| 00:00:01 |       |       |
|* 20 |            HASH JOIN RIGHT OUTER          |                          |   151K|    61M|       |  5173   (1)| 00:01:03 |       |       |
|  21 |             TABLE ACCESS FULL             | TBL_BILLCASE             |    40 |   800 |       |     4   (0)| 00:00:01 |       |       |
|* 22 |             HASH JOIN RIGHT OUTER         |                          |   151K|    58M|       |  5168   (1)| 00:01:03 |       |       |
|  23 |              TABLE ACCESS FULL            | TBL_BUSINESS_TYPE        |    33 |   660 |       |     3   (0)| 00:00:01 |       |       |
|* 24 |              HASH JOIN RIGHT OUTER        |                          |   151K|    55M|       |  5164   (1)| 00:01:02 |       |       |
|  25 |               TABLE ACCESS FULL           | M_MEMDETAIL              |     5 |   120 |       |     4   (0)| 00:00:01 |       |       |
|  26 |               NESTED LOOPS OUTER          |                          |   151K|    52M|       |  5159   (1)| 00:01:02 |       |       |
|  27 |                PARTITION RANGE ITERATOR   |                          |   151K|    50M|       |  5158   (1)| 00:01:02 |   181 |   180 |
|* 28 |                 TABLE ACCESS FULL         | TBL_BILL                 |   151K|    50M|       |  5158   (1)| 00:01:02 |   181 |   180 |
|  29 |                TABLE ACCESS BY INDEX ROWID| M_MEMDETAILCARD          |     1 |    14 |       |     1   (0)| 00:00:01 |       |       |
|* 30 |                 INDEX UNIQUE SCAN         | XPK_TBL_MEMDETAIL        |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|* 31 |       TABLE ACCESS FULL                   | TBL_USER_STORES          |     1 |    10 |       |   175   (1)| 00:00:03 |       |       |
|* 32 |       TABLE ACCESS FULL                   | TBL_USER_STORES          |     1 |    10 |       |   175   (1)| 00:00:03 |       |       |
|* 33 |       TABLE ACCESS BY INDEX ROWID         | TBL_STORES               |     1 |    18 |       |     2   (0)| 00:00:01 |       |       |
|* 34 |        INDEX UNIQUE SCAN                  | XPK_TBL_STORES           |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 35 |       TABLE ACCESS BY INDEX ROWID         | TBL_STORES               |     1 |    18 |       |     2   (0)| 00:00:01 |       |       |
|* 36 |        INDEX UNIQUE SCAN                  | XPK_TBL_STORES           |     1 |       |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
   27716859  consistent gets
          1  physical reads
          0  redo size
      16407  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         20  rows processed
2771万次逻辑读。
在该SQL涉及到的所有表中,TBL_BILL是主要的一张大表,此处对TBL_BILL的访问都做了分区裁剪,两个执行计划的主要差异是A SCHEMA以TBL_BILL为驱动表进行NESTED LOOPS OUTER,
而B Schema则以TBL_BUSINESS_TYPE表作为驱动表对TBL_BILL进行HASH JOIN,怀疑是因为A走了错误的执行计划导致,于是使用hint使A对TBL_BILL走HASH JOIN,发现没有任何改善,
于是看看时间具体消耗在哪:
SELECT /*+gather_plan_statistics */ *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select 
          t3.check_show,
                       t1.*,
                       t2.storesid,
                       to_char(rdate, 'yyyy-mm-dd') as to_rdate,
                       to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
                       to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
                       to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
                       to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
                       to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
                       (nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
                       to_char((nvl(debtsum, 0) - nvl(received, 0)),
                               'fm9999999990.00') to_debtsum,
                       nvl(amounttotal, 0) as to_amounttotal,
                       t4.reduce_points
                  from SchemaA.v0bill                          t1,
                       SchemaA.tbl_stores               t2
                       ,SchemaA.TBL_BILL_CHECKSTATE_SHOW t3,
                        SchemaA.m_mempoint_logs t4 
                 where ((posbillno is not null and BCOMPLETE = 1) or
                       posbillno is null)
                   and t1.StoreRoomID = t2.storesid
                   and t1.billsubcase = t3.billsubcase
                   and t1.check_status = t3.check_status
                   and (instoreroomid in
                       (select  storesid
                           from SchemaA.tbl_user_stores
                          where employeeid = 3945) or
                       outstoreroomid in
                       (select  storesid
                           from SchemaA.tbl_user_stores
                          where employeeid = 3945))
                   and servicStatus = 1
                   and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
                   and rdate <=
                       to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and (t1.billsubcase in ('75' ,'711') or
                       (instoreroomid in
                       (select storesid
                            from SchemaA.tbl_stores
                           where areacode like '001022%') or
                       outstoreroomid in
                       (select storesid
                            from SchemaA.tbl_stores
                           where areacode like '001022%')))
                    and  t1.billno=t4.billno(+)
                           and T4.billtype (+)= 4
                 ORDER by rdate desc, billingdate desc, t1.billno desc) A
         WHERE ROWNUM <= (1 * 20))

 WHERE RN > ((1 - 1)* 20);

发现主要时间都消耗在对TBL_USER_STORES上(对该表会有2次filter操作(全表扫描),占总耗时的99%),如下图:


再看看2家企业该表(TBL_USER_STORES)的数据量不是一个量级,如下:

A Schema记录数:136057

B Schema记录数:7439

原来这就是原因所在了,于是优化表TBL_USER_STORES的访问路径,创建一个组合索引:

 create index SchemaA.i_TBL_USER_STORESon SchemaA.TBL_USER_STORES(employeeid,storesid);

创建索引后,在SchemaA上执行该SQL,执行时长在3秒以内:


 


  相关解决方案