关于块清除的一个问题，谢谢

solearn

flush行为也许就像它的字面意思----如同一个冲马桶的动作：所有赃物冲出去，再将清水引进来。

但是仍然不明白下面的结果，为什么仅仅alter system checkpoint之后，xcur的buffer会增加？

SQL> select status,count(*) from v$bh group by status;

STATU   COUNT(*)
----- ----------
free       12001
xcur          11

SQL> alter system checkpoint;

系统已更改。

SQL> select status,count(*) from v$bh group by status;

STATU   COUNT(*)
----- ----------
free       11970
xcur          42

Yong Huang

原帖由 solearn 于 2008-6-27 01:18 发表
但是仍然不明白下面的结果，为什么仅仅alter system checkpoint之后，xcur的buffer会增加？

How reproducible is it? Always state you Oracle version.

Yong Huang

solearn

原帖由 Yong Huang 于 2008-6-28 01:15 发表


How reproducible is it? Always state you Oracle version.

Yong Huang

It was on Oracle 9.2.0.1 on winxp.

solearn

I tried on a 10g box. It is a bit different. Maybe "alter system checkpoint" itself causes xcur increase. But immediately after flush, there is still '1' xcur while in my 9i test there was no xcur.

SQL> startup
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219304 bytes
Variable Size             213910808 bytes
Database Buffers          117440512 bytes
Redo Buffers                2973696 bytes
Database mounted.

Database opened.
SQL> SQL> select status,count(*) from v$bh group by status;

STATUS    COUNT(*)
------- ----------
xcur          3891
free            63
read             1
cr             140

SQL> alter system checkpoint;

System altered.

SQL> select status,count(*) from v$bh group by status;

STATUS    COUNT(*)
------- ----------
xcur          9705
free            63
cr             638

SQL> alter system flush cache;
alter system flush cache
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword


SQL> alter system set events 'immediate trace name flush_cache';

System altered.

SQL> select status,count(*) from v$bh group by status;

STATUS    COUNT(*)
------- ----------
xcur             1
free         10405

SQL> /

STATUS    COUNT(*)
------- ----------
xcur             1
free         10405

SQL> alter system checkpoint;

System altered.

SQL> select status,count(*) from v$bh group by status;

STATUS    COUNT(*)
------- ----------
xcur            67
free         10339

SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1583

SQL> select status,count(*) from v$bh group by status;

STATUS    COUNT(*)
------- ----------
xcur          1739
free          8667

SQL>

Yong Huang

solearn,

Sorry. Neither I nor eagle_fan can reproduce your test, in 10g and 11g. Before and after our checkpoint, "select status,count(*) from v$bh group by status" does not show any difference. There must be something special about your database.

Yong Huang

solearn

I did the test on my 9i, this time I got a different result:


SQL> alter system checkpoint;

System altered.

SQL> select status,count(*) from v$bh group by status;

STATU   COUNT(*)
----- ----------
cr             2
free       10312
xcur        1698

SQL> /

STATU   COUNT(*)
----- ----------
cr             2
free       10312
xcur        1698

SQL> alter system set events 'immediate trace name flush_cache';

System altered.

SQL> select status,count(*) from v$bh group by status;

STATU   COUNT(*)
----- ----------
free       12012

SQL> alter system checkpoint;

System altered.

SQL> select status,count(*) from v$bh group by status;

STATU   COUNT(*)
----- ----------
free       12012

SQL> /

STATU   COUNT(*)
----- ----------
free       12012

SQL>

It seems that 'flush_cache' eliminate all cr and xcur buffers, there are only free buffer left. And 'alter system checkpoint' leads to no new xcur buffers. The only difference between those two experiments is the latter one was done just after a OS and db restart. But I cannot believe this is the reason.