有一条语句,占用很高的cpu请问是什么原因

zjc19870507

数据库是9i的 ,应用做个报表,客户反映很慢
我跟踪了一个sql语句,发现很占cpu资源,


UPDATE tmptotal d
           set d.produceday=(select sum(nvl(b.drQuantity,0))
                             from day_records b,Day_Record a,machine c
                            where a.recordid=b.recordid
                              and a.drdate>=c.begindate
                              and a.drstate = '2'
                              and b.machinecode = c.machinecode
                              and a.depcode = d.itemcode
                              and to_char(a.drDate,'YYYYMMDD') = to_char(:b3,'YYYYMMDD')
                              and c.MachineType<>'小水电机组'
                              and b.iftest='0'),
              d.ratepower=(select nvl(sum(b.ratingpower*decode(a.depcode,'109',0.4,1)/10000),d.ratepower)
                             from day_records b,Day_Record a,machine c
                            where a.recordid=b.recordid
                              and a.drdate>=c.begindate
                              and a.drstate = '2'
                              and b.machinecode = c.machinecode
                              and a.depcode = d.itemcode
                              and to_char(a.drDate,'YYYYMMDD') = to_char(:b3,'YYYYMMDD')
                              and c.MachineType<>'小水电机组'
                              --and b.iftest='0'
                              )               
         where d.itemcode not in(:b2 || '_' || '1',:b2 || '_' || '2',:b2 || '_' || '3',:b2 || '_' || '4',:b2 || '_' || '5')
           and d.iftest=0
           and d.Random = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute    1     25.70      25.73         0     176398    78          38
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     25.71      25.74          0     176398   78          38

Misses in library cache during parse: 1
Optimizer goal: CHOOSE

之后我把优化器改成cbo
后来看了下这个语句的执行计划 也不高 cost在200左右
我看了下event 也都是一些关于control file 并行写之类的

请问这个是什么情况?有什么优化的思路么??

duolanshizhe

如果要做优化的话，请提供该语句中所有涉及到表的结构，比如字段类型、索引等信息

另外最好给出该语句的执行计划，不然很难下手，希望对你有所启示！

zjc19870507

回复 2# duolanshizhe


    Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=7
   1    0   UPDATE OF 'TMPTOTAL'
   2    1     TABLE ACCESS (FULL) OF 'TMPTOTAL' (Cost=11 Card=1 Bytes=7
   3    1     SORT (AGGREGATE)
   4    3       HASH JOIN (Cost=218 Card=1 Bytes=62)
   5    4         HASH JOIN (Cost=215 Card=10 Bytes=380)
   6    5           TABLE ACCESS (FULL) OF 'DAY_RECORD' (Cost=106 Card=5 Bytes=95)
   7    5           TABLE ACCESS (FULL) OF 'DAY_RECORDS' (Cost=108 Card=107142 Bytes=203569
   8    4         TABLE ACCESS (FULL) OF 'MACHINE' (Cost=2 Card=138 Bytes=3312)
   9    1     SORT (AGGREGATE)
  10    9       HASH JOIN (Cost=218 Card=1 Bytes=59)
  11   10         HASH JOIN (Cost=215 Card=19 Bytes=665)
  12   11           TABLE ACCESS (FULL) OF 'DAY_RECORD' (Cost=106 Card=5 Bytes=95)
  13   11           TABLE ACCESS (FULL) OF 'DAY_RECORDS' (Cost=108 Card=214284 Bytes=3428544)
  14   10         TABLE ACCESS (FULL) OF 'MACHINE' (Cost=2 Card=138 Bytes=3312)

这个是执行计划 在select的时候稍微高点,可是在update时又变的很低...



以下是根据上面的表所对应的索引和列
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
IND_DR_DATE                    DAY_RECORD                     SYS_NC00042$
PK_DAYRECORDS                  DAY_RECORDS                    MRECORDID
U_DAYRECORDS                   DAY_RECORDS                    MRECORDID
U_DAYRECORDS                   DAY_RECORDS                    MACHINECODE
PK_MACHINECODE                 MACHINE                        MACHINECODE
UK_MACHINE                     MACHINE                        FACGROUP
UK_MACHINE                     MACHINE                        MACHINESORT


看来要重写sql了吧?

wodi1015

能说说这些表有多少行记录，以及各个表之间的关系，另外从你的sql 的plan看 一个index也没用上，你有建立index的权限么？

doni

ind_dr_date看上去象一个基于函数的索引，所以SQL中大小写是否正确?
另外这个字段不建议这样索引，最好用dr_date=to_date（）

doni

drstate分辨率高吗?
如果高的话，建议建索引

wodi1015

貌似这个哥们问了问题 就再也不来了

renxiao2003

看来这个SQL有狂热的计算啊。

东东堂

都是全表扫描.COST主要都在这几个扫描上了.

www_xylove

sql执行计划有问题，要么表统计信息错误，要么相关的表没有索引。

对于筛选条件的列段需要添加索引，过滤掉大量的数据，将有效的减少扫描数据块的次数。降低对cpu资源的
消耗。