Slow Inserts
Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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).
Re: Slow Inserts
[EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Any idea why you have as many parses as executes ? That's where all the elapsed time is, parsing. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [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).
Re: Slow Inserts
The Parse statistics are what caught my eye. The statement is being parsed once per execution, which is puzzling because it does use bind variables. Of more concern, though, is the difference between CPU and elapsed times for the parsing, indicating that there is a lot of waiting going on. I'd suggest looking at v$session_event to see what are the top wait events for the session. break on sid col event format a30 set pages 99 select se.event, se.total_waits, se.time_waited, se.average_wait from v$session s, v$session_event se where s.sid = se.sid and s.username is not null and s.sid = sid order by se.time_waited / Run this before and after executing the insert statement, and compute the delta for each wait event seen. HTH Paul Baumgartel --- [EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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). __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.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 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: [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).
RE: Slow Inserts
And a note: Using bind variables is a necessary step but not a sufficient step toward eliminating parse CALLS. Using bind variables will only reduce the number of misses in the library cache (parse count (hard)). If the application still puts the parse call inside a loop, it still gets the parse once per execute behavior shown in the trace file. When the application says parse (prepare or whatever), the Oracle kernel will parse (unless something on the client side stops the call from going to SQL*Net). See Scaling applications to massive user counts at www.hotsos.com/catalog. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Baumgartel Sent: Friday, November 15, 2002 11:20 AM To: Multiple recipients of list ORACLE-L The Parse statistics are what caught my eye. The statement is being parsed once per execution, which is puzzling because it does use bind variables. Of more concern, though, is the difference between CPU and elapsed times for the parsing, indicating that there is a lot of waiting going on. I'd suggest looking at v$session_event to see what are the top wait events for the session. break on sid col event format a30 set pages 99 select se.event, se.total_waits, se.time_waited, se.average_wait from v$session s, v$session_event se where s.sid = se.sid and s.username is not null and s.sid = sid order by se.time_waited / Run this before and after executing the insert statement, and compute the delta for each wait event seen. HTH Paul Baumgartel --- [EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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). __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City
