Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: insert stmt disk reads

Re: insert stmt disk reads

From: Daniel W. Fink <optimaldba_at_yahoo.com>
Date: Tue, 18 Mar 2003 05:34:01 -0800
Message-ID: <F001.0056C5DA.20030318053401@fatcity.com>


Let's follow the transaction and see what happens. I am sure there are some errors and omissions, so please take with a grain of salt and add other steps/clarifications.

  1. Object resolution. If the object definition does not already exist in the dictionary cache, it must be read from the data dictionary.
  2. Translate the object to a physical location. Again, this requires a read from the data dictionary.
  3. Read the undo segment header and allocate a slot in the transaction table
  4. Read the undo block indicated by the undo header
  5. Read the segment header. If there are indexes, read those headers as well.
  6. Read the first free block on the list to memory. Insert data.
  7. Read the appropriate branch and leaf blocks for each index
  8. If space management is required, read the extent map(s) (dictionary or bitmap)

As you can see, an insert of a single record can cause many reads to occur. IIRC, you can see the file and block output in the raw trace output from sql_trace. Track these back to the file/block values in dba_extents and it will tell you which objects you are reading.

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
    Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
    Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


AK wrote:


>I dont have any foreign key const on the table . Yes but there are two
>indexes on this table . Would that cause this high disk reads ?
>
>-ak
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Monday, March 17, 2003 10:14 AM
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: optimaldba_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Tue Mar 18 2003 - 07:34:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US