Re: truncate command internal behavior

True.

I think 8i was walking the buffer cache, so the cost was proportional to the size of the buffer cache.

Then 9i introduced a 'small table' strategy which walked the blocks in the table then, for each block, checked if the block was in the buffer cache - which made the cost proportional to the size of the table.

Then 10gR2 seems to be working on a linked list per object running through the buffer headers - but I don't know where the linked list is rooted - which should make the cost proportional to the number of blocks buffered.

All comments above are from occasional observations, and old memories - so not guaranteed to be very accurate.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Kevin Closson" <kevinc_at_polyserve.com> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Tuesday, October 17, 2006 10:06 PM Subject: RE: truncate command internal behavior

>>>Third, a bit of curiosity: Does the time to truncate vary
>>>with the buffer cache size? That would imply a horribly
>>>blown cache search which I certainly hope is not the case.

...hoping JL will comment, but I can recall from experience that in 8i (can't remember if R1,R2 or R3), a VLM size SGA would take forever to truncate a table as the server process had to walk all the chains to invalidate the buffers ... that smoked a single CPU for a long time on a large SGA....

I routinely use 20+ GB SGAs and have not seen that in 9i or 10g.

FWIW

--
http://www.freelists.org/webpage/oracle-l