今天软开部的CTE约我帮他看看,上次那系统快挂的问题,叫我调优,其实我心里也很没底,不过想想这两年多的学习,也应该可以解决这问题。(之前一直指出上次两个ORA-00600和ORA-07445错误,在系统中连续出现这种错误,并从alert_XXX.log中统计下ORA-00600错误个数达2807次,并伴随ORA-07445的个数为1238次,因此系统无人维护造成。两个错误的间隔时间很短,而且都是由SQL语句所引起。由于一直出现有这个错误,基本上可以认为问题就是SQL语句。)
查出消耗资源最多的 SQL:
select sql_text,
hash_value,
executions,
buffer_gets,
disk_reads,
parse_calls
from v$sqlarea
where buffer_gets > 10000000
or disk_reads > 1000000
order by buffer_gets + 100 * disk_reads desc;
取出的SQL一个一个解决,基本是让走规则方法,强制使用/*+RULE*/ /*+HASH()*/和使用INDEX来。
发现意思的是若用RULE,会出来是索引挣资源的情况,一个表多个索引同时在用,会变慢很多。
select /*+rule */ 1,
a.*,
b.vessel_name_cn,
b.voyages,
to_number(fuc_work_cntr_outnum(A.cntr_no, 2) kem,
fuc_work_cntr_outnum(A.cntr_no, 3) zmd,
round(sysdate - DYN_TIME) dcts,
C.Work_Status,
D.CLASS_VALUES_NAME,
(SELECT remark
FROM T_OPE_VSCH_CNTR c
WHERE c.VSCHEDULE_ID = a.VSCHEDULE_ID
AND c.CNTR_NO = a.CNTR_NO
AND ROWNUM <= 1) AS BZ
from t_basic_field a,
t_ope_fore_VSCH b,
(select Cntr_No, work_status
from t_ope_strobe_record
where work_status = '0') C,
(select CLASS_VALUES_ID, CLASS_VALUES_NAME
from t_pub_class_values
where class_id = 15) D
where a.vschedule_id = b.vschedule_id(+)
and A.cntr_no is not null
and a.LOCATION_STATUS = '1'
and A.Cntr_No = C.Cntr_No(+)
and A.CON_STATUS = D.CLASS_VALUES_ID(+)
order by dcts desc, kem desc
本身语句也是多方嵌套造成。
和截取时间问题上,如
select /*+ RULE */ count(*) A
from T_OPE_STROBE_RECORD
where IMP_EXP_TYPE = '3'
and trunc(IMP_EXP_TIME) = trunc(sysdate)
因要走RULE要进行表分析。
ORACLE9I没有自动分析功能,编写一个SQL,定期执行下。
set pages 999999
set linesize 120;
set heading off
set echo off
set feedback off
spool /oracle/alog/hotbak/bin/sqltemp.sql;
select 'analyze table '||owner||'.'||table_name||' estimate statistics sample 5000 rows;'from dba_tables where owner in('XXXX');
select 'analyze index '||owner||'.'||index_name||' compute statistics;' from dba_indexes where owner in('XXXX');
spool off;
set echo on
set feedback on
spool /oracle/alog/hotbak/log/analyze.log;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') fromtime from dual;
@/oracle/alog/hotbak/bin/sqltemp.sql;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') totime from dual;
spool off
exit;
经过一天修改,客户反应速度大大的加快,并无在报ORA-0600和ORA-07445错误,定期再跟踪。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15242702/viewspace-666769/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15242702/viewspace-666769/