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).