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.
[EMAIL PROTECTED]
__________________________________________________
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
