|
Replies:
15
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Aug 20, 2008 11:47 AM
Last Post By: Aman....
|
|
|
Posts:
300
Registered:
03/16/04
|
|
|
|
Dirty buffer
Posted:
Jul 29, 2005 6:27 AM
|
|
|
|
Hi all,
What is dirty buffer.
|
|
|
Posts:
24,000
Registered:
10/11/99
|
|
|
|
Re: Dirty buffer
Posted:
Jul 29, 2005 6:41 AM
in response to: neo
|
|
|
Normally, a dirty buffer is a buffer that has been changed in memory but not yet written to disk. Oracle only has to write dirty buffers to disk-- a buffer that was read into memory and not modified can be discarded from memory without the expense of going to disk.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC |
|
|
Posts:
18,604
Registered:
08/22/02
|
|
|
Posts:
541
Registered:
09/27/06
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 11:49 AM
in response to: N. Gasparotto
|
|
|
|
Here are some commands:
SQL> col object_name format a10
SQL> col dirty format a6
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'SCOTT';
no rows selected
SQL> update scott.emp set sal = 563 where empno=2;
1 row updated.
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'SCOTT';
STATUS OBJECT_NAM OBJECT_TYPE Dirty
----------
------
xcur EMP TABLE N
xcur PK_EMP INDEX N
7 rows selected.
SQL> commit;
Commit complete.
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'SCOTT';
STATUS OBJECT_NAM OBJECT_TYPE Dirty
----------
------
xcur EMP TABLE Y
xcur PK_EMP INDEX N
8 rows selected.
Does this mean that an UPDATE uncommitted dosn't create a dirty block in the buffer cache ? So we need to commit in order for a block to become dirty ? I am wordering this because I cannot understand how DBWR can write a modified block if it is not marcked "dirty".
Thanks,
Paul
|
|
|
Posts:
8,906
Registered:
05/20/01
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 12:32 PM
in response to: TPC
|
|
|
Intersting.The samw is there over 11gr1 in Windows XP.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> drop table t purge;
Table dropped.
SQL> create table t(a number)
2 ;
Table created.
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';
STATUS OBJECT_NAME OBJECT_TYPE D
---------- -------------------- ------------------- -
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
SQL> insert into t values(1);
1 row created.
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';
STATUS OBJECT_NAME OBJECT_TYPE D
---------- -------------------- ------------------- -
xcur T TABLE Y
[b]xcur T TABLE N[/b]
[b]xcur T TABLE N[/b]
xcur T TABLE Y
[b]xcur T TABLE N[/b]
[b]xcur T TABLE N[/b]
xcur T TABLE Y
[b]xcur T TABLE N[/b]
8 rows selected.
SQL> commit;
Commit complete.
SQL> SELECT b.status, object_name, object_type, dirty "Dirty" FROM v$bh b, dba_objects o
2 WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';
STATUS OBJECT_NAME OBJECT_TYPE D
---------- -------------------- ------------------- -
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
xcur T TABLE Y
8 rows selected.
SQL>
Couple of blocks got marked dirty after commit;And even when I issued queries from another session, Oracle didn't create a CR block from the same. They were still shown as XCUR only.
Aman....
Message was edited by: Did give a wrong reply that buffers were marked dirty initiall too.
Aman.... |
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 12:41 PM
in response to: TPC
|
|
|
Paul,
It looks like you're using 10g, which introduced private redo threads and delayed block changes. For "small" transactions, 10g generates private redo and doesn't apply the changes to the blocks until the commit. However the flag (x$bh.flag) has bit 3 set to 1 to show that private redo exists for the block.
When the commit occurs, the redo is applied to the block, at which point the block is marked as dirty, the private redo is then copied to the public redo buffer and LGWR is posted to write the redo to disc. (The treatment of the related undo blocks is similar).
If you ran your test on 9i, you would see the EMP table block marked as dirty as soon as you did the update.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." (Stephen Hawking) |
|
|
Posts:
8,906
Registered:
05/20/01
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 12:48 PM
in response to: Jonathan Lewis
|
|
|
|
Sir,
Excellent reply. So where is it documented that this behaviour is there in 10g and I believe also in 11g ? What is the benefit of doing this?One reason I can make out is that this should reduce the burden over LGWR but its better you give an explanation for this.
And why the buffers were marked dirty when I made the table? At that time which buffers got in the buffer cache which were marked as dirty by Oracle?
Regards
Aman....
|
|
|
Posts:
541
Registered:
09/27/06
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 12:52 PM
in response to: Aman....
|
|
|
Thanks Jonathan,
And yes, you are right, my database is 10g. 
Paul |
|
|
Posts:
10,896
Registered:
10/08/98
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 8:37 PM
in response to: Jonathan Lewis
|
|
|
|
Thanks Jonathan for the insight.
This metalink note offer some hint how to interprete flag column of x$bh
SCRIPT: Script to Report SGA Buffer Summary
Doc ID: Note:1019635.6
|
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: Dirty buffer
Posted:
Aug 19, 2008 10:52 PM
in response to: Aman....
|
|
|
Aman,
I'm not sure where this is documented - but it's probably in the performance guide, concepts guide, or the Admin guide somewhere.
If you can't find it there, google for "private redo" and either "tanel poder" or "Haisley" who have both produced some very good information about the feature.
I agree with your assumption about redo - in a highly concurrency OLTP system this should reduce the contention on the redo allocation and redo copy latches (by increasing the CPU, of course, and moving the contention to difference areas of the code where it is more "sharable").
When you did the 'create table' you still made some blocks dirty - if you report the block number, file number and class from the view (x$bh has more detail than v$bh) then you should be able to identify things like the segment header block, bitmap blocks and data blocks that have been created and modified.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." (Stephen Hawking) |
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
Posts:
8,906
Registered:
05/20/01
|
|
|
|
Re: Dirty buffer
Posted:
Aug 20, 2008 10:58 AM
in response to: Jonathan Lewis
|
|
|
|
Hi sir,
Yes I am searching but the only reference I got about it in the docs is about an error. I am not able (yet) to find the info from the docs.
I agree with your assumption about redo - in a highly concurrency OLTP system this should reduce the contention on the redo allocation and redo copy latches (by increasing the CPU, of course, and moving the contention to difference areas of the code where it is more "sharable").
I am searching for this stuff sir and the more I am searching, its appearing to be more and more interesting and definitely a good improvement. I shall search for Tanel's and Kyle's presentations for the same too.
When you did the 'create table' you still made some blocks dirty - if you report the block number, file number and class from the view (x$bh has more detail than v$bh) then you should be able to identify things like the segment header block, bitmap blocks and data blocks that have been created and modified.
I tried the creation of the table over 10gr2(10201) and 11106 systems. Both , for the same create table witha single column with number datatype , did show different results. In 10g, there were 3 blocks allocated and in 11g, there were much more than that. Number is not my botheration at the moment. I did try to find the info abou the block types and as you mentioned, they are header blocvks and stuff like that. But I am not able to understand one thing, why would a create table statement needs to enter buffer cache and have to pin some buffers from there in order to complete creation of table? Its a data dictionary structure in in the initial phase so for what purpose these blocks are actually required in the buffer cache and are marked dirty evem when there is nothing that we had changed for them. If they would be pinned, it would be okay. The blocks are shownin the status of XCUR which I guess is the exclusive access only but still being them dirty is some thing I am notable to understand. Can you please throw some light for it?
Thanks and best regards
Aman....
|
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: Dirty buffer
Posted:
Aug 20, 2008 11:29 AM
in response to: Aman....
|
|
|
Aman,
When you create a new physical object, you need to format the segment header (at least). If you are using ASSM (segment space management auto) then the first level bitmap blocks (class 8) and one second level bitmap block (class 9) have to be created in the first extent before the segment header block (class 4).
The number of first level bitmap blocks is dependent on the block size and extent size - which is why you may have seen some differences between the 10g and 11g tests.
I made a mistake in my original comment - I thought you have done a CTAS, which would also have required at least one data block (class 1) to be formatted as well.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." (Stephen Hawking) |
|
|
Posts:
8,906
Registered:
05/20/01
|
|
|
|
Re: Dirty buffer
Posted:
Aug 20, 2008 11:35 AM
in response to: Jonathan Lewis
|
|
|
|
Sir,
Thanks for the reply. I guess I am now understandng the reason to see the blocks being occupied but one doubt remains the same that for the creation(and the formatting ) of these blocks, why a memory access is needed? This is based on this understanding that the table creation is a data dictionary update command. So when the segment header is created and/or ASSM blocks are acquired, why Oracle picks them from the buffer cache, formats them , marks them dirty? This would increase the number of dirty buffers in the database IMO. Though I agree that this wont happen as a daily job but still ,as it is said, one hole is enough to sink the ship, may be one extra block marked as dirty may trigger DBWR to shoot an IO and at that time when we don't need it. That's why it is boggling me.
Regards
Aman....
|
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: Dirty buffer
Posted:
Aug 20, 2008 11:43 AM
in response to: Aman....
|
|
|
Aman,
Creatring a table is NOT just a dictionary management process. Critically, you allocate a data segment, and the segment header block (historically just the first block in that segment) has to be formatted and populated with information. That's one of the block that has to be "newed", formatted, made dirty and subsequently written.
There's a (very old) example of the sort of thing you see in a segment header block in a dictionary managed tablespace if you dump it as this URL: http://www.jlcomp.demon.co.uk/blockdum.html
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." (Stephen Hawking) |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
