关于日志切换和检查点的疑惑！！！！！

Yong Huang

原帖由 flying_warrior 于 2009-6-25 23:16 发表
I Saw that from x$kvit

and what the means of "write batch size limit"?

I don't know it's still relevant in newer versions. Literally "write batch size limit" simply means the upper bound of the size for one write.

I was a little surprised to see you said "25%" (note the *percent*) while the description for the undocumented parameter _db_large_dirty_queue says "*Number* of buffers..."

Yong Huang

flying_warrior

原帖由 Yong Huang 于 2009-6-27 00:01 发表


I don't know it's still relevant in newer versions. Literally "write batch size limit" simply means the upper bound of the size for one write.

I was a little surprised to see you said "25%" (note the *percent*) while the description for the undocumented parameter _db_large_dirty_queue says "*Number* of buffers..."

Yong Huang

hah,I made a experiment for your "surprised "......
my buffer_pool is 160 M

and there is almost 20000 blocks in my x$bh;

and  i was  alter system checkpoint~~~

checkpint time  2009-6-28 22:24:12

select count(decode(bitand(flag,1), 0, 'N', 'Y')) as countdty,decode(bitand(flag,1), 0, 'N', 'Y') isdty from x$bh
group by decode(bitand(flag,1), 0, 'N', 'Y');

0 Y
22454 N

i did a delete from a bigtable;

now there is more than 5000 blocks  is dirty ~~  it's above 25% , and blow 40% for Max percentage of LRU list foreground can scan for free

just wait a mo (almost 1 min)

select count(decode(bitand(flag,1), 0, 'N', 'Y')) as countdty,decode(bitand(flag,1), 0, 'N', 'Y') isdty from x$bh
group by decode(bitand(flag,1), 0, 'N', 'Y');

93 Y
22361 N

the datafile's the point is 2009-6-28 22:25:33;

another  test , when the dirty blocks less than 3000blcok or less(grammar....)

i wait a mo too (about 2min or more),  dirty blocks in x$bh didn't chaged...

I think the experiment  can proived this parameter  is used for large enough size need to clean-out...


By the way , i'm trying study english and grammar....

[ 本帖最后由 flying_warrior 于 2009-6-28 23:17 编辑 ]

Yong Huang

flying_warrior,

Thank you. I think your test is trying to prove that if there're more than 25% of blocks that are dirty, they'll be written by DBWn to datafiles, and if there're less than that, they will not. So the parameter _db_large_dirty_queue is indeed a percent, not an absolute number (assuming that parameter determines the threshold we're talking about here).

I think that makes sense. Although I was initially "surprised", I also had doubt about the description "Number of ...", because an absolute number, especially as low as 25, doesn't sound right.

By the way, you probably need to wait 5 minutes instead of a short moment (shorter than 5 min). This is because liuyidba and I did some test in 9i and 10g on the effect of _dbwr_scan_interval. It seems that dirty buffers will be written at exactly 5 minutes after they become dirty, probably determined by _dbwr_scan_interval. Unfortunately changing that parameter's value doesn't change the 5 minute waiting time.

Yong Huang

flying_warrior

原帖由 Yong Huang 于 2009-6-29 10:00 发表
flying_warrior,

Thank you. I think your test is trying to prove that if there're more than 25% of blocks that are dirty, they'll be written by DBWn to datafiles, and if there're less than that, they will not. So the parameter _db_large_dirty_queue is indeed a percent, not an absolute number (assuming that parameter determines the threshold we're talking about here).

I think that makes sense. Although I was initially "surprised", I also had doubt about the description "Number of ...", because an absolute number, especially as low as 25, doesn't sound right.

By the way, you probably need to wait 5 minutes instead of a short moment (shorter than 5 min). This is because liuyidba and I did some test in 9i and 10g on the effect of _dbwr_scan_interval. It seems that dirty buffers will be written at exactly 5 minutes after they become dirty, probably determined by _dbwr_scan_interval. Unfortunately changing that parameter's value doesn't change the 5 minute waiting time.

Yong Huang

THankyou for your suggest~~And I got a new answer,I think the parameter "_dbwr_scan_interval" just a part of CLARE-OUT's Condition...

But it's not exactly  5 min  in anytime;

And I was  find a new parameter in x$kvii  "DBWR max outstanding writes" ;

it's meas that, the batch size limit for DBWR need write out, If they are full, or close to full, then there will clearly be benefit in raising the limit.

In 10GR2 ,the size is equal  to my initial parameter _DB_BLOCK_BUFFERS ;

I did a test for this....

my initial parameter _DB_BLOCK_BUFFERS   is 3992
when my dirty blcoks only 423 or less ,   i wait for a long time~~ not 5 min or 6  ,is't more time,~  nothing happend ,even the dirty blocks in x$bh is not decrease..

and when the drity blocks increase to 3832, wait  a short time, the blocks is clean-out~~ only 412 dirty blocks remain, and not checkpoint,no swtich log~

Yong Huang

I checked my 10.2.0.4 database. "DBWR max outstanding writes" is 4096 while buffer cache (from show sga) is 2768240640/8192=337920 buffers (I use 8k block size). And _db_block_buffers is 330068 in x$ksppsv. So none of the three numbers match. And x$bh has approximately 316603 rows.

Can you double check your numbers, and try to make some conclusion about at what threshold dirty buffers will be written out? Ignore the time experiment for now.

Yong Huang

nishi2b

原帖由 Yong Huang 于 2009-7-2 05:18 发表
I checked my 10.2.0.4 database. "DBWR max outstanding writes" is 4096 while buffer cache (from show sga) is 2768240640/8192=337920 buffers (I use 8k block size). And _db_block_buffers is 330068 in x$ksppsv. So none of the three numbers match. And x$bh has approximately 316603 rows.

