sql优化小例子

来源：互联网发布：js加密后怎么调用编辑：程序博客网时间：2024/09/21 06:34

客户业务系统随着数据量的增大出现运行速度降低问题，通过AWR报告分析发现运行效率低的SQL，如下：

select * from scott.计划产品出生证表 where 序号=(select min(序号) from scott.计划产品出生证表 where 任务单号= ‘xxxx-xxx-xxx’ and 产品状态=’正在执行’)

其执行计划

-----------------------------------------------------------------------------------------------Plan hash value: 2054637612------------------------------------------------------------------------------------------------| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                |     1 |   131 |   851   (2)| 00:00:11 ||*  1 |  TABLE ACCESS FULL            | 计划_产品出生证   |    1 |   131 |   838   (2)| 00:00:11 ||   2 |   SORT AGGREGATE              |                |     1 |    29 |            |          ||*  3 |    TABLE ACCESS BY INDEX ROWID| 计划_产品出生证  |     1 |    29 |    13   (0)| 00:00:01 ||*  4 |     INDEX RANGE SCAN          | 计划_产品出生证  |   366 |       |     4   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):----------------------------------------------------------------------------------------------   1 - filter("序号"= (SELECT MIN("序号") FROM "SCOTT"."计划_产品出生证表" "计划_产品出生证表" WHERE              "产品状态"='正在执行' AND "任务单号"='170511-P22-001'))   3 - filter("任务单号"='170511-P22-001')   4 - access("产品状态"='正在执行')统计信息----------------------------------------------------------          1  recursive calls          0  db block gets          3802  consistent gets          0  physical reads          0  redo size       1636  bytes sent via SQL*Net to client        374  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed

分析：
执行计划中id=1的SQL通过全表扫描方式获取数据，整体的cost值为851，consistent gets 3802，SQL效率低下。

解决方法：
create index scott.计划产品出生证表_indx_zxq on scott.计划产品出生证表(序号);

效果：
优化后的SQL执行计划执行计划

Plan hash value: 1523355933-------------------------------------------------------------------------------------------------------- Id  | Operation                       | Name             | Rows |  Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |                  |   1  |   131 |  15   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID   | 计划_产品出生证表   |   1  |  131  |   2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN             | 计划_产品出生证表_I |   1  |       |   1   (0)| 00:00:01 ||   3 |    SORT AGGREGATE              |                  |    1  |   29 |           |          ||*  4 |     TABLE ACCESS BY INDEX ROWID| 计划_产品出生证表   |     1 |  29  | 13 (0)   | 00:00:01 ||*  5 |      INDEX RANGE SCAN          | 计划_产品出生证表_I |   366 |      |   4   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("序号"= (SELECT MIN("序号") FROM "SCOTT"."计划_产品出生证表" "计划_产品出生证表" WHERE              "产品状态"='正在执行' AND "任务单号"='xxxx-xxx-xxx'))   4 - filter("任务单号"='xxxx-xxx-xxx')   5 - access("产品状态"='正在执行')统计信息----------------------------------------------------------          1  recursive calls          0  db block gets          23  consistent gets          0  physical reads          0  redo size       1636  bytes sent via SQL*Net to client        374  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)0   rows processed

执行计划中SQL由全表扫描方式改为索引读取方式获取数据，cost值由851降低至15，consistent gets由 3802降低至23， SQL效率明显提高。

阅读全文

1 0