Re: Inserts slowing down on OLTP systems
In OLTP system if there are too many indexes defined on tables, insert slows down. As volume of data goes up, the system shows even worse performance. if you can reduce total no of indexes probably it will help...actually this is a trade off. Thanks, Saumyajit --- Jared Still [EMAIL PROTECTED] wrote: As Rachel already said, don't bother with hit ratios. They are in fact nearly useless for any kind of tuning. For a good ( but verbose ) paper on the subject, goto www.hotsos.com Jared On Monday 07 May 2001 17:55, Vikas Kawatra wrote: 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: Jared Still 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). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saumyajit Mandal 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).
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:[EMAIL PROTECTED]]
|| 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
|| col lf_rows_used heading 'Used|Leaf Rows' format
|| col ibadness heading '% Deleted|Leaf Rows' format 999.9
||
|| SELECT name, del_lf_rows, lf_rows - del_lf_rows
|| lf_rows_used,
||to_char(del_lf_rows / (lf_rows)*100,'999.9') 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:[EMAIL PROTECTED]]
|| 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 [EMAIL PROTECTED]
|| Reply-To: [EMAIL PROTECTED]
|| To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
|| 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: [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).
||
|| _
|| 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: [EMAIL PROTECTED]
||
|| Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
|| San Diego, California-- Public Internet access /
|| Mailing Lists
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 [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [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). _ 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Kawatra 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).
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
col lf_rows_used heading 'Used|Leaf Rows' format
col ibadness heading '% Deleted|Leaf Rows' format 999.9
SELECT name, del_lf_rows, lf_rows - del_lf_rows
lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.9') 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:[EMAIL PROTECTED]]
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 [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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: [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).
_
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
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
Re: Inserts slowing down on OLTP systems
As Rachel already said, don't bother with hit ratios. They are in fact nearly useless for any kind of tuning. For a good ( but verbose ) paper on the subject, goto www.hotsos.com Jared On Monday 07 May 2001 17:55, Vikas Kawatra wrote: 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: Jared Still 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).
RE: Inserts slowing down on OLTP systems
Hi, Can u elaborate? What does the statement Inserts slow down by 100% from levels 15 days ago? mean. I did not understand especially the levels part of it. Also, mention as to how you are doing the inserts. Are the inserts done as select * from another_table? or Using SQLLOADER? or Pro*C or anyother? I want these details 'Cos - Each method of loading data has its own optimization technique. Rajaram. -Original Message- From: Vikas Kawatra [SMTP:[EMAIL PROTECTED]] Sent: Monday, May 07, 2001 8:55 PM To: Multiple recipients of list ORACLE-L Subject:Inserts slowing down on OLTP systems 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: [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). NetZero Platinum No Banner Ads and Unlimited Access Sign Up Today - Only $9.95 per month! http://www.netzero.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajaram 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).
