[20151111]rman执行list backupset很慢的问题.txt
--我的测试环境在rman下执行list backupset很慢,这个问题由来已久,一直没解决,以前我通过重新建立控制文件来解决,不过没多久有出
--现.今天做了1个10046跟踪发现:
--以前遇到设置优化模式rule,或者执行exec dbms_stats.GATHER_FIXED_OBJECTS_STATS 都会好起来,但是我遇到的这些都不行.
1.问题提出:
SYS@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
/* Formatted on 2015/11/11 11:44:58 (QP5 v5.252.13127.32867) */
DECLARE
thread NUMBER;
sequence NUMBER;
recid NUMBER;
alRec dbms_rcvman.alRec_t;
BEGIN
dbms_rcvman.getArchivedLog (alRec => alRec);
IF ( :rlscn = alRec.rlgSCN
AND :stopthr = alRec.thread
AND ( (alRec.sequence >= :stopseq AND :toclause = 0)
OR (alRec.sequence > :stopseq AND :toclause = 1)))
THEN
:flag := 1;
ELSE
:flag := 0;
:al_key:al_key_i := alRec.key;
:recid:recid_i := alRec.recid;
:stamp:stamp_i := alRec.stamp;
:thread := alRec.thread;
:sequence := alRec.sequence;
:fileName:fileName_i := alRec.fileName;
:lowSCN := alRec.lowSCN;
:lowTime := alRec.lowTime;
:nextSCN := alRec.nextSCN;
:nextTime := NVL (alRec.nextTime, TO_DATE ('12/31/9999', 'MM/DD/YYYY'));
:rlgSCN := alRec.rlgSCN;
:rlgTime := alRec.rlgTime;
:blocks := alRec.blocks;
:blockSize := alRec.blockSize;
:status := alRec.status;
:compTime:compTime_i := alRec.compTime;
:duplicate := alRec.duplicate;
:compressed:compressed_i := alRec.compressed;
:isrdf:isrdf_i := alRec.isrdf;
:stby := alRec.stby;
:terminal := alRec.terminal;
:site_key:site_key_i := alRec.site_key;
:source_dbid := alRec.source_dbid;
END IF;
END;
--问题主要集中这条语句,不断的循环执行.但是我观察跟踪文件时不断输出如下内容,rman并没有输出.
Bind#27
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=2600
kxsbbbfp=2a97650d38 bln=22 avl=00 flg=01
FETCH #182927268104:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=1395075536,tim=1447212896313250
$ grep -A5 "Bind#27" test_ora_20151_127_0_0_1.trc | grep FETCH | wc
616 1232 61630
--也就是执行了616次.
SYS@test> select * from V$CONTROLFILE_RECORD_SECTION where records_used>=610;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 616 616 308 307 6159
DELETED OBJECT 20 818 818 223 222 4312
SYS@test> select count(*) FROM V$ARCHIVED_LOG;
COUNT(*)
----------
616
--很明显问题是type='ARCHIVED LOG'的数量正好对上.
RMAN> list archivelog all ;
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
6159 1 4012 A 2015-11-11 08:25:24
Name: /u01/app/oracle11g/archivelog/1_4012_798551880.dbf
--仅仅看到1个,并且我这样执行很快.通过tkprof观察:
SQL ID: 7qd215rsryu1u Plan Hash: 0
declare thread number; sequence number; recid number; alRec
dbms_rcvman.alRec_t; begin dbms_rcvman.getArchivedLog(alRec => alRec); if
(:rlscn = alRec.rlgSCN and :stopthr = alRec.thread and ((alRec.sequence >=
:stopseq and :toclause = 0) or (alRec.sequence > :stopseq and :toclause = 1)
)) then :flag := 1; else :flag := 0; :al_key:al_key_i := alRec.key;
:recid:recid_i := alRec.recid; :stamp:stamp_i :=
alRec.stamp; :thread := alRec.thread; :sequence
:= alRec.sequence; :fileName:fileName_i := alRec.fileName; :lowSCN
:= alRec.lowSCN; :lowTime := alRec.lowTime;
:nextSCN := alRec.nextSCN; :nextTime :=
nvl(alRec.nextTime, to_date('12/31/9999', 'MM/DD/YYYY')); :rlgSCN
:= alRec.rlgSCN; :rlgTime := alRec.rlgTime; :blocks
:= alRec.blocks; :blockSize :=
alRec.blockSize; :status := alRec.status;
:compTime:compTime_i := alRec.compTime; :duplicate :=
alRec.duplicate; :compressed:compressed_i := alRec.compressed;
:isrdf:isrdf_i := alRec.isrdf; :stby :=
alRec.stby; :terminal := alRec.terminal;
:site_key:site_key_i := alRec.site_key; :source_dbid :=
alRec.source_dbid; end if; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 618 0.02 0.02 0 0 0 0
Execute 618 0.86 0.91 0 0 0 616
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1236 0.88 0.94 0 0 0 616
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 618 0.00 0.00
SQL*Net message from client 618 0.00 0.20
SQL*Net break/reset to client 4 0.00 0.00
********************************************************************************
--时间并不是很多.但是主要的等待就是在这里.在这里要循环616次才有输出.
2.我这套系统为了学习dgmgrl,还建立dg,我在dg下测试rman的list backupset,显示也很快出来:
SYS@testdg> select count(*) FROM V$ARCHIVED_LOG;
COUNT(*)
----------
308
--在主机上执行如下:
SYS@test> select * FROM V$ARCHIVED_LOG where name is null;
--303行.先不管问题在那里,清除控制文件的记录信息看看是否好转.
3.清除控制文件的记录信息:
--方法可以通过重建控制文件的方法.还可以通过使用dbms_backup_restore包resetcfilesection可以清除对应的信息.
--参考:http://blog.itpub.net/267265/viewspace-748366/
SYS@test> execute dbms_backup_restore.resetcfilesection(11);
PL/SQL procedure successfully completed.
SYS@test> select * from (select rownum -1 a, crs.* from v$controlfile_record_section crs) where a=11;
A TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ---------------------------------------- ----------- ------------- ------------ ----------- ---------- ----------
11 ARCHIVED LOG 584 616 0 0 0 0
--感觉是快了不少对比原来的情况,但是依旧感觉有点慢!大约3,5秒开始显示.这样估计问题还会再现,先这样吧.
--注册archive log文件,再测试:
RMAN> catalog start with '/u01/app/oracle11g/archivelog/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle11g/archivelog/
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle11g/archivelog/1_4012_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4015_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4013_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4014_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4016_798551880.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle11g/archivelog/1_4012_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4015_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4013_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4014_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/1_4016_798551880.dbf
SYS@test> select * from (select rownum -1 a, crs.* from v$controlfile_record_section crs) where a=11;
A TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ---------------------------------------- ----------- ------------- ------------ ----------- ---------- ----------
11 ARCHIVED LOG 584 616 5 1 5 5
--继续测试,依旧3-5秒再显示,先暂时这样不管它.总之比原来的好许多......而且这样临时解决比较简单.
