000条记录都未处理，总 共有7万多条。
declare
cursor c1 is
select caller
from zxx_sms_step where chargemonth=200504 and fee is null;
icnt number;
begin
icnt:=0;
for m_c1 in c1 loop
update zxx_sms_step a set fee=
(select nvl(sum(pascharge),0) from ipasimport_200504 where caller=m_c1.caller and pastag in (1243,1251))
where caller=m_c1.caller and chargemonth=200504;
icnt:=icnt+1;
if icnt=500 then
exit;
end if;
end loop;
end;
这样的SQL语句，建议先将update中的子查询生成一张中间表，然后再update。
alter session set hash_area_size=400000000 ;
select /*+use_hash(a,b)*/ b.caller,nvl(sum(a.pascharge),0) from ipasimport_200504 a,zxx_sms_step b
where b.chargemonth=200504 and b.fee is null
and a.caller=b.caller and a.pastag in (1243,1251)
group by b.caller;
这样10分钟不到就可产生中间表，然后再update只需几分钟即可。
2． 部分表格未做统计信息分析
网通OA系统自从oracle服务器从pc服务器上迁到小型机上后，其CPU利用率经常冲到很高。而其中每一个进程在某个瞬间将占用40%左右的CPU。这些进程都是通过jdbc thin client 连过来的。
通过抓取其sql_text，发现以下两条SQL语句不正常。
1.
SQL> select D.flow_inid,D.step_inco,D.deal_man,D.agen_men,D.time_set,D.peri_man,
2 S2.fsub_set,S2.fsub_id,F.mtbl_stru,F.doc_name,F.svr_name
3 from deal_info D,step_inst S1,step_def S2,flow_inst F
4 where D.step_inco=S1.step_inco and S1.flow_id=S2.flow_id
5 and S1.step_code=S2.step_code and S1.flow_inid=F.flow_inid and D.step_type=5
6 and D.fsub_flag is not null and D.fsub_flag=1 and rownum<=1;
其执行计划和统计信息如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=1077)
1 0 COUNT (STOPKEY)
2 1 NESTED LOOPS (Cost=22 Card=1 Bytes=1077)
3 2 NESTED LOOPS (Cost=21 Card=1 Bytes=360)
4 3 NESTED LOOPS (Cost=20 Card=1 Bytes=150)
5 4 TABLE ACCESS (FULL) OF 'STEP_INST' (Cost=2 Card=9 Bytes=153)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'DEAL_INFO' (Cost=2 Card=1 Bytes=133)
7 6 INDEX (RANGE SCAN) OF 'DEAL_INFO_STEP_INCO' (NON-UNIQUE) (Cost=2
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'FLOW_INST' (Cost=1 Card=1 Bytes=210)
9 8 INDEX (UNIQUE SCAN) OF 'PK_FLOW_INST' (UNIQUE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'STEP_DEF' (Cost=1 Card=1 Bytes=717)
11 10 INDEX (UNIQUE SCAN) OF 'STEP_DEF_PK11119358638593' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
270626 consistent gets
273 physical reads
0 redo size
1079 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
这条SQL语句执行的时间也不长，就几秒钟，但是我们看到consistent gets很高有27万多，这个操作就是消耗CPU的祸首。从执行计划来看，其执行计划显然不可理，问题出在表格的连接顺序上面，应该是deal_info表格做为驱动表先访问。
检查这些表格的统计分析，发现step_def表格未做分析，对该表格做统计信息分析，并对deal_info表做柱状图分析后：
analyze table deal_info compute statistics for all indexed columns;
其执行计划正是我们所想要的，同时consistent gets也只有200左右，该操作所消耗的CPU也下降到了1%。
2.表格的柱状图信息没有分析：
SELECT SO.SO_NBR, so_type.name,STATUS.STS_WORDS, SO.REMARKS, SO.CHECK_TYPE,CTRL_ASGN.DISPATCHED_DATE,
CTRL_ASGN.PRE_ALARM_DATE, CTRL_ASGN.ALARM_DATE
from SO,SO_HANDLE, CTRL_ASGN,so_type,status
WHERE SO_HANDLE.SO_NBR=SO.SO_NBR AND SO.SO_NBR=CTRL_ASGN.SO_NBR
AND SO_HANDLE.HANDLE_TYPE_ID=1017
and so