Can you double check your numbers, and try to make some conclusion about at what threshold dirty buffers will be written out? Ignore the time experiment for now.

Yong Huang

Yeah ,when i was use my database for a while , the things as you said   its not equals ~

but what i said "In 10GR2 ,the size is equal  to my initial parameter _DB_BLOCK_BUFFERS ;"   ,it's just after my database installed...

so i think may be ,just may be something relation is in the _DB_BLOCK_BUFFERS and the DBWR max outstanding writes.......

because exactly  the _DB_BLOCK_BUFFERS is increased after I did sth, and it's never back to begin....

kevindream0509

oracle 8i以后 完全检查点只有在alter system checkpoint 以及非shutdown abort之外的正常关闭数据库的情况下才会触发
而增量检查点每隔三秒钟以及日志切换时启动
增量检查点是根据检查点队列将脏数据写到数据文件中，检查点队列中所挂载的脏数据buffer header链表是按数据块第一次被修改的时间先后顺序实现的。在控制文件中每隔三秒中，也就是俗称检查点的“心跳”，找出当前检查点队列上的第一个buffer header，并将该buffer header中所记录的LRBA（这个LRBA也就是checkpoint position了）记录到控制文件中去。buffer header除了记录LRBA之外如果是日志切换引发的增量检查点，还会将checkpoint position记录到每个数据文件头中。当一批脏数据块写完之后，就会将其对应的buffer header从检查点队列中摘下。
因此，对于Yong Huang的Indeed they're different.这句话表示赞同，但是后面一句But I don't think you can say the second case (触发的) is incremental. It's still complete表示疑问。
如果想法有不对之处，请多多请教。。。

kevindream0509

原帖由 nl_oracle 于 2009-6-25 12:52 发表
谢谢楼上两位。
还想问下楼上版主，那么日志切换触发的检查点的优先级低，没有其他任务的时候数据库可能才会写脏数据对吧，这个是我的测试环境，数据库没有什么工作量啊，我插入了一些数据，然后提交，然后切换日志。过了很长时间，active的状态还是没变啊。

我继续插入数据，提交，active日志组的状态还是没变，我又继续插入，提交，发现那个日志组的状态变成inactive了。是不是如果db cache中的脏数据没有达到一个阀值时是不会写入的呢？

DBWR要写脏数据块时，并不是说立即将所有的脏数据块都同时写入磁盘。为了尽量减少物理的
I/O的次数，DBWR会将要写的脏数据块所对应的buffer header拷贝到一个名为批量写（write batch）的结构中。每个working set所对应的DBWR进程都可以向该结构里拷贝buffer header。当write batch的buffer header的个数达到一定限额时，才会发生实际的I/O，从而将脏数据块写入磁盘。这个限额为硬件平台所能支持的同时并发的异步I/O的最大数量。8i之前是可以用隐藏参数（_db_block_write_batch）来控制这个限额的。但是8i以后，取消了该参数，而由oracle自己来计算。

Yong Huang

原帖由 kevindream0509 于 2009-7-3 21:53 发表
oracle 8i以后 完全检查点只有在alter system checkpoint 以及非shutdown abort之外的正常关闭数据库的情况下才会触发
而增量检查点每隔三秒钟以及日志切换时启动
...
因此，对于Yong Huang的Indeed they're different.这句话表示赞同，但是后面一句But I don't think you can say the second case (触发的) is incremental. It's still complete表示疑问。
如果想法有不对之处，请多多请教。。。

Thanks for the correction. When I wrote that, I had in my mind two concepts "fast vs slow checkpoint" (which determines checkpoint priority) and "complete vs incremental checkpoint" (which is about whether to write *all* buffers on the checkpoint queue). I searched on Metalink and Google and realized the first concept is old, used in Oracle 8 or older, and it seems to be the same as the second one. That is, there's only one concept, not two independent "orthogonal" concepts. Oracle just quietly renamed fast to complete and slow to incremental. The renaming is a good move, because it's really not about speed, but about how much the checkpoint queue is processed.

With that understanding, I pulled my book from the shelf, "Oracle8 Backup and Recovery Handbook" by Rama Velpuri, published in 1998. On p.244, three's a table listing all types of checkpoints. Since this book is hard to find and there's no ebook version, let me summarize here (some post-Oracle8 concepts are not listed of course):

These checkpoints are fast: alter system checkpoint (local or global), alter tablespace begin backup or offline (normal, temporary), instance shutdown (normal, immediate), log file switch stuck.

These checkpoints are slow: alter system switch logfile, log file switch normal, checkpoint due to log_checkpoint_(timeout|interval).

So is the checkpoint triggered by a logfile switch complete or incremental? According to Rama's book, normally it is incremental, consistent with what you said. It only becomes complete when the switch gets stuck, which Rama clearly explains in a paragraph, as in the case where you only have 2 small logfiles and Oracle can't switch to the other file because checkpoint of that old one is still being checkpointed.

Yong Huang

Yong Huang

原帖由 kevindream0509 于 2009-7-3 22:01 发表

当write batch的buffer header的个数达到一定限额时，才会发生实际的I/O，从而将脏数据块写入磁盘。这个限额为硬件平台所能支持的同时并发的异步I/O的最大数量。8i之前是可以用隐藏参数（_db_block_write_batch）来控制这个限额的。但是8i以后，取消了该参数，而由oracle自己来计算。

According to Steve Adams' research (http://www.ixora.com.au/q+a/0008/30140320.htm), _db_block_write_batch was replaced by _db_writer_chunk_writes, probably not by a simple parameter renaming, but by some concept change as well. Do you think it's still tunable? If you have any reference, please list it.

Yong Huang