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:[EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
>
> 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: [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.net
> --
> Author: Hallas, John, Tech Dev
> INET: [EMAIL PROTECTED]
>
> 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: [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.net
--
Author: Stephen Lee
INET: [EMAIL PROTECTED]
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: [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).
