RE: 9.2.0.3 online index builds resolved (we hope)

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_at_DTAG.Com>
Sent by: root_at_fatcity.com
 06/05/2003 08:29 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_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_at_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_at_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_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.net
> --
> Author: Hallas, John, Tech Dev
> INET: John.Hallas_at_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_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.net
-- 
Author: Stephen Lee
  INET: Stephen.Lee_at_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_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.net
-- 
Author: 
  INET: Jared.Still_at_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_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).