Message-ID: <F001.005AB29C.20030605101454@fatcity.com>
Date: Thu, 05 Jun 2003 10:14:54 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Jared.Still@radisys.com
Subject: RE: 9.2.0.3 online index builds resolved (we hope)
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Now I'm curious.

If the index size grows 5 fold in a day, is it noticably affecting 
performance?

If not, then why are you rebuilding the index?

If so, can you provide some traces detailing the waits?

I would suggest that rebuilding the index nightly is hurting performance 
during
the day with numerous index block splits.

Jared


Stephen Lee <Stephen.Lee@DTAG.Com>
Sent by: root@fatcity.com
 06/05/2003 08:29 AM
 Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
        cc: 
        Subject:        RE: 9.2.0.3 online index builds resolved (we hope)


As we found out last night, the "fix" doesn't fix anything.  The online
index rebuild will still hang indefinitely and lock the table.

Since there is bound to be some inquisitive mind out there wanting know 
why
we have a nightly online index rebuild: This is on tables that have 
complete
turnover in their data every 15 minutes .... COOL!  The index sizes grow
approximately five-fold in a day.  The DBA's here didn't write the app, 
and
during the design phase (using the term very loosely), we told them (in 
our
best cartoon voice)
Y-O-U-'-L-L  B-E  S-O-R-R-Y
(Actually, it's us that's sorry since we are the ones that have to deal 
with
it.)

We upgraded to 9.2.0.3 because of the show stopping shared pool
fragmentation in 8.1.7.4.  We apparently got rid of one problem and picked
up another one. (SURPRISE!)  I guess I'll have to write some logic in the
script to detect when the index build gets stuck and kill it.  Oracle 
really
should be begging us to be become a beta test site.  We can screw up a 
steel
ball.

> -----Original Message-----
> From: Hallas, John, Tech Dev [mailto:John.Hallas@gb.vodafone.co.uk]
> Sent: Thursday, June 05, 2003 4:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: 9.2.0.3 online index builds resolved (we hope)
> 
> 
> Stephen,
> When you say there IS an problem with index rebuilds what do you mean?
> Have Oracle acknowledged it as a bug?
> 
> Your work around  basically slows the volume of data being 
> passed through the buffer cache, presumably allowing other 
> users to get on with their work.
> I assume that this causes the index rebuild to take longer 
> than it did with with a higher MBRC on 8.1.7.4 - is that true ?
> 
> Just trying to determine the extent of this issue
> 
> John
> 
> -----Original Message-----
> Sent: 03 June 2003 18:05
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> Well folks, there IS an issue with online index builds in 
> 9.2.0.3.  Not only
> have we experienced "normal" SQL getting blocked, but we have 
> seen index
> builds hang indefinitely -- while holding the blocking lock 
> (of course!).
> The work around is (we hope):
> 
> alter session set db_file_multiblock_read_count=1;
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Stephen Lee
>   INET: Stephen.Lee@DTAG.Com
> 
> 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: 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.net
> -- 
> Author: Hallas, John, Tech Dev
>   INET: John.Hallas@gb.vodafone.co.uk
> 
> 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: 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.net
-- 
Author: Stephen Lee
  INET: Stephen.Lee@DTAG.Com

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: 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.net
-- 
Author: 
  INET: Jared.Still@radisys.com

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