| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Online index creation on 9.2
I see, you can't find ot the waits because you don't want to ruin the
performance again on purpose. That makes sense!
Online index rebuild has been problematic WRT the journal application phase (among other problems such as abandoned journal segments after cancel or failure) since it was introduced. On highly transactional systems, a variety of waits, mostly library cache related, can occur at the end of the index rebuild. Many more such problems are addressed (fixed) in 9.2.0.3 than were in 8.1.7.
Unfortunately, you probably will need to hit this while doing a 10046 trace with waits in one of the application processes and the index rebuild. ALternatively, multiple systemstate or hanganalyze dumps, or even the output from a typical session's v$session_event or v$session_wait from the time of the slowdown would help support to ID what is actulally going on. Different systems access patterns are not alike enough just to say you have problem X based on your description.
So management can either live with slowness due to indexes not being rebuilt for a long time, an outage to rebuild them in blocking mode, or another shot at the online rebuild to find the root cause and get it fixed for good.
Or you can try to get it to happen under load on a test system.
Hope this helps!
-- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 29 May 2003, Stephen Lee wrote:Received on Thu May 29 2003 - 17:51:58 CDT
>
> I appreciate the response. The questions here are not "Are there waits?"
> Most obviously, there are. The questions flying around here are: We have
> been doing this with no problems when the database was 8.1.7.4. Now we try
> when the database is 9.2.0.3, and we don't just see waits, we see the thing
> go right into the dirt. So question is, does 9.2.0.3 have "issues" with
> online index builds on large tables in a large database? We have a TAR
> going, and we are going to try to figure out a way we can get some
> diagnostics going. There is no way management is going to allow us to do
> this on the production database. It would have been nice if a big mess of
> diagnostics had been running at the time. But it wasn't (other than
> spotlight -- OK, I use _ONE_ GUI), and when you're up to your ass in
> alligators, it's hard to think much about draining the swamp. I suspect
> what we will try to find out is what type of transactions would be most
> affected and get with the application people to see if we find some
> correlation.
>
> > -----Original Message-----
> >
> > What are the wait events of the slowed sessions?
> >
> > What is the correspondence with v$session_longops phases for the
> > rebuild?
> >
> > Really, the answer to 75% of the questions in this group is "look at
> > v$session_wait."
> >
> --
> 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: Jeremiah Wilton INET: jwilton_at_speakeasy.net 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).
 |
 |