RE: 9.2.0.3 online index builds resolved (we hope)

Why not just drop and recreate the indexes nightly instead of rebuild? Oracle provided a cool little package called DBMS_METADATA that allows you to extract the DDL from the database. If you use this package you don't have to worry about managing table/index changes as you simply extract the definition form the data dictionary every night.

Kevin

-----Original Message-----
Sent: Thursday, June 05, 2003 11:30 AM
To: Multiple recipients of list ORACLE-L

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: Kevin Toepke
  INET: ktoepke_at_rlcarriers.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).