oracle查询V$ARCHIVE_GAP慢的问题解决--转瞻Derek
select * from gv$archive_gap;<p>INST_ID THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#<br>---------- ---------- ------------- --------------<br> 1 1 580 581<br> <br> <br>select USERENV('Instance'), high.thread#, low.lsq, high.hsq<br> from<br> (select a.thread#, rcvsq, min(a.sequence#)-1 hsq<br> from v$archived_log a,<br> (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq<br> from v$log_history lh, v$database_incarnation di<br> where lh.resetlogs_time = di.resetlogs_time<br> and lh.resetlogs_change# = di.resetlogs_change#<br> and di.status = 'CURRENT'<br> and lh.thread# is not null<br> and lh.resetlogs_change# is not null<br> and lh.resetlogs_time is not null<br> group by lh.thread#, lh.resetlogs_change#<br> ) b<br> where a.thread# = b.thread#<br> and a.resetlogs_change# = b.resetlogs_change#<br> and a.sequence# > rcvsq<br> group by a.thread#, rcvsq) high,<br> (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq<br> from<br> (select thread#, min(sequence#)+1 lsq<br> from<br> v$log_history lh, x$kccfe fe, v$database_incarnation di<br> where to_number(fe.fecps) = lh.first_change#<br> and fe.fedup!=0 and bitand(fe.festa, 12) = 12<br> and di.resetlogs_time = lh.resetlogs_time<br> and lh.resetlogs_change# = di.resetlogs_change#<br> and di.status = 'CURRENT'<br> group by thread#) lh_lsq,<br> (select thread#, max(sequence#)+1 lsq<br> from<br> v$log_history<br> where (select min( to_number(fe.fecps))<br> from x$kccfe fe<br> where fe.fedup!=0 and bitand(fe.festa, 12) = 12)<br> >= next_change#<br> group by thread#) srl_lsq<br> where srl_lsq.thread# = lh_lsq.thread#(+)<br> ) low<br> where low.thread# = high.thread#<br> and lsq rcvsq;<br> <br>USERENV('INSTANCE') THREAD# LSQ HSQ<br>------------------- ---------- ---------- ----------<br>1 1 580 581<br></p>
