Oracle-L: consistent reads during inserts

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Fri, 12 Apr 2002 10:33:23 -0800
Message-ID: <F001.00443732.20020412103323@fatcity.com>

Hi, all. Sorry to be such a pest, but I'd like to ask another question about my insert performance problem.

An example of the trace output for the two machines is shown below. There are many other traces containing the same insert statements, and the values are very similar:

Machine 1--acceptable performance

INSERT INTO TLMPCSV (
SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD, REC_INS_TS )
VALUES
( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE )

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0   
       0
Execute  10975     11.37      15.88          1        907     131437   
   10975
Fetch        0      0.00       0.00          0          0          0   
       0

------- ------ -------- ---------- ---------- ---------- ----------

total 10975 11.37 15.88 1 907 131437

10975

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (APP730LM01) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE

Machine 2--unacceptable performance

INSERT INTO TLMPCSV (
SRT_SCH_SYS_NR,BCD_LBL_REF_TE,LD_REF_NR,SVC_TYP_CD, REC_INS_TS )
VALUES
( :b1,:b2,:b3,NVL(:b4,'000'),SYSDATE )

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0   
       0
Execute  11010     12.40      55.78          0       3903     134549   
   11010
Fetch        0      0.00       0.00          0          0          0   
       0

------- ------ -------- ---------- ---------- ---------- ----------

total 11010 12.40 55.78 0 3903 134549

11010

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 65 (APP730LM01) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE

Notice that, in each case, approximately 11,000 executions of the insert statement used about 12 or so seconds of CPU time, got about 130,000 buffers in current mode, and caused little or no disk activity. The ELAPSED time, though, is 15 seconds vs. 55 seconds. The only other statistic that differs is "query", which is about 900 on the fast machine, and 4000 on the slow machine. "Query" is defined as number of buffers gotten for consistent read.

So, I'm wondering why an insert needs buffers in consistent read mode, and, as a follow-up, if my assumption is correct that consistent read buffers are always obtained from a rollback segment. Finally, would any of you draw the conclusion that the difference in elapsed time between these two is due to the difference in number of consistent reads?

Thanks!

Paul Baumgartel, Adept Computer Associates, Inc. paul.baumgartel_at_aya.yale.edu

Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: treegarden_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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 Fri Apr 12 2002 - 13:33:23 CDT