2013-04-22不适当的索引导致的性能问题--影响CBO的判断

最新推荐文章于 2019-04-21 21:53:32 发布

深圳gg

最新推荐文章于 2019-04-21 21:53:32 发布

阅读量1k

点赞数

分类专栏： Oracle SQL优化

本文链接：https://blog.csdn.net/stevendbaguo/article/details/8849887

版权

Oracle SQL优化专栏收录该内容

130 篇文章 7 订阅

订阅专栏

现场反馈系统慢，做了一个AWR报告，平时上班时间一个小时正常DB time为100min，现在已经远远超过这个数。数据库服务器CPU有4个。

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	31355	22-Apr-13 11:00:48	91	21.4
End Snap:	31356	22-Apr-13 12:01:01	105	17.5
Elapsed:		60.21 (mins)
DB Time:		1,777.30 (mins)

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
latch: cache buffers chains	34,983	11,720	335	11.0	Concurrency
CPU time		11,527		10.8
latch free	442	88	200	.1	Other
db file sequential read	61,933	71	1	.1	User I/O
db file scattered read	13,744	15	1	.0	User I/O

可以看到SQL的执行时间远远大于SQL消耗CPU的时间。老问题，单条SQL逻辑读过高，在并发的情况下，产生大量资源争用，平均每次逻辑读1,109,335。

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
44,559	4,145	113	394.32	41.79	435xza3c04ac9		SELECT GG_JOB_ITEM.*, PROD_...
44,551	4,392	113	394.25	41.78	8jjqdn6bw4f2p		SELECT COUNT(1) C FROM GG_JOB...

问题SQL语句：

SELECT COUNT(1) C
  FROM GG_JOB_ITEM, GG_JOB_PATROL_DETAIL
 WHERE GG_JOB_ITEM.TASK_ID = GG_JOB_PATROL_DETAIL.TASK_ID(+)
   AND GG_JOB_ITEM.TASK_KIND = :1
   AND GG_JOB_ITEM.TASK_SOURCE = :2
   AND GG_JOB_ITEM.IS_COMPLETE = 5
   AND NOT EXISTS (SELECT 1
          FROM GG_JOB_ITEM A
         WHERE A.TASK_KIND = :3
           AND A.TASK_SOURCE != :4
           AND A.TASK_SOURCE_ID = GG_JOB_ITEM.TASK_ID)
   AND EXISTS
 (SELECT 1
          FROM GG_JOB_ITEM B
         WHERE B.TASK_KIND = :5
           AND B.IS_COMPLETE != 5
           AND B.TASK_ID = GG_JOB_ITEM.TASK_SOURCE_ID)
   AND ((GG_JOB_ITEM.TASK_BEGIN_DATE >= :6 AND
       GG_JOB_ITEM.TASK_BEGIN_DATE <= :7) OR
       (GG_JOB_ITEM.TASK_END_DATE >= :8 AND
       GG_JOB_ITEM.TASK_END_DATE <= :9) OR
       (GG_JOB_ITEM.TASK_BEGIN_DATE <= :10 AND
       GG_JOB_ITEM.TASK_END_DATE >= :11))
   AND GG_JOB_ITEM.TASK_KIND = :12
   AND GG_JOB_ITEM.IS_COMPLETE = :13
   AND GG_JOB_ITEM.TASK_TYPE IN
       (1, 2, 6, 4, 21, 3, 7, 8, 9, 22, 23, 26, 29, 11, 12, 14, 19, 24);

----------------------------------------------------------------------------------------------------------------
| Id | Operation                        | Name                        | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |       |       | 1353 (100)|          |
|   1 | SORT AGGREGATE                  |                             |     1 |    63 |            |          |
|* 2 |   FILTER                         |                             |       |       |            |          |
|   3 |    NESTED LOOPS ANTI             |                             |     1 |    63 | 1353   (1)| 00:00:17 |
|   4 |     NESTED LOOPS SEMI            |                             |     1 |    52 | 1352   (1)| 00:00:17 |
|   5 |      NESTED LOOPS OUTER          |                             |     1 |    42 | 1351   (1)| 00:00:17 |
|* 6 |       TABLE ACCESS BY INDEX ROWID| GG_JOB_ITEM              |     1 |    37 | 1350   (1)| 00:00:17 |
|* 7 |        INDEX RANGE SCAN          | INDEX_TASK_KIND             |   107K|       |    63   (0)| 00:00:01 |
|* 8 |       INDEX UNIQUE SCAN          | PK_GG_JOB_PATROL_DETAILA |     1 |     5 |     1   (0)| 00:00:01 |
|* 9 |      TABLE ACCESS BY INDEX ROWID | GG_JOB_ITEM              | 86225 |   842K|     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | PK_GG_JOB_ITEM           |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID | GG_JOB_ITEM              |   107K| 1151K|     1   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN            | INDEX_TASK_KIND             |     3 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

对表GG_JOB_ITEM进行分析，记录有三十多万，有8个索引，其中在TASK_KIND和TASK_TYPE上建了索引，TASK_KIND只有3个值，TASK_TYPE只有20个值，选择性如此差的字段不能建索引，处理方法是全部drop。
只是删除了两个索引，更新表的统计信息后再次执行SQL，执行计划为：

--------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                        | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     3 |   966 | 4980   (2)|
|   1 | NESTED LOOPS OUTER           |                             |     3 |   966 | 4980   (2)|
|   2 |   NESTED LOOPS SEMI           |                             |     3 |   891 | 4979   (2)|
|   3 |    HASH JOIN ANTI             |                             |   233 | 66871 | 4932   (2)|
|   4 |     TABLE ACCESS FULL         | PROD_TASK_ITEM              |   234 | 64584 | 2466   (2)|
|   5 |     TABLE ACCESS FULL         | PROD_TASK_ITEM              |   105K| 1129K| 2465   (2)|
|   6 |    TABLE ACCESS BY INDEX ROWID| PROD_TASK_ITEM              | 1347 | 13470 |     1   (0)|
|   7 |     INDEX UNIQUE SCAN         | PK_PROD_TASK_ITEM           |     1 |       |     1   (0)|
|   8 |   TABLE ACCESS BY INDEX ROWID | PROD_TASK_PATROL_DETAIL     |     1 |    25 |     1   (0)|
|   9 |    INDEX UNIQUE SCAN          | PK_PROD_TASK_PATROL_DETAILA |     1 |       |     1   (0)|
--------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      22722 consistent gets
         27 physical reads
          0 redo size
      17193 bytes sent via SQL*Net to client
        281 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         93 rows processed