Re: Needing to brush up on my internals -- FTS and DB buffer cache

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 29 Apr 2004 19:05:46 +0100
Message-ID: <007b01c42e14$9a1ad590$7102a8c0@Primary>

Bullet 1 is partly correct.

When you truncate or drop a table (shrink a rollback segment), Oracle does write all dirty blocks to disc before doing so and can do this by walking the checkpoint queue.

But there may be clean blocks that are not on the checkpoint queue, and Oracle has
to mark their buffer headers (x$bh) as free.

The only way to find them is to check every cache buffers chain. There are a couple of "optimisations" that could be used:

Each LATCH could be taken once whilst all the buckets covered by that latch are searched. But I don't think this is done
for small objects (measured as a X% of the size of the cache, Oracle could calculate for each block which chain the block ought to be on, and only hit the necessary chains. I believe Oracle is supposed to do this, but doesn't.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Cary Millsap" <cary.millsap_at_hotsos.com> To: <oracle-l_at_freelists.org> Sent: Thursday, April 29, 2004 2:52 PM Subject: RE: Needing to brush up on my internals -- FTS and DB buffer cache

Richard's hunch is correct: in the context where the statement is made, the statement is absurd.

In a very fast skim of the article, it looks like there are some other technical inaccuracies as well. For example, there's a statement in the article, where it says "there are times when the database must examine all of the blocks in the RAM cache..." I think this statement, combined with its three following bullet points, is unfortunately incorrect as well. I believe that in each of the three circumstances the author describes, the Oracle kernel doesn't sweep the cache, but only the buffers on the dirty list.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 5/7 Dallas, 5/18 New Jersey, 6/22 Pittsburgh

- SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Richard Foote Sent: Wednesday, April 28, 2004 9:25 AM
To: oracle-l_at_freelists.org
Subject: Re: Needing to brush up on my internals -- FTS and DB buffer cache

Original Message ----- From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com> To: "ORACLE-L (E-mail)" <oracle-l_at_freelists.org> Sent: Thursday, April 29, 2004 12:09 AM Subject: Needing to brush up on my internals -- FTS and DB buffer cache

While surfing Google for HP-UX questions, I ran across an Oracle guru page
that says this:

"Remember, when Oracle performs a full-table scan, the database blocks are
read directly into the program global area (PGA), bypassing the data buffer
RAM." The article doesn't say what version of Oracle the author was referring to.

Hi Rich,

I've got a horrible feeling I know who the author is ....

Be afraid, be very afraid .

BTW, did the article mention "parallel" reads at all ? It's relevant.

Cheers

Richard

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Thu Apr 29 2004 - 13:02:58 CDT