[20130318]v$rman_backup_job_details访问缓慢的问题.txt
今天我想检查备份,执行如下:
SELECT start_time, end_time, elapsed_seconds,TRIM (time_taken_display) TIME, status, input_type,
compression_ratio, TRIM (input_bytes_display) inputb,
TRIM (output_bytes_display) outputb,
TRIM (input_bytes_per_sec_display) input,
TRIM (output_bytes_per_sec_display) output,
status
FROM v$rman_backup_job_details
WHERE start_time > TRUNC (SYSDATE) - 22
order by start_time desc ;
--我生产系统的版本信息如下:
select * from v$version where rownum
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
1 row selected.
--系统如死机一般,根本不动.
--仔细看执行计划,发现有好几个地方使用笛卡尔集MERGE JOIN CARTESIAN.
--我马上想起来,上个星期5我执行分析收集fixed table信息。
exec dbms_stats.gather_fixed_objects_stats;
--马上修改sql语句,加入提示rule 看看.
SELECT /*+ rule */
start_time, end_time, elapsed_seconds, TRIM (time_taken_display) TIME, status, input_type, compression_ratio,
TRIM (input_bytes_display) inputb, TRIM (output_bytes_display) outputb, TRIM (input_bytes_per_sec_display) input,
TRIM (output_bytes_per_sec_display) output, status
FROM v$rman_backup_job_details
WHERE start_time > TRUNC (SYSDATE) - 32
ORDER BY start_time DESC;
--结果马上返回.google发现如下链接:
http://rupamverma.blogspot.com/2010/11/oracle-database-backup-report.html
Oracle Database Backup Report - v$rman_backup_job_details
/*
-- rman - Query using V$RMAN_BACKUP_JOB_DETAILS is taking too long to execute both in SQL*Plus and also in DATABASE CONSOLE.
-- doc : 420200.1
*/
Steps :-
1. sqlplus as sysdba
2. execute following procedure
Sql> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); # deletes the statistics on the fixed object.
Sql> exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR'); # lock that object so that statistics will not be collected in future.
Hope this helps. Regards Rupam
--按照建议就是删除SYS.X$KCCRSR上的统计,lock统计.
SELECT obj#, savtime, flags, rowcnt, blkcnt, avgrln, samplesize, analyzetime
FROM SYS.wri$_optstat_tab_history
WHERE obj# IN (SELECT object_id FROM v$fixed_table WHERE NAME = 'X$KCCRSR');
OBJ# SAVTIME FLAGS ROWCNT BLKCNT AVGRLN SAMPLESIZE ANALYZETIME
---------- --------------------------- ---------- ---------- ---------- ---------- ---------- -------------------
4294952064 2013-03-15 15:21:48.724980 8 5456 0 144 5456 2012-03-07 08:12:52
--不知道以前分析,问题为什么不出现.
SQL> select * from DBA_TAB_STATS_HISTORY where table_name='X$KCCRSR';
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------ ----------- ------------------------------ ------------------------------ ----------------------------------------
SYS X$KCCRSR 2013-03-15 15:21:48.724980
--这种fixed表不保留原来的统计信息.
解决方法两个:
1.使用提示rule.
2.删除统计信息,在lock统计.
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');