RE: Progress of an index rebuild
that also uses v$session_longops ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, January 07, 2004 2:55 PM To: Multiple recipients of list ORACLE-L Or use Oracle Enterprise Manager 2.2 (coming with 9i) and check for session details and it will tell you how much completed and how long will it take... Regards Rafiq ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Progress of an index rebuild
Or use Oracle Enterprise Manager 2.2 (coming with 9i) and check for session details and it will tell you how much completed and how long will it take... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 07 Jan 2004 11:34:24 -0800 How about v$session_longops Cheers From: Daniel Hanks <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Progress of an index rebuild Date: Tue, 06 Jan 2004 09:49:25 -0800 Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). Are there better ways to see the progress of an index rebuild? Thanks for any help. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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). _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards 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). _ Worried about inbox overload? Get MSN Extra Storage now! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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).
RE: Progress of an index rebuild
How about v$session_longops Cheers From: Daniel Hanks <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Progress of an index rebuild Date: Tue, 06 Jan 2004 09:49:25 -0800 Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). Are there better ways to see the progress of an index rebuild? Thanks for any help. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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). _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Richards 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).
Re: Progress of an index rebuild
--- Daniel Hanks <[EMAIL PROTECTED]> wrote: > Is there any way to check on the progress of an > index rebuild? (Oracle 8i). I.e., it would be nice > to get some indicator as to how far an index rebuild > has gone, something like "75% done...". > Daniel, v$session_longops, perhaps? (at least for the sort operation). I'd run a test, but stomach tells me otherwise. There is a window in OEM, if you edit the session, that will show a progress bar. methinks that it is hitting v$session_longops under the hood. pretty easy to see which sql (in v$sql) belongs to OEM, as it is hinted with "OEM" in it. Paul > As a rough indicator, I've noticed when > rebuilding/moving an index to a different > tablespace, Oracle will allocate temporary extents > in the tablespace to which the index is being moved. > So, in theory, I suppose I could see how big (in > terms of extents) the index is before moving it and > use that as a rough indicator to see how far along > the rebuild is by counting the number of temporary > extents allocated in the new tablespace (taking into > account the extent size in the new tablespace). > > Are there better ways to see the progress of an > index rebuild? > > Thanks for any help. > > -- Dan > >Daniel Hanks - Systems/Database Administrator >About Inc., Web Services Division > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Daniel Hanks > 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Drake 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).
RE: Progress of an index rebuild
Daniel, Check out V$SESSION_LONGOPS. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 06, 2004 12:49 PM To: Multiple recipients of list ORACLE-L Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). Are there better ways to see the progress of an index rebuild? Thanks for any help. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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: Goulet, Dick 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).
Re: Progress of an index rebuild
Hi! Check v$session_longops view, it is meant for monitoring this kind of long-running jobs - but it can be quite inaccurate. Another way would be to check v$sort_usage during sorting phase of index recreation and then check the newly created index segments size (it'll be created as a temporary segment initially and switched to index-type afterwards). Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 7:49 PM > Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". > > As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). > > Are there better ways to see the progress of an index rebuild? > > Thanks for any help. > > -- Dan > >Daniel Hanks - Systems/Database Administrator >About Inc., Web Services Division > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Daniel Hanks > 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: Tanel Poder 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).
RE: Progress of an index rebuild
Have you checked v$session_longops ... ??? regular index builds show up there, so I guess rebuilds will show up as well ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, January 06, 2004 12:49 PM To: Multiple recipients of list ORACLE-L Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). Are there better ways to see the progress of an index rebuild? Thanks for any help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Progress of an index rebuild
Is there any way to check on the progress of an index rebuild? (Oracle 8i). I.e., it would be nice to get some indicator as to how far an index rebuild has gone, something like "75% done...". As a rough indicator, I've noticed when rebuilding/moving an index to a different tablespace, Oracle will allocate temporary extents in the tablespace to which the index is being moved. So, in theory, I suppose I could see how big (in terms of extents) the index is before moving it and use that as a rough indicator to see how far along the rebuild is by counting the number of temporary extents allocated in the new tablespace (taking into account the extent size in the new tablespace). Are there better ways to see the progress of an index rebuild? Thanks for any help. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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).
Re: index rebuild
Yeap , Thanks Dennis . I am keeping that in mind . -ak - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 23, 2003 2:54 PM > AK > Even though you can rebuild indexes online, it is a good idea to perform > this task when the table is being hit with only a light DML load. > > > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Wednesday, July 23, 2003 1:49 PM > To: Multiple recipients of list ORACLE-L > > > Hi! > > For index, yes, if you use: alter index rebuild compute statistics; > > But don't rebuild your indexes daily, it's an expensive operation... and you > won't gain much from it anyway. > > Tanel. > > - Original Message - > To: Multiple <mailto:[EMAIL PROTECTED]> recipients of list ORACLE-L > Sent: Wednesday, July 23, 2003 9:14 PM > > Does index rebuild generates new statistics as well ? How does that can hit > performance intermediately if I want to rebuild few indexes daily just to > finish everything in 5 days . > > -ak > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > 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: AK 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).
RE: index rebuild
AK Even though you can rebuild indexes online, it is a good idea to perform this task when the table is being hit with only a light DML load. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Hi! For index, yes, if you use: alter index rebuild compute statistics; But don't rebuild your indexes daily, it's an expensive operation... and you won't gain much from it anyway. Tanel. - Original Message - To: Multiple <mailto:[EMAIL PROTECTED]> recipients of list ORACLE-L Sent: Wednesday, July 23, 2003 9:14 PM Does index rebuild generates new statistics as well ? How does that can hit performance intermediately if I want to rebuild few indexes daily just to finish everything in 5 days . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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).
Re: index rebuild
Re: index rebuild
ak, You have to give a little to get a little. LMT for the indexes with partitioning will help performance in the long run. It also make maintenance of the database easier. Ron >>> [EMAIL PROTECTED] 07/23/03 05:14PM >>> The only reason I wanted to build the indexes was to relocate them to another tablespace ( LMT ) so that I can get rid of high fragmentation and start living . -ak - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 23, 2003 11:49 AM Subject: Re: index rebuild Hi! For index, yes, if you use: alter index rebuild compute statistics; But don't rebuild your indexes daily, it's an expensive operation... and you won't gain much from it anyway. Tanel. - Original Message - From: AK To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 23, 2003 9:14 PM Subject: index rebuild Does index rebuild generates new statistics as well ? How does that can hit performance intermediately if I want to rebuild few indexes daily just to finish everything in 5 days . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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).
Re: index rebuild
yeh, just found the rebuid_clause syntax have compute statistics option. My-bad. joan Joan Hsieh wrote: > > Ak, > > I just know you can collect statistics while creating index not rebuild > index. > > Joan > > > AK wrote: > > > > Does index rebuild generates new statistics as well ? How does that > > can hit performance intermediately if I want to rebuild few indexes > > daily just to finish everything in 5 days . > > > > -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh 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).
Re: index rebuild
The only reason I wanted to build the indexes was
to relocate them to another tablespace ( LMT ) so that I can get rid of high
fragmentation and start living .
-ak
- Original Message -
From:
Tanel
Poder
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, July 23, 2003 11:49
AM
Subject: Re: index rebuild
Hi!
For index, yes, if you use: alter index
rebuild compute statistics;
But don't rebuild your indexes daily, it's an
expensive operation... and you won't gain much from it anyway.
Tanel.
- Original Message -
From:
AK
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, July 23, 2003 9:14
PM
Subject: index rebuild
Does index rebuild generates new statistics as
well ? How does that can hit performance intermediately if I want to rebuild
few indexes daily just to finish everything in 5 days .
-ak
RE: index rebuild
Oh yes, index rebuild will solve all your problems. sqlplus "/ as sysdba" rtfm immediate Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 23, 2003 2:15 PMTo: Multiple recipients of list ORACLE-LSubject: index rebuild Does index rebuild generates new statistics as well ? How does that can hit performance intermediately if I want to rebuild few indexes daily just to finish everything in 5 days . -ak
Re: index rebuild
Hi! For index, yes, if you use: alter index rebuild compute statistics; But don't rebuild your indexes daily, it's an expensive operation... and you won't gain much from it anyway. Tanel. - Original Message - From: AK To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 23, 2003 9:14 PM Subject: index rebuild Does index rebuild generates new statistics as well ? How does that can hit performance intermediately if I want to rebuild few indexes daily just to finish everything in 5 days . -ak
Re: index rebuild
Ak, I just know you can collect statistics while creating index not rebuild index. Joan > AK wrote: > > Does index rebuild generates new statistics as well ? How does that > can hit performance intermediately if I want to rebuild few indexes > daily just to finish everything in 5 days . > > -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh 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).
index rebuild
Does index rebuild generates new statistics as well ? How does that can hit performance intermediately if I want to rebuild few indexes daily just to finish everything in 5 days . -ak
RE: INDEX REBUILD
Guang - Below is what Jared said on this topic: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 26, 2002 9:14 PM To: Multiple recipients of list ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL >= 5. go to asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared -Original Message- Sent: Thursday, June 19, 2003 12:34 PM To: Multiple recipients of list ORACLE-L Where can I find more detail info about "frequent index rebuilds can reduce your performance" ? Thanks. Guang -Original Message- Sent: Thursday, June 19, 2003 12:00 PM To: Multiple recipients of list ORACLE-L Seema - If you analyze in conjunction with rebuilds, you could track with the LAST_ANALYZED column in USER_INDEXES table. That is all I can think of. Why do you ask? Jared has spoken eloquently about how frequent index rebuilds can reduce your performance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: DENNIS WILLIAMS 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).
RE: INDEX REBUILD
8i which release? It was broken in 8.1.6 and before, but fixed in 8.1.7. -Original Message- Sent: Thursday, June 19, 2003 2:01 PM To: Multiple recipients of list ORACLE-L Anup this view will not help in 8i. thanks anyway! >From: "Arup Nanda" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: INDEX REBUILD >Date: Thu, 19 Jun 2003 08:20:06 -0800 > >Seema, > >Select last_ddl_time and timestamp from user_objects where the object_name >= >'' > >HTH > >Arup Nanda >- Original Message - >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >Sent: Thursday, June 19, 2003 11:29 AM > > > > Hi, > > > > Is any view available which can tell us indexe rebuild status in 8i? > > Means how can we know these are indexes rebuilded recently? > > thx > > -Seema > > > > _ > > The new MSN 8: smart spam protection and 2 months FREE* > > http://join.msn.com/?page=features/junkmail > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Seema Singh > > 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: Arup Nanda > 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). _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Seefelt, Beth 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).
Re: INDEX REBUILD
Anup this view will not help in 8i. thanks anyway! From: "Arup Nanda" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: INDEX REBUILD Date: Thu, 19 Jun 2003 08:20:06 -0800 Seema, Select last_ddl_time and timestamp from user_objects where the object_name = '' HTH Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 19, 2003 11:29 AM > Hi, > > Is any view available which can tell us indexe rebuild status in 8i? > Means how can we know these are indexes rebuilded recently? > thx > -Seema > > _ > The new MSN 8: smart spam protection and 2 months FREE* > http://join.msn.com/?page=features/junkmail > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Seema Singh > 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: Arup Nanda 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). _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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).
RE: INDEX REBUILD
Where can I find more detail info about "frequent index rebuilds can reduce your performance" ? Thanks. Guang -Original Message- Sent: Thursday, June 19, 2003 12:00 PM To: Multiple recipients of list ORACLE-L Seema - If you analyze in conjunction with rebuilds, you could track with the LAST_ANALYZED column in USER_INDEXES table. That is all I can think of. Why do you ask? Jared has spoken eloquently about how frequent index rebuilds can reduce your performance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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).
Re: INDEX REBUILD
hi, From user_objects.last_ddl_time. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, June 20, 2003 12:00 AM > Seema - If you analyze in conjunction with rebuilds, you could track with > the LAST_ANALYZED column in USER_INDEXES table. That is all I can think of. > Why do you ask? Jared has spoken eloquently about how frequent index > rebuilds can reduce your performance. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Thursday, June 19, 2003 10:29 AM > To: Multiple recipients of list ORACLE-L > > > Hi, > > Is any view available which can tell us indexe rebuild status in 8i? > Means how can we know these are indexes rebuilded recently? > thx > -Seema > > _ > The new MSN 8: smart spam protection and 2 months FREE* > http://join.msn.com/?page=features/junkmail > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Seema Singh > 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: DENNIS WILLIAMS > 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: zhu chao 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).
Re: INDEX REBUILD
Seema, Select last_ddl_time and timestamp from user_objects where the object_name = '' HTH Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 19, 2003 11:29 AM > Hi, > > Is any view available which can tell us indexe rebuild status in 8i? > Means how can we know these are indexes rebuilded recently? > thx > -Seema > > _ > The new MSN 8: smart spam protection and 2 months FREE* > http://join.msn.com/?page=features/junkmail > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Seema Singh > 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: Arup Nanda 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).
RE: INDEX REBUILD
Seema - If you analyze in conjunction with rebuilds, you could track with the LAST_ANALYZED column in USER_INDEXES table. That is all I can think of. Why do you ask? Jared has spoken eloquently about how frequent index rebuilds can reduce your performance. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, June 19, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Hi, Is any view available which can tell us indexe rebuild status in 8i? Means how can we know these are indexes rebuilded recently? thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: DENNIS WILLIAMS 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).
RE: INDEX REBUILD
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>
INDEX REBUILD
Hi, Is any view available which can tell us indexe rebuild status in 8i? Means how can we know these are indexes rebuilded recently? thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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).
RE: Online Index Rebuild Tuning
Sorry to take so long to respond (not much time for list lately...) If you are going to be rebuilding regularly, then DO NOT use compressed indexes. They save lots of space and seem to work fairly well, but you cannot do online index rebuilds with them (at least it was that way in 8.1.6) Another option might be to use partitioned indexes so you can rebuild a partition at a time. Strange as it may seem, Oracle allows you to create partitioned indexes on non-partitioned tables. Babette Turner-Underwood -Original Message- Richard Sent: Thursday, December 05, 2002 4:36 PM To: Multiple recipients of list ORACLE-L Connor, That's a good point - something which I really hadn't thought about. Unfortunately many of the indexes relate to foreign keys and primary keys, which are an ever increasing value here. I've already tried rebuilding one or two small indexes and they shrunk from ~180MB to ~70MB. Also, I have heard about compressing indexes, but it is something I have never used before. Can anyone shed some light on the topic? Are there any drawbacks (ie: reduced IO but increased processing)? Thanks, Mark. Connor McDonald To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Online Index Rebuild Tuning Sent by: [EMAIL PROTECTED] om 05/12/2002 20:24 Please respond to ORACLE-L The first question is whether you really need to rebuild them. If the indexes columns are such that the values are likely to be reused, then I wouldn't bother - since that deleted space will get reused as required. Cheers Connor --- Mark Richard <[EMAIL PROTECTED]> wrote: > Folks, > > I know that when creating indexes a couple of > settings such as > SORT_AREA_SIZE can have a big impact on duration. > What settings apply > during online rebuilds? Are the rules the same? > What tips do you have? > > Basically we have some very large indexes in an OLTP > system (several > indexes are across ~250m rows, several GB in > physical storage) which have > fairly low density due to deletes and updates. In > looks like the time has > come to rebuild then to gain some performance. Any > other suggestions > regarding tricks to avoid this, etc would be greatly > appreciated. > > Thanks, > Mark. > > PS: If you going to suggest things which are > version specific we're > dealing with 8.1.7.4 on Solaris. > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> >Privileged/Confidential information may be > contained in this message. > If you are not the addressee indicated in > this message >(or responsible for delivery of the message > to such person), > you may not copy or deliver this message > to anyone. > In such case, you should destroy this message and > kindly notify the sender >by reply e-mail or by telephone on (61 3) > 9612-6999. >Please advise immediately if you or your employer > does not consent to > Internet e-mail for messages of this > kind. > Opinions, conclusions and other information > in this message > that do not relate to the official > business of > Transurban City Link Ltd > shall be understood as neither given nor > endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Mark Richard > 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 > a
Re: Online Index Rebuild Tuning
Ah, yes, I was bitten by one of those persistent myths some weeks ago, involving this very subject. Never stop learning... Jraed On Friday 06 December 2002 01:04, Connor McDonald wrote: > A few nasties still exist with compression on indexes > if they are going to be used as a unique or primary > key - you'll have to come to my session at UKOUG if > you want to see why :-) > > But also jumping back to Mark's point about rebuilding > the index because it contains ever increasing values - > this is not necessarily a driver toward rebuild the > index. A simple example follows: > > SQL> create table t1 ( x number, y number); > > Table created. > > SQL> create index t1x on t1 (x); > > Index created. > > SQL> insert into t1 > 2 select rownum,rownum > 3 from sys.source$ > 4 where rownum < 10; > > 9 rows created. > > SQL> analyze index t1x compute statistics; > > Index analyzed. > > SQL> select leaf_blocks from user_indexes > 2 where index_name = 'T1X'; > > LEAF_BLOCKS > --- > 200 > > -- remove the "lower" half of the rows > > SQL> delete from t1 where x < 5; > > 4 rows deleted. > > SQL> commit; > > Commit complete. > > rem > rem and add to the top > rem > > SQL> insert into t1 > 2 select rownum+10,rownum+10 > 3 from sys.source$ > 4 where rownum < 5; > > 4 rows created. > > SQL> analyze index t1x compute statistics; > > Index analyzed. > > SQL> select leaf_blocks from user_indexes > 2 where index_name = 'T1X'; > > LEAF_BLOCKS > --- > 202 > > -- and again > > SQL> delete from t1 where x < 10; > > 5 rows deleted. > > SQL> commit; > > Commit complete. > > SQL> insert into t1 > 2 select rownum+15,rownum+15 > 3 from sys.source$ > 4 where rownum < 5; > > 4 rows created. > > SQL> analyze index t1x compute statistics; > > Index analyzed. > > SQL> select leaf_blocks from user_indexes > 2 where index_name = 'T1X'; > > LEAF_BLOCKS > --- > 205 > > Cheers > Connor > > --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > > Compressing indexes has been an option since 8i, and > > > Jonathan Lewis has > > done some interesting presentations on this (I > > witnessed it at our > > Database Forum in Middelfart this year - very > > impresive). > > > > In short, it changes the way you should think of > > concatenated indexes, > > ie you should put the least selective column first, > > then compress it. > > That way you'll end up with very small indexes > > compared to the old days > > and ways. > > > > I don't see any drawbacks to this approach except > > that you of course has > > to unlearn what you have learned (Yoda?)... > > > > Other index things Jonathan adresses include: It is > > actually better to > > index small tables, even one-row tables. > > > > Mogens > > > > Rachel Carmichael wrote: > > > > > >http://www.tusc.com/oracle/download/author.html#loneyk > > > > >--- John Kanagaraj <[EMAIL PROTECTED]> wrote: > > >>Mark, > > >> > > >>>Also, I have heard about compressing indexes, but > > > > it is > > > > >>>something I have > > >>>never used before. Can anyone shed some light on > > > > the topic? > > > > >>>Are there any > > >>>drawbacks (ie: reduced IO but increased > > > > processing)? > > > > >>Kevin Loney presented a paper on this at IOUG 2002 > > > > - should be in the > > > > >>archives at www.ioug.org. > > >> > > >>John Kanagaraj > > >>Oracle Applications DBA > > >>DB Soft Inc > > >>Work : (408) 970 7002 > > >> > > >>Listen to great, commercial-free christian music > > > > 24x7x365 at > > > > >>http://www.klove.com > > >> > > >>** The opinions and facts contained in this > > > > message are entirely mine > > > > >>and do not reflect those of my employer or > > > > customers ** > > > > >>-- > > >>Please see the official ORACLE-L FAQ: > > > > http://www.orafaq.com > > > > >>-- > > >>Author: John Kanagaraj > > >> 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). > > > > >__ > > >Do you Yahoo!? > > >Yahoo! Mail Plus - Powerful. Affordable. Sign up > > > > now. > > > > >http://mailplus.yahoo.com > > = > Connor McDonald > http://www.oracledba.co.uk > http://www.oaktable.net > > "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, > and...he will sit in a boat and drink beer al
RE: Online Index Rebuild Tuning
Title: RE: Online Index Rebuild Tuning Uh oh ... read this yesterday on Metalink the version part I don't remember, but in that case if your PK is compressed, Oracle wouldn't return ORA-1 error ... that's scary. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Steve Perry [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 7:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: Online Index Rebuild Tuning I tried using compress in the past and ran into a bug with "and_equal" access paths. You'd get ORA-600's. I think it was 8.1.7.2 on Win2k. don't know if it's been fixed. steve This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Online Index Rebuild Tuning
A few nasties still exist with compression on indexes if they are going to be used as a unique or primary key - you'll have to come to my session at UKOUG if you want to see why :-) But also jumping back to Mark's point about rebuilding the index because it contains ever increasing values - this is not necessarily a driver toward rebuild the index. A simple example follows: SQL> create table t1 ( x number, y number); Table created. SQL> create index t1x on t1 (x); Index created. SQL> insert into t1 2 select rownum,rownum 3 from sys.source$ 4 where rownum < 10; 9 rows created. SQL> analyze index t1x compute statistics; Index analyzed. SQL> select leaf_blocks from user_indexes 2 where index_name = 'T1X'; LEAF_BLOCKS --- 200 -- remove the "lower" half of the rows SQL> delete from t1 where x < 5; 4 rows deleted. SQL> commit; Commit complete. rem rem and add to the top rem SQL> insert into t1 2 select rownum+10,rownum+10 3 from sys.source$ 4 where rownum < 5; 4 rows created. SQL> analyze index t1x compute statistics; Index analyzed. SQL> select leaf_blocks from user_indexes 2 where index_name = 'T1X'; LEAF_BLOCKS --- 202 -- and again SQL> delete from t1 where x < 10; 5 rows deleted. SQL> commit; Commit complete. SQL> insert into t1 2 select rownum+15,rownum+15 3 from sys.source$ 4 where rownum < 5; 4 rows created. SQL> analyze index t1x compute statistics; Index analyzed. SQL> select leaf_blocks from user_indexes 2 where index_name = 'T1X'; LEAF_BLOCKS --- 205 Cheers Connor --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > Compressing indexes has been an option since 8i, and > Jonathan Lewis has > done some interesting presentations on this (I > witnessed it at our > Database Forum in Middelfart this year - very > impresive). > > In short, it changes the way you should think of > concatenated indexes, > ie you should put the least selective column first, > then compress it. > That way you'll end up with very small indexes > compared to the old days > and ways. > > I don't see any drawbacks to this approach except > that you of course has > to unlearn what you have learned (Yoda?)... > > Other index things Jonathan adresses include: It is > actually better to > index small tables, even one-row tables. > > Mogens > > Rachel Carmichael wrote: > > >http://www.tusc.com/oracle/download/author.html#loneyk > > > > > >--- John Kanagaraj <[EMAIL PROTECTED]> wrote: > > > > > >>Mark, > >> > >> > >> > >>>Also, I have heard about compressing indexes, but > it is > >>>something I have > >>>never used before. Can anyone shed some light on > the topic? > >>>Are there any > >>>drawbacks (ie: reduced IO but increased > processing)? > >>> > >>> > >>Kevin Loney presented a paper on this at IOUG 2002 > - should be in the > >>archives at www.ioug.org. > >> > >>John Kanagaraj > >>Oracle Applications DBA > >>DB Soft Inc > >>Work : (408) 970 7002 > >> > >>Listen to great, commercial-free christian music > 24x7x365 at > >>http://www.klove.com > >> > >>** The opinions and facts contained in this > message are entirely mine > >>and do not reflect those of my employer or > customers ** > >>-- > >>Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >>-- > >>Author: John Kanagaraj > >> 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). > >> > >> > >> > > > > > >__ > >Do you Yahoo!? > >Yahoo! Mail Plus - Powerful. Affordable. Sign up > now. > >http://mailplus.yahoo.com > > > > > > = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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 thi
Re: Online Index Rebuild Tuning
On Thursday 05 December 2002 13:35, Mark Richard wrote: > Also, I have heard about compressing indexes, but it is something I have > never used before. Can anyone shed some light on the topic? Are there any > drawbacks (ie: reduced IO but increased processing)? > To obtain optimal results with a compressed index, you need to build it with the least selective columns as the leading columns. A great deal of compression can be had, but you need to be careful where you use it. It can lead to a lot of buffer busy waits. I found that one out the hard way. ;) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
Re: Online Index Rebuild Tuning
Compressing indexes has been an option since 8i, and Jonathan Lewis has done some interesting presentations on this (I witnessed it at our Database Forum in Middelfart this year - very impresive). In short, it changes the way you should think of concatenated indexes, ie you should put the least selective column first, then compress it. That way you'll end up with very small indexes compared to the old days and ways. I don't see any drawbacks to this approach except that you of course has to unlearn what you have learned (Yoda?)... Other index things Jonathan adresses include: It is actually better to index small tables, even one-row tables. Mogens Rachel Carmichael wrote: http://www.tusc.com/oracle/download/author.html#loneyk --- John Kanagaraj <[EMAIL PROTECTED]> wrote: Mark, Also, I have heard about compressing indexes, but it is something I have never used before. Can anyone shed some light on the topic? Are there any drawbacks (ie: reduced IO but increased processing)? Kevin Loney presented a paper on this at IOUG 2002 - should be in the archives at www.ioug.org. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
RE: Online Index Rebuild Tuning
http://www.tusc.com/oracle/download/author.html#loneyk --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > Mark, > > >Also, I have heard about compressing indexes, but it is > >something I have > >never used before. Can anyone shed some light on the topic? > >Are there any > >drawbacks (ie: reduced IO but increased processing)? > > Kevin Loney presented a paper on this at IOUG 2002 - should be in the > archives at www.ioug.org. > > John Kanagaraj > Oracle Applications DBA > DB Soft Inc > Work : (408) 970 7002 > > Listen to great, commercial-free christian music 24x7x365 at > http://www.klove.com > > ** The opinions and facts contained in this message are entirely mine > and do not reflect those of my employer or customers ** > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Kanagaraj > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: Online Index Rebuild Tuning
Mark, >Also, I have heard about compressing indexes, but it is >something I have >never used before. Can anyone shed some light on the topic? >Are there any >drawbacks (ie: reduced IO but increased processing)? Kevin Loney presented a paper on this at IOUG 2002 - should be in the archives at www.ioug.org. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
Re: Online Index Rebuild Tuning
I tried using compress in the past and ran into a bug with "and_equal" access paths. You'd get ORA-600's. I think it was 8.1.7.2 on Win2k. don't know if it's been fixed. steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 3:35 PM > Connor, > > That's a good point - something which I really hadn't thought about. > Unfortunately many of the indexes relate to foreign keys and primary keys, > which are an ever increasing value here. I've already tried rebuilding one > or two small indexes and they shrunk from ~180MB to ~70MB. > > Also, I have heard about compressing indexes, but it is something I have > never used before. Can anyone shed some light on the topic? Are there any > drawbacks (ie: reduced IO but increased processing)? > > Thanks, > Mark. > > > > > Connor > McDonald To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > co.uk> Subject: Re: Online Index Rebuild Tuning > Sent by: > [EMAIL PROTECTED] > om > > > 05/12/2002 > 20:24 > Please respond > to ORACLE-L > > > > > > > The first question is whether you really need to > rebuild them. If the indexes columns are such that > the values are likely to be reused, then I wouldn't > bother - since that deleted space will get reused as > required. > > Cheers > Connor > > --- Mark Richard <[EMAIL PROTECTED]> wrote: > > Folks, > > > > I know that when creating indexes a couple of > > settings such as > > SORT_AREA_SIZE can have a big impact on duration. > > What settings apply > > during online rebuilds? Are the rules the same? > > What tips do you have? > > > > Basically we have some very large indexes in an OLTP > > system (several > > indexes are across ~250m rows, several GB in > > physical storage) which have > > fairly low density due to deletes and updates. In > > looks like the time has > > come to rebuild then to gain some performance. Any > > other suggestions > > regarding tricks to avoid this, etc would be greatly > > appreciated. > > > > Thanks, > > Mark. > > > > PS: If you going to suggest things which are > > version specific we're > > dealing with 8.1.7.4 on Solaris. > > > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > > >Privileged/Confidential information may be > > contained in this message. > > If you are not the addressee indicated in > > this message > >(or responsible for delivery of the message > > to such person), > > you may not copy or deliver this message > > to anyone. > > In such case, you should destroy this message and > > kindly notify the sender > >by reply e-mail or by telephone on (61 3) > > 9612-6999. > >Please advise immediately if you or your employer > > does not consent to > > Internet e-mail for messages of this > > kind. > > Opinions, conclusions and other information > > in this message > > that do not relate to the official > > business of > > Transurban City Link Ltd > > shall be understood as neither given nor > > endorsed by it. > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Mark Richard > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > San Diego, California-- Mailing list and web > > hosting services > > > -
Re: Online Index Rebuild Tuning
Connor, That's a good point - something which I really hadn't thought about. Unfortunately many of the indexes relate to foreign keys and primary keys, which are an ever increasing value here. I've already tried rebuilding one or two small indexes and they shrunk from ~180MB to ~70MB. Also, I have heard about compressing indexes, but it is something I have never used before. Can anyone shed some light on the topic? Are there any drawbacks (ie: reduced IO but increased processing)? Thanks, Mark. Connor McDonald To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Online Index Rebuild Tuning Sent by: [EMAIL PROTECTED] om 05/12/2002 20:24 Please respond to ORACLE-L The first question is whether you really need to rebuild them. If the indexes columns are such that the values are likely to be reused, then I wouldn't bother - since that deleted space will get reused as required. Cheers Connor --- Mark Richard <[EMAIL PROTECTED]> wrote: > Folks, > > I know that when creating indexes a couple of > settings such as > SORT_AREA_SIZE can have a big impact on duration. > What settings apply > during online rebuilds? Are the rules the same? > What tips do you have? > > Basically we have some very large indexes in an OLTP > system (several > indexes are across ~250m rows, several GB in > physical storage) which have > fairly low density due to deletes and updates. In > looks like the time has > come to rebuild then to gain some performance. Any > other suggestions > regarding tricks to avoid this, etc would be greatly > appreciated. > > Thanks, > Mark. > > PS: If you going to suggest things which are > version specific we're > dealing with 8.1.7.4 on Solaris. > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >Privileged/Confidential information may be > contained in this message. > If you are not the addressee indicated in > this message >(or responsible for delivery of the message > to such person), > you may not copy or deliver this message > to anyone. > In such case, you should destroy this message and > kindly notify the sender >by reply e-mail or by telephone on (61 3) > 9612-6999. >Please advise immediately if you or your employer > does not consent to > Internet e-mail for messages of this > kind. > Opinions, conclusions and other information > in this message > that do not relate to the official > business of > Transurban City Link Ltd > shall be understood as neither given nor > endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > > -- > Please see the official ORACLE-L FAQ: >
Re: Online Index Rebuild Tuning
The first question is whether you really need to rebuild them. If the indexes columns are such that the values are likely to be reused, then I wouldn't bother - since that deleted space will get reused as required. Cheers Connor --- Mark Richard <[EMAIL PROTECTED]> wrote: > Folks, > > I know that when creating indexes a couple of > settings such as > SORT_AREA_SIZE can have a big impact on duration. > What settings apply > during online rebuilds? Are the rules the same? > What tips do you have? > > Basically we have some very large indexes in an OLTP > system (several > indexes are across ~250m rows, several GB in > physical storage) which have > fairly low density due to deletes and updates. In > looks like the time has > come to rebuild then to gain some performance. Any > other suggestions > regarding tricks to avoid this, etc would be greatly > appreciated. > > Thanks, > Mark. > > PS: If you going to suggest things which are > version specific we're > dealing with 8.1.7.4 on Solaris. > > <<>> >Privileged/Confidential information may be > contained in this message. > If you are not the addressee indicated in > this message >(or responsible for delivery of the message > to such person), > you may not copy or deliver this message > to anyone. > In such case, you should destroy this message and > kindly notify the sender >by reply e-mail or by telephone on (61 3) > 9612-6999. >Please advise immediately if you or your employer > does not consent to > Internet e-mail for messages of this > kind. > Opinions, conclusions and other information > in this message > that do not relate to the official > business of > Transurban City Link Ltd > shall be understood as neither given nor > endorsed by it. > <<<> > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Mark Richard > 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). > = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Re: Online Index Rebuild Tuning
As far as I remember the online rebuild does a FFS (Fast Full Scan) of the existing index' leaf blocks, then builds the new one and finally does some clever stuff before switching over (renaming the new index from a temporary segment to a permanent). So there's a lot of reading and writing involved (IO considerations) and the sort area will probably be used just as heavily as if you created a new index (but I'm not too sure about that) and finally there's of course the usual storage consideration (LMT's, etc.). That's about it, I believe. Mogens Mark Richard wrote: Folks, I know that when creating indexes a couple of settings such as SORT_AREA_SIZE can have a big impact on duration. What settings apply during online rebuilds? Are the rules the same? What tips do you have? Basically we have some very large indexes in an OLTP system (several indexes are across ~250m rows, several GB in physical storage) which have fairly low density due to deletes and updates. In looks like the time has come to rebuild then to gain some performance. Any other suggestions regarding tricks to avoid this, etc would be greatly appreciated. Thanks, Mark. PS: If you going to suggest things which are version specific we're dealing with 8.1.7.4 on Solaris. <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
Online Index Rebuild Tuning
Folks, I know that when creating indexes a couple of settings such as SORT_AREA_SIZE can have a big impact on duration. What settings apply during online rebuilds? Are the rules the same? What tips do you have? Basically we have some very large indexes in an OLTP system (several indexes are across ~250m rows, several GB in physical storage) which have fairly low density due to deletes and updates. In looks like the time has come to rebuild then to gain some performance. Any other suggestions regarding tricks to avoid this, etc would be greatly appreciated. Thanks, Mark. PS: If you going to suggest things which are version specific we're dealing with 8.1.7.4 on Solaris. <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard 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).
RE: alter index rebuild online
Hello, I have used this dynamic script (on 8.1.6.x and 8.1.7.x) successfully with no corruption, for the past two years. The script actually moves the indexes from where they are originally built on a data tblsp to its index tblsp. However, dropping the "and tablespace_name = 'TBLSPD'", will result in a in-situ rebuild, which is probably what you want. Perhaps your environment is not conducive to index rebuilds ? I have 64-bit HP-UNIX, 32-bit oracle. set pagesize 0 set lines 120 set verify off set echo off set feedback off set head off spool move_indexes.sql select 'alter index '||index_name||' rebuild tablespace TBLSPX; ' from all_indexes where owner = 'MYSCHEMA' and tablespace_name = 'TBLSPD' order by index_name; spool off start move_indexes Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 05, 2002 11:43 AM To: Multiple recipients of list ORACLE-L I'm still back on 8.1.6 and when I tried to use 'alter index rebuild online' I got corrupted indexes. I saw on metalink that it's supposed to be fixed by 8.1.7.1 - anybody using successfully now? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: alter index rebuild online
Works great on 9.0.1 david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Tel. (919) 466-6723 Fax (919) 466-6783 Mobile (919) 225-4962 [EMAIL PROTECTED] http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Tuesday, March 05, 2002 11:43 AM To: Multiple recipients of list ORACLE-L I'm still back on 8.1.6 and when I tried to use 'alter index rebuild online' I got corrupted indexes. I saw on metalink that it's supposed to be fixed by 8.1.7.1 - anybody using successfully now? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: David Wagoner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
alter index rebuild online
I'm still back on 8.1.6 and when I tried to use 'alter index rebuild online' I got corrupted indexes. I saw on metalink that it's supposed to be fixed by 8.1.7.1 - anybody using successfully now? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Loader Direct Load - Problem with Index Rebuild
Hello Jay Here is a little script that will generate the commands needed to rebuild the indexes: spool rebuild.sql select distinct 'alter table '||table_name||' modify partition '||p.partition_name||' rebuild unusable local indexes;' from user_ind_partitions p, user_TAB_PARTITIONS t where p.status='UNUSABLE' and p.partition_name=t.partition_name; select distinct 'alter index '||index_name||' rebuild;' from user_indexes where status='INVALID' or status='UNUSABLE'; spool off Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Miller, Jay [SMTP:[EMAIL PROTECTED]] > Sent: Wed, January 16, 2002 5:51 PM > To: Multiple recipients of list ORACLE-L > Subject: SQL Loader Direct Load - Problem with Index Rebuild > > Hi, > > Okay, this is the second time this has happened and while I have a guess > I'd > appreciate any feedback on the issue. Last night during a direct load to > one of our datawarehouse tables 4 of the index partition rebuilds failed > with the following errors (the remaining partitions were fine, as were all > the other indexes): > > index EIS.IDX_BKP_TRANS_DATE partition BOOK2001OCT was made unusable due > to: > ORA-01652: unable to extend temp segment by 320 in tablespace > TS_BOOKKEEPING_FACT_IDX > index EIS.IDX_BKP_TRANS_DATE partition BOOK2001NOV was made unusable due > to: > ORA-01658: unable to create INITIAL extent for segment in tablespace > TS_BOOKKEEPING_FACT_IDX > index EIS.IDX_BKP_TRANS_DATE partition BOOK2001DEC was made unusable due > to: > ORA-01658: unable to create INITIAL extent for segment in tablespace > TS_BOOKKEEPING_FACT_IDX > index EIS.IDX_BKP_TRANS_DATE partition BOOK2002JAN was made unusable due > to: > ORA-01658: unable to create INITIAL extent for segment in tablespace > TS_BOOKKEEPING_FACT_IDX > > I was able to manually rebuild the index partitions with no problem. > > There are currently 1,881 free extents of the size 320 blocks in that > tablespace. The entire index (including all partitions) only takes up 473 > extents. We have degrees=1 on the index. > > There's a metalink Forum thread (ORA 1652 During Direct Load) where a > similar problem occurred. It seems to imply each index may be claiming 2x > it's required space while being rebuilt and that SMON might not clean up > that space right away. This could certainly have caused there to be not > enough space available if it required the tablespace to be 2x the size of > *all* the indexes. In that case the fact that SMON cleaned it up before I > did the manual rebuild would explain why the latter worked. > Looking at the tablespace I see that it is currently almost 2x the size so > it's possible that's why I had avoided the problem for the last few > months. > > Has anyone else had this problem and is there a workaround short of > dropping > the indexes before the load and recreating them? I'd rather avoid that > since it's usually only a few partitions that need to be rebuilt. > > I'm on Oracle 8.1.6.3, Solaris 2.6. > > Thanks, > Jay Miller > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Miller, Jay > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > This e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SQL Loader Direct Load - Problem with Index Rebuild
Jay, Had the same problem when I used a script during the move of data. A look at the database tablespaces and indexes showed that the indexes all resided in TEMP and were not perminently written into the designed tablespace before the next partition started. The TEMP filled and the remaining indexes failed with the "failed to extend.." problem. I increased the size of the datafile used in the TEMP tablespace until to load was completed and then RESIZE'd it down to the max size used for the load. I figure that the loads going forward are going to be as large if not larger so I will need the additional space. My space increased from 1.4 GIG to 2.1 GIG on the datafile used as the TEMP tablespace. Other than putting some delays in the load plan to allow the updates to complete, your manual method works. ROR mª¿ªm >>> JayMiller@Received: from CONNECT-MTA by galotterTDWaterhouse.com 01/16/02 10:50AM Hi, Okay, this is the second time this has happened and while I have a guess I'd appreciate any feedback on the issue. Last night during a direct load to one of our datawarehouse tables 4 of the index partition rebuilds failed with the following errors (the remaining partitions were fine, as were all the other indexes): index EIS.IDX_BKP_TRANS_DATE partition BOOK2001OCT was made unusable due to: ORA-01652: unable to extend temp segment by 320 in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2001NOV was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2001DEC was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2002JAN was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX I was able to manually rebuild the index partitions with no problem. There are currently 1,881 free extents of the size 320 blocks in that tablespace. The entire index (including all partitions) only takes up 473 extents. We have degrees=1 on the index. There's a metalink Forum thread (ORA 1652 During Direct Load) where a similar problem occurred. It seems to imply each index may be claiming 2x it's required space while being rebuilt and that SMON might not clean up that space right away. This could certainly have caused there to be not enough space available if it required the tablespace to be 2x the size of *all* the indexes. In that case the fact that SMON cleaned it up before I did the manual rebuild would explain why the latter worked. Looking at the tablespace I see that it is currently almost 2x the size so it's possible that's why I had avoided the problem for the last few months. Has anyone else had this problem and is there a workaround short of dropping the indexes before the load and recreating them? I'd rather avoid that since it's usually only a few partitions that need to be rebuilt. I'm on Oracle 8.1.6.3, Solaris 2.6. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL Loader Direct Load - Problem with Index Rebuild
Hi, Okay, this is the second time this has happened and while I have a guess I'd appreciate any feedback on the issue. Last night during a direct load to one of our datawarehouse tables 4 of the index partition rebuilds failed with the following errors (the remaining partitions were fine, as were all the other indexes): index EIS.IDX_BKP_TRANS_DATE partition BOOK2001OCT was made unusable due to: ORA-01652: unable to extend temp segment by 320 in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2001NOV was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2001DEC was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX index EIS.IDX_BKP_TRANS_DATE partition BOOK2002JAN was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX I was able to manually rebuild the index partitions with no problem. There are currently 1,881 free extents of the size 320 blocks in that tablespace. The entire index (including all partitions) only takes up 473 extents. We have degrees=1 on the index. There's a metalink Forum thread (ORA 1652 During Direct Load) where a similar problem occurred. It seems to imply each index may be claiming 2x it's required space while being rebuilt and that SMON might not clean up that space right away. This could certainly have caused there to be not enough space available if it required the tablespace to be 2x the size of *all* the indexes. In that case the fact that SMON cleaned it up before I did the manual rebuild would explain why the latter worked. Looking at the tablespace I see that it is currently almost 2x the size so it's possible that's why I had avoided the problem for the last few months. Has anyone else had this problem and is there a workaround short of dropping the indexes before the load and recreating them? I'd rather avoid that since it's usually only a few partitions that need to be rebuilt. I'm on Oracle 8.1.6.3, Solaris 2.6. Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
