Re: Needing to brush up on my internals -- FTS and DB buffer cache
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2004 19:05:46 +0100
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:
a) Each LATCH could be taken once
whilst all the buckets covered by that
latch are searched. But I don't think this
is done
b) 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@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Richard Foote
Sent: Wednesday, April 28, 2004 9:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Needing to brush up on my internals -- FTS and DB buffer
cache
----- Original Message -----
From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
