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

我做了这样一个测试：

SQL> update emp set sal=9000 where empno=7788;

1 row updated.

SQL> update emp set sal=9999 where empno=7900;

1 row updated.

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

SQL> alter system dump datafile 4 block min 25 block max 32;

然后我查看trc文件，发现其中并没有ITL事务信息，我的问题是：既然内存中的数据块已经被修改过，而且已经写回数据文件，为什么看不到ITL信息，但是我提交之后再DUMP一次就可以看到了……？

你的update操作和dump操作是在一个SESSION里吗？

[quote]原帖由 [i]lozity[/i] 于 2008-6-17 16:46 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10695986&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]
...
SQL> alter system set events='immediate trace name flush_cache';
...
[/quote]

Try checkpoint instead.

Yong Huang

[quote]原帖由 [i]sqysl[/i] 于 2008-6-18 07:58 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10696104&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]
你的update操作和dump操作是在一个SESSION里吗？ [/quote]
不在同一个session中啊

[quote]原帖由 [i]Yong Huang[/i] 于 2008-6-18 09:11 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10696898&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]


Try checkpoint instead.

Yong Huang [/quote]

我想问的是：内存中的数据块已经被修改了，通过alter system set events = 'immediate trace name flush_cache'只好再dump出来应该可以看得到分配ITL槽位的信息啊，为什么看不到呢？

至于用检查点，我等下试试……

[quote]原帖由 [i]Yong Huang[/i] 于 2008-6-18 09:11 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10696898&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]


Try checkpoint instead.

Yong Huang [/quote]

我测试了用：
alter system checkpoint;

可以看到ITL信息，我的理解是执行了检查点相当于为那个session做了commit操作，是这样的吗？

快沉了啊……………………

[quote]原帖由 [i]Yong Huang[/i] 于 2008-6-18 09:11 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10696898&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]


Try checkpoint instead.

Yong Huang [/quote]

Could you describe ther difference between checkpoint and flush_cache briefly?

沉啦………………

up

[quote]原帖由 [i]solearn[/i] 于 2008-6-17 21:54 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10700987&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]

Could you describe ther difference between checkpoint and flush_cache briefly? [/quote]

I haven't done any test. But what you need is to write modified or dirty buffers to datafiles, which is exactly what checkpoint does. Flush_cache is not designed to do that. It's meant to restore the buffer cache to what it was like before a data block was read from a datafile into buffer cache.

When you have time, please do a test and watch the change in v$bh or x$bh. Thanks.

Yong Huang

It's meant to restore the buffer cache to what it was like before a data block was read from a datafile into buffer cache.

dx能解释下这句话吗？看得实在不是很懂…………谢谢

[quote]原帖由 [i]Yong Huang[/i] 于 2008-6-19 01:19 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10709933&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]


I haven't done any test. But what you need is to write modified or dirty buffers to datafiles, which is exactly what checkpoint does. Flush_cache is not designed to do that. It's meant to restore the buffer cache to what it was like before a data block was read from a datafile into buffer cache.

When you have time, please do a test and watch the change in v$bh or x$bh. Thanks.

Yong Huang [/quote]

Thanks for showing me the clue.



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

STATU   COUNT(*)
----- ----------
free       11972
xcur          40

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

系统已更改。

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

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

SQL> select count(*) from dengsk.t8;

  COUNT(*)
----------
         9

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

STATU   COUNT(*)
----- ----------
free       12006
xcur           6

SQL> select * from dengsk.t8;

C                ID
-------- ----------
az             1330
by             1331
Candy          1332
Deskbook       1333
EggReady       1334
finefine       1335
aaa              32
aaa              33
mourning       1329

已选择9行。

SQL> delete from dengsk.t8 where id=1329;

已删除 1 行。

SQL> commit;

提交完成。

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

The above is a simple test. I haven't an idea how come it turned out yet. These days I am busy in dealing with an exam of our company at the end of this month. While I am free, I would like to probe it further.

[quote]原帖由 [i]lozity[/i] 于 2008-6-19 05:59 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10710024&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]
It's meant to restore the buffer cache to what it was like before a data block was read from a datafile into buffer cache.

dx能解释下这句话吗？看得实在不是很懂…………谢谢 [/quote]

大概翻译一下：（flush cache）用于把buffer cache恢复到它读入文件上数据块之前的样子。

大概翻译一下：（flush cache）用于把buffer cache恢复到它读入文件上数据块之前的样子。


