OTN Discussion Forums : HWM question ...

This question is answered.

Reply to this Thread

Search Forum

Back to Thread List

Replies: 5 - Pages: 1 - Last Post: Nov 30, 2008 4:50 AM Last Post By: Aman....

OracleGuy777

Posts: 529
Registered: 06/15/08

HWM question
Posted: Nov 30, 2008 3:37 AM

hi guys,

it says:

The high water mark (HWM) is the pointer to the DBA of the last block to receive inserts. Blocks below this mark are maintained on free lists. Blocks in extents above this mark have been allocated to the segment but haven't been used yet. Blocks above the HWM are available for SQLLoader direct-load operations, parallel DML, and for inserts after the free list has been exhausted and the HWM has been moved.

is there a reason that the operations in bold look for blocks above the HWM?

thanks

Aman....

Posts: 9,550
Registered: 05/20/01

Re: HWM question
Posted: Nov 30, 2008 3:43 AM

in response to: OracleGuy777

Correct

OracleGuy777 wrote:

The high water mark (HWM) is the pointer to the DBA of the last block to receive inserts. Blocks below this mark are maintained on free lists. Blocks in extents above this mark have been allocated to the segment but haven't been used yet. Blocks above the HWM are available for SQLLoader direct-load operations, parallel DML, and for inserts after the free list has been exhausted and the HWM has been moved.

is there a reason that the operations in bold look for blocks above the HWM?

The mentioned operations actually create first temporary extents. These extents are allocated "above" the HWM and once the operation is committed than only they are merged with the actual extents of the segment below the HWM. Once done with the merge, the HWM is moved to reflect the change.This is same what happens when we exhaust the list of HWM blocks.Oracle simply moves the pointer to reflect the next set of blocks.
HTH
Aman....

Richard Foote

Posts: 485
Registered: 12/13/99

Re: HWM question
Posted: Nov 30, 2008 3:52 AM

in response to: OracleGuy777

Helpful

Yes, because direct-load operations, etc. are more "efficient" DML operations that can by-pass many of the general overheads associated with DML statements such as reading blocks into the buffer cache, navigating freelists, redo generation, contending with other transactions, etc. and so need to process their own independant blocks which have to therefore be those above the current HWM and "appended" at the completion of the transaction.

Cheers

Richard Foote
http://richardfoote.wordpress.com/

OracleGuy777

Posts: 529
Registered: 06/15/08

Re: HWM question
Posted: Nov 30, 2008 4:26 AM

in response to: Richard Foote

many thanks guys.

burleson

Posts: 2,343
Registered: 05/06/98

Re: HWM question
Posted: Nov 30, 2008 4:36 AM

in response to: OracleGuy777

Hi,

is there a reason that the operations in bold look for blocks above the HWM?

Good question. It's all about performance!

Data loads are I/O intensive and it's important to minimize disk I/O.

By using "fresh", brand-new, completely-empty blocks above the HWN, more rows can be inserted before a disk write happens.

Some shops also use large blocksizes in staging tables, for this same reason . . .

Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference"
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

Aman....

Posts: 9,550
Registered: 05/20/01

Re: HWM question
Posted: Nov 30, 2008 4:50 AM

in response to: burleson

Don,

burleson wrote:

By using "fresh", brand-new, completely-empty blocks above the HWN, more rows can be inserted before a disk write happens.

I have been researching about this term for another question.Can you explain this in a more exhaustive manner?When Oracle has to allocate a block and the block was in the past having some data.Now a delete followed by commit has cleared the block.Now when we insert some data,would oracle insert that data in a new,clean block or it will try using the same old block? I know that there are other condiions like PCTUSED and all are there but still,I would like to hear what you would say about it?
Regards
Aman....

Pages: 1

Back to Thread List

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)