From MohanR@STARS-SMI.com Wed, 09 May 2001 10:00:07 -0700 From: "Mohan, Ross" Date: Wed, 09 May 2001 10:00:07 -0700 Subject: RE: Inserts slowing down on OLTP systems Message-ID: MIME-Version: 1.0 Content-Type: text/plain as a minor embellishment to John's scriptology, you might also consider searching on the phrase 'index browning'. A little while ago, it was the esoteric, nouveau way of referring to leaf block aging. || -----Original Message----- || From: John Kanagaraj [mailto:john.kanagaraj@hds.com] || Sent: Tuesday, May 08, 2001 3:31 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Inserts slowing down on OLTP systems || || || Hi Vikas, || || In simple words, a block split happens when an INSERT needs || to add an entry || into a leaf block and finds it full, requiring it to 'split' || and balance || itself by migrating half the index entries into the new || block (at's why it's || called a B Tree or 'Balanced' tree). More details in the || oh-so-rarely-read || Server Concepts manual... || || As far as specific events go, you may need to look at 'latch || free' waits - || trace the p2 values from v$session_wait (usually 'cache || buffer chains') || using the following sqls || || select event, count(*) from v$session_wait || group by event || || select p2, count(*) from v$session_wait || where event = 'latch free' || group by p2 || || select * from v$latchname where latch# in () || || V$SYSTEM_EVENT will tell about the system as a whole while || V$SESSION_WAIT || will tell you what that particular process is waiting for. || Are many INSERTs || doen in parallel? There could also be an issue with || FREELISTS (that's a || different ballgame altogether). || || What you should probably do is to investigate your 'Index || badness' using the || following: || || ANALYZE INDEX &&index_name VALIDATE STRUCTURE; || || col name heading 'Index Name' format a30 || col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999 || col lf_rows_used heading 'Used|Leaf Rows' format 99999999 || col ibadness heading '% Deleted|Leaf Rows' format 999.99999 || || SELECT name, del_lf_rows, lf_rows - del_lf_rows || lf_rows_used, || to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness || FROM index_stats where name = upper('&&index_name'); || || undefine index_name || || As a rule of thumb if 10-15% of the table data changes, then || you should || consider rebuilding the index. || || Hth, || John Kanagaraj || Oracle Applications DBA || Hitach Data Systems, Santa Clara || Work : (408) 970 7002 || || >-----Original Message----- || >From: Vikas Kawatra [mailto:VKawatra@innoventry.com] || >Sent: Tuesday, May 08, 2001 10:52 AM || >To: Multiple recipients of list ORACLE-L || >Subject: RE: Inserts slowing down on OLTP systems || > || > || >What specific events should I look for in these tables ! - || >atleast the imp || >ones - || >And What's a block split ? || > || >vikas || > || >-----Original Message----- || >Sent: Tuesday, May 08, 2001 6:32 AM || >To: Multiple recipients of list ORACLE-L || > || > || >instead of checking hit ratios, try looking at wait events in || >v$session_event, v$system_event and v$session_wait. || > || >also -- how many indexes are on these tables? How much data || is in the || >tables? If the indexes are doing constant block splits on the || >insert you are || > || >basically rebuilding the index each time.. this can have a || >major impact on || >performance. || > || >Rachel || > || > || >>From: Vikas Kawatra || >>Reply-To: ORACLE-L@fatcity.com || >>To: Multiple recipients of list ORACLE-L || >>Subject: Inserts slowing down on OLTP systems || >>Date: Mon, 07 May 2001 16:55:28 -0800 || >> || >>Anyone have ideas on why OLTP database inserts would slow || >down by 100% from || >>levels 15 days ago ? || >> || >>We alreay checked hit ratios in shared pool , checked for || any storage || >>issues || >>etc || >> || >>thanks || >> || >>vikas || >>-- || >>Please see the official ORACLE-L FAQ: http://www.orafaq.com || >>-- || >>Author: Vikas Kawatra || >> INET: VKawatra@innoventry.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@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). || > || >_________________________________________________________________ || >Get your FREE download of MSN Explorer at http://explorer.msn.com || > || >-- || >Please see the official ORACLE-L FAQ: http://www.orafaq.com || >-- || >Author: Rachel Carmichael || > INET: carmichr@hotmail.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@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). || >-- || >Please see the official ORACLE-L FAQ: http://www.orafaq.com || >-- || >Author: Vikas Kawatra || > INET: VKawatra@innoventry.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@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). || > || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: John Kanagaraj || INET: john.kanagaraj@hds.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@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). || -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: MohanR@STARS-SMI.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@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).