From oracle-l-bounce@freelists.org Thu Apr 29 13:02:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3TI2hw32394 for ; Thu, 29 Apr 2004 13:02:53 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3TI2X632360 for ; Thu, 29 Apr 2004 13:02:43 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D3A8272E9E2; Thu, 29 Apr 2004 12:54:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11511-31; Thu, 29 Apr 2004 12:54:13 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1D85272CDFD; Thu, 29 Apr 2004 12:54:13 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Apr 2004 12:53:00 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BBA8272EB8C for ; Thu, 29 Apr 2004 12:52:53 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10691-75 for ; Thu, 29 Apr 2004 12:52:53 -0500 (EST) Received: from gadolinium.btinternet.com (gadolinium.btinternet.com [194.73.73.111]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F398C72EB8D for ; Thu, 29 Apr 2004 12:52:52 -0500 (EST) Received: from [217.43.69.162] (helo=Primary) by gadolinium.btinternet.com with smtp (Exim 3.22 #25) id 1BJFur-0006u8-00 for oracle-l@freelists.org; Thu, 29 Apr 2004 19:05:45 +0100 Message-ID: <007b01c42e14$9a1ad590$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <000001c42df1$2f1c7960$6701a8c0@CVMLAP02> Subject: Re: Needing to brush up on my internals -- FTS and DB buffer cache Date: Thu, 29 Apr 2004 19:05:46 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4039 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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" To: 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Richard Foote Sent: Wednesday, April 28, 2004 9:25 AM To: oracle-l@freelists.org Subject: Re: Needing to brush up on my internals -- FTS and DB buffer cache ----- Original Message ----- From: "Jesse, Rich" To: "ORACLE-L (E-mail)" 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@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 ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------