记一次Oracle Sql优化经历--消耗过多CPU(原创)
发现并未在sfssq_qsrq和sfssq_zzrq这两列上创建索引.
解决方案，在sfssq_qsrq和sfssq_zzrq这两列上创建普通索引或者函数索引，如果网报系统中to_char(a.sfssq_qsrq,'yyyy-mm-dd') = :2的用法较多，那么则应创建函数索引，反之创建普通索引即可
创建普通索引：
SQL> set timing on
SQL> create index ETAX.fbi_2 on ETAX.T_WB_SHBXMXSBB(sfssq_zzrq);
Index created.
Elapsed: 00:00:31.54
SQL> explain plan for select a.shbxhm, nvl(sum(a.ylbx_jfjs), 0) from etax.t_wb_shbxmxsbb a, etax.t_wb_sbbqk b where a.pz_xh = b.pz_xh and a.swglm = :1 and a.sfssq_qsrq >= to_date('2','yyyy-mm-dd') and a.sfssq_qsrq < to_date('3','yyyy-mm-dd') and
? 2? a.sfssq_zzrq >= to_date('4','yyyy-mm-dd') and a.sfssq_zzrq < to_date('5','yyyy-mm-dd')? and b.zt in ('2', '6', '7', '8') group by a.shbxhm
? 3? ;
Explained.
Elapsed: 00:00:00.03
SQL> set linesize 180
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 3466029591
------------------------------------------------------------------
| Id? | Operation?????????????????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????????????? |??????????????? |???? 1 |??? 86 |?? 308?? (2)| 00:00:04 |
|?? 1 |? HASH GROUP BY????????????????????? |??????????????? |???? 1 |??? 86 |?? 308?? (2)| 00:00:04 |
|*? 2 |?? FILTER??????????????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 3 |??? NESTED LOOPS???????????????????? |??????????????? |???? 1 |??? 86 |?? 307?? (2)| 00:00:04 |
|*? 4 |???? TABLE ACCESS BY INDEX ROWID???? | T_WB_SHBXMXSBB |???? 1 |??? 67 |?? 305?? (2)| 00:00:04 |
|?? 5 |????? BITMAP CONVERSION TO ROWIDS??? |??????????????? |?????? |?????? |??????????? |????????? |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
|?? 6 |?????? BITMAP AND??????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 7 |??????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|?? 8 |???????? SORT ORDER BY?????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|*? 9 |????????? INDEX RANGE SCAN?????????? | FBI??????????? | 43201 |?????? |?? 117?? (0)| 00:00:02 |
|? 10 |??????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|? 11 |???????? SORT ORDER BY?????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|* 12 |????????? INDEX RANGE SCAN?????????? | FBI_2????????? | 43201 |?????? |?? 117?? (0)| 00:00:02 |
|* 13 |???? TABLE ACCESS BY INDEX ROWID???? | T_WB_SBBQK???? |???? 1 |??? 19 |???? 2?? (0)| 00:00:01 |
|* 14 |????? INDEX UNIQUE SCAN????????????? | PK_T_WB_SBBQK? |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter(TO_DATE('4','yyyy-mm-dd')<TO_DATE('5','yyyy-mm-dd') AND
????????????? TO_DATE('2','yyyy-mm-dd')<TO_DATE('3','yyyy-mm-dd'))
?? 4 - filter("A"."SWGLM"=TO_NUMBER(:1))
?? 9 - access("A"."SFSSQ_QSRQ">=TO_DATE('2','yyyy-mm-dd') AND
????????????? "A"."SFSSQ_QSRQ"<TO_DATE('3','yyyy-mm-dd'))
? 12 - access("A"."SFSSQ_ZZRQ">=TO_DATE('4','yyyy-mm-dd') AND
????????????? "A"."SFSSQ_ZZRQ"<TO_DATE('5','yyyy-mm-dd'))
? 13 - filter("B"."ZT"='2' OR "B"."ZT"='6' OR "B"."ZT"='7' OR "B"."ZT"='8')
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
? 14 - access("A"."PZ_XH"="B"."PZ_XH")
34 rows selected.
Elapsed: 00:00:00.01
走索引了，时间有所缩短
SQL> drop index etax.fbi ;urge;
Index dropped.
Elapsed: 00:00:00.11
SQL> drop index ETAX.fbi_2;
Index dropped.
Elapsed: 00:00:00.04
并更改该sql写法如下
a.sfssq_qsrq>= to_date('2012-01-01','yyyy-mm-dd') and a.sfssq_qsrq < to_date('2012-01-02','yyyy-mm-dd')
创建函数索引：
SQL> create index etax.fbi on ETAX.T_WB_SHBXMXSBB ( to_char(sfssq_qsrq, 'yyyy-mm-dd'));
Index created.
Elapsed: 00:00:34.65
SQL> create index ETAX.fbi_2 on ETAX.T_WB_SHBXMXSBB ( to_char(sfssq_zzrq, 'yyyy-mm-dd'));
Index created.
Elapsed: 00:00:36.66
SQL> explain plan for select a.shbxhm, nvl(sum(a.ylbx_jfjs), 0) from ETAX.t_wb_shbxmxsbb a, ETAX.t_wb_sbbqk b where a.pz_xh = b.pz_xh and a.swglm = :1 and to_char(a.sfssq_qsrq, 'yyyy-mm-dd') = :2 and to_char(a.sfssq_zzrq, 'yyyy-mm-dd') = :3 and b.zt in ('2', '4', '5', '6') group by a.shbxhm;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 4204756945
-----------------------------------------------------------------
| Id? | Operation????????????????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-----------------------------------------------------------------
|?? 0 | SELECT STATEMENT?????????????????? |??????????????? |???? 1 |??? 86 |? 1134?? (1)| 00:00:14 |
|?? 1 |? HASH GROUP BY???????????????????? |??????????????? |???? 1 |??? 86 |? 1134?? (1)| 00:00:14 |
|?? 2 |?? NESTED LOOPS???????????????????? |??????????????? |???? 1 |??? 86 |? 1133?? (1)| 00:00:14 |
|*? 3 |??? TABLE ACCESS BY INDEX ROWID???? | T_WB_SHBXMXSBB |???? 1 |??? 67 |? 1131?? (1)| 00:00:14 |
|?? 4 |???? BITMAP CONVERSION TO ROWIDS??? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 5 |????? BITMAP AND??????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
|?? 6 |?????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|*? 7 |??????? INDEX RANGE SCAN??????????? | FBI_2????????? | 38405 |?????? |?? 456?? (1)| 00:00:06 |
|?? 8 |?????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|*? 9 |??????? INDEX RANGE SCAN??????????? | FBI??????????? | 38405 |?????? |?? 463?? (1)| 00:00:06 |
|* 10 |??? TABLE ACCESS BY INDEX ROWID???? | T_WB_SBBQK???? |???? 1 |??? 19 |???? 2?? (0)| 00:00:01 |
|* 11 |???? INDEX UNIQUE SCAN????????????? | PK_T_WB_SBBQK? |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
?? 3 - filter("A"."SWGLM"=TO_NUMBER(:1))
?? 7 - access(TO_CHAR(INTERNAL_FUNCTION("SFSSQ_ZZRQ"),'yyyy-mm-dd')=:3)
?? 9 - access(TO_CHAR(INTERNAL_FUNCTION("SFSSQ_QSRQ"),'yyyy-mm-dd')=:2)
? 10 - filter("B"."ZT"='2' OR "B"."ZT"='4' OR "B"."ZT"='5' OR "B"."ZT"='6')
? 11 - access("A"."PZ_XH"="B"."PZ_XH")
27 rows selected.
Elapsed: 00:00:00.01
走索引了，时间有所缩短
SQL> drop index etax.fbi ;urge;
Index dropped.
Elapsed: 00:00:00.11
SQL> drop index ETAX.fbi_2;
Index dropped.
Elapsed: 00:00:00.04
可以看到，创建函数索引和创建普通索引在本质操作本质上对系统并没有太大影响。创建什么样的索引，关键取决于应用系统。
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:czmcj@163.com