Oracle版本11204，有一个bug 18411339，查询v$archive_gap会显示hang住。（详见Doc ID 18411339.8）

实际不是hang住，而是因为控制文件记录里记的archive log 和 log history 太多了，所以查询返回结果慢。

SQL> select type, records_used, records_total from v$controlfile_record_section where type like '%LOG%';

TYPE RECORDS_USED RECORDS_TOTAL

---------------------------- ------------ -------------

REDO LOG 22 192

LOG HISTORY 24318 24318

ARCHIVED LOG 56000 56000

BACKUP REDOLOG 8312 8312

FLASHBACK LOG 0 2048

FOREIGN ARCHIVED LOG 0 1002

已选择6行。

如果可以打补丁打补丁，不能打补丁，oracle也提供了另外一种等效查询方法：

SQL> set timing on

SQL> select USERENV('Instance'), high.thread#, low.lsq, high.hsq

from

(select a.thread#, rcvsq, min(a.sequence#)-1 hsq

from

v$archived_log a,

(select thread#, resetlogs_change#, max(sequence#) rcvsq

from v$log_history

where (thread#, resetlogs_change#, resetlogs_time) in

(select /*+ no_unnest */

lh.thread#, lh.resetlogs_change#, lh.resetlogs_time

from v$log_history lh, v$database_incarnation di

where lh.resetlogs_time = di.resetlogs_time

and lh.resetlogs_change# = di.resetlogs_change#

and di.status = 'CURRENT'

)

group by thread#, resetlogs_change#

) b

where a.thread# = b.thread#

and a.resetlogs_change# = b.resetlogs_change#

and a.sequence# > rcvsq

group by a.thread#, rcvsq) high,

(select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq

from

(select thread#, min(sequence#)+1 lsq

from

v$log_history lh, x$kccfe fe, v$database_incarnation di

where to_number(fe.fecps) <= lh.next_change#

and to_number(fe.fecps) >= lh.first_change#

and fe.fedup!=0 and bitand(fe.festa, 12) = 12

and di.resetlogs_time = lh.resetlogs_time

and lh.resetlogs_change# = di.resetlogs_change#

and di.status = 'CURRENT'

group by thread#) lh_lsq,

(select thread#, max(sequence#)+1 lsq

from

v$log_history

where (select min( to_number(fe.fecps))

from x$kccfe fe

where fe.fedup!=0 and bitand(fe.festa, 12) = 12)

>= next_change#

group by thread#) srl_lsq

where srl_lsq.thread# = lh_lsq.thread#(+)

) low

where low.thread# = high.thread#

and lsq < = hsq

and hsq > rcvsq;

未选定行

这个语句可以在前面提到的官方文档里能找到。

每天进步一点点。