[20130318]v$rman_backup_job_details訪問緩慢的問題.txt

lfree發表於2013-03-18
[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<=1;

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');
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-756410/,如需轉載,請註明出處,否則將追究法律責任。

相關文章