RE: Inserts slowing down on OLTP systems

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_at_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 (<list of latches>)
||
|| 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_at_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 <VKawatra_at_innoventry.com>
|| >>Reply-To: ORACLE-L_at_fatcity.com
|| >>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
|| >>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_at_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_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).
|| >
|| >_________________________________________________________________
|| >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_at_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_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).
|| >--
|| >Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| >--
|| >Author: Vikas Kawatra
|| > INET: VKawatra_at_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_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).
|| >
|| --
|| Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| --
|| Author: John Kanagaraj
|| INET: john.kanagaraj_at_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_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).
||

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohan, Ross
  INET: MohanR_at_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_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).