不是这样的吧？我看eygle的深入浅出中写：强制刷新buffer cache ，将buffer cache中的数据写回到数据文件，如果像楼上那么说，那flush cache 之后岂不是dml会丢失？

flush cache肯定会把脏数据块写入到数据文件中的,就算写到原来数据文件中的是原始的数据块,那么之后变更产生的UNDO块也会写入到UNDO表空间里面的.DML肯定不会丢失.

[quote]原帖由 [i]solearn[/i] 于 2008-6-18 19:09 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10710623&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]


Thanks for showing me the clue.



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

STATU   COUNT(*)
----- ----------
free       11972
xcur          40

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

系统已更改。

...[/quote]

How about checking v$bh.dirty instead of status before and after flush_cache and checkpoint?

Yong Huang

[quote]原帖由 [i]Yong Huang[/i] 于 2008-6-19 06:06 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10718585&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]

How about checking v$bh.dirty instead of status before and after flush_cache and checkpoint?

Yong Huang [/quote]

I did a test in 10.2.0.1 database. I keep checking x$bh.flag:

select flag, count(*) from x$bh group by flag order by 1;

while I do flush buffer_cache and checkpoint. The flag column is documented at
[url]http://www.jlcomp.demon.co.uk/buf_flag.html[/url]

It looks like flush buffer_cache does much more than checkpoint. Flushing cache reduces count of buffers with flags such as 8192 (gotten_in_current_mode), 524288 (only_sequential_access) and 33562625 (dirty and gotten_in_current_mode and redo_since_read). Checkpoint, on the other hand, does much less, but it does reduce dirty buffers.

I don't know much about some of the obscure (uncommon) flags. But it looks like flush buffer_cache does what checkpoint does, plus more. Then the question is How do we explain lozity's test (see his messages at 2008-6-17 16:46 and 2008-6-17 20:23)? Maybe I misunderstood his test.

Even if flush cache writes dirty buffers to datafiles as checkpoint does (as I guessed), Oracle does not consider it as a checkpoint. In my test, v$sysstat does not show increase for
DBWR checkpoints
background checkpoints started
background checkpoints completed
and only increases
physical writes non checkpoint.

If I can check the content of the checkpoint queue, it's possible flush buffer_cache does not change it.

Can jingjingxiaomei or biti_rainy shed some light on this? You have done more research on buffer cache than I.

Yong Huang

多谢版主，我的测试是这样的：

首先对一个表做update，然后用dba用户登录，做flush cache操作，最后dump出表的数据块，发现其中没有ITL信息

我的理解是块在内存中已经被修改，然后做了flush cache以后再dump块，应该是可以看到ITL信息了，为什么看不到？

[quote]原帖由 [i]lozity[/i] 于 2008-6-18 06:46 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10695986&ptid=1007648][img]http://www.itpub.net/images/common/back.gif[/img][/url]
我做了这样一个测试(oracle 9.2.0.1)：

SQL> update emp set sal=9000 where empno=7788;

1 row updated.

SQL> update emp set sal=9999 where empno=7900;

1 row updated.

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

SQL> alter system dump datafile 4 block min 25 block max 32;

然后我查看trc文件，发现其中并没有ITL事务信息，我的问题是：既然内存中的数据块已经被修改过，而且已经写回数据文件，为什么看不到ITL信息，但是我提交之后再DUMP一次就可以看到了……？ [/quote]

我模拟了你的实验，可以看到事物信息。

Session 1：

SQL> update t8 set id=9999 where id=1330;

1 row updated.

SQL>

session 2:

SQL> connect / as sysdba
Connected.
SQL> alter system set events 'immediate trace name flush_cache';

System altered.

SQL> alter system dump datafile 6 block 20;

System altered.

SQL> connect / as sysdba
Connected.
SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

在dump文件中可以看到:
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000e.015.0000000b  0x02000045.0010.33  ----    1  fsc 0x0000.00000000
0x02   0x000c.00f.00000007  0x0200001a.000c.13  C---    0  scn 0x0000.00639d60
0x03   0x000b.002.00000009  0x0200000a.0020.12  C-U-    0  scn 0x0000.0064768f

data_block_dump,data header at 0x5a1107c
===============


session1:
sql>commit;

session2:
SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

[[i] 本帖最后由 solearn 于 2008-6-28 11:29 编辑 [/i]]