数据库Io的优化问题,请教,急

rollingpig

Physical 内存多少？

1。可能的话增加db_buffer_blocks
你的buffer_hit很低。

你的应用好像频繁修改数据哦

2。你的CPU有几个，可能的话提高db_writer_processes=CPU数。
或者dbwriter_io_slaves (单CPU)

ccipt_cloud

我的CPU是6个,参数也是6
buffer hits 这个是高峰时期的的,我可以再尝试调大点

Io的影响大不大?

rollingpig

提高buffer_hit应该会显著减少db_file_read的wait

另外，看了一下你topsql的physical read部分
好几个SQL错误的在比较的列中使用了函数，这会导致sql无法使用index
如：
select nvl(sum(total),0) from tl_pp where to_char(log_time, 'yyy
yMMddhh24miss')<=:p1 and to_char(log_time, 'yyyyMMddhh24miss')>;=
:p2 and to_char(mo_id)=:moid

应当改为
select nvl(sum(total),0) from tl_pp where log_time <=to_date(:p1,'yyy
yMMddhh24miss') and log_time>;= to_date(
:p2,'yyy
yMMddhh24miss') and to_char(mo_id)=:moid

其它几个SQL也类似。

还有
V_GSM_WHCDR02 似乎没在start_date columns 上建index
可以根据你的应用考虑加上index,应该对降低如下SQL的physical read.

SELECT "START_DATE","START_TIME","MSISDN","OTHER_PARTY","TRUNK_G
ROUPOUT","TRUNK_GROUPIN","VISIT_AREA_CODE","ROAM_TYPE","CALL_TYP
E","OFFICE_CODE","CALL_DURATION","TIMES1","TIMES2","CFEE","LFEE"
,"OTHERFEE","DISCOUNT_CFEE","DISCOUNT_LFEE","DISCOUNT_OTHERFEE"
FROM "V_GSM_XMCDR02" "V_GSM_XMCDR02" WHERE "START_DATE"='2005022
0' AND ("ROAM_TYPE"='3' OR "ROAM_TYPE"='4' OR "ROAM_TYPE"='5' OR
"ROAM_TYPE"='6')

ccipt_cloud

谢谢!!

feijin

看的我晕......