RE: table reorganizations
Niall, In the first case disk space was the primary reason, performance improvement being a positive side-affect, so as to avoid a major disk array upgrade. I know that new inserts would use the deleted space in the pruged tables. However that free-space within the table block isn't very flexible i.e. it can only be used for inserts into the particular table. After the reorg the space is available to all objects in the tablespace and in our case as we moved the tables to new tablespaces we were also able to reduce the size of the tablespaces, therefore gving the space back as the OS level where it can be allocated to any tablespace in the future. In the second the "table-swap" was first implemented under Oracle 7 so alter table move wasn't available. I hope this explains the reasoning. Chris Quoting Niall Litchfield <[EMAIL PROTECTED]>: > Hi Chris > > Richard, > > > > I agree there are a number of reasons for reorganising > > tables. LMTs remove the > > need to reorganise a tablespace but not to reorganise a > > table. Two further real- ilfe examples of table reorgs: > > > > 1) The purge programs have at last been written and run > > deleting data > 2 years > > old. The system's been running for 4 years. So in simple > > terms most of the > > tables are approx 50% empty. You need to reorg in this case. > > What would be the rationale for this? If the rationale is performance how > much faster does it make everything, and how many users are complaining to > start with? If purely for space management purposes then I'd ask why the > deleted space could not be reused? > > > 2) A "transaction log" table is inserted to throughout the > > day and most of the > > night. A clear down processing job runs at the end of the day > > and deletes all > > the rows its processed, but more rows are being added. So the > > table is now < 1% > > full. Not good for FTS. So instead of a conventional reorg we > > implemented a > > nightly "table-swap". This meant locking the source table, > > copying it's > > contents to a replica empty single extent table, target > > table. The names of the > > target and source tables are swapped, hence "table-swap". The > > new source table > > is now available to the application and the original source > > is truncated and > > ready to be the target in 24 hrs time. > > > Pah. Once a day indeed. Once every 5 minutes... I'm interested in why you > implemented "table swap" instead of 'alter table move' which is what we did. > > > > > > > Cheers, > > > > Chris Dunscombe > > Cheers > > Niall > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Niall Litchfield > 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). > Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: table reorganizations
Tom, In my first example you are right that new inserts would use the space freed by the deletes but the purge program is run every quarter (sorry for not stating that explictly earlier). Therefore there is at most 2.25 years worth of data in the tables when before the first purge there was 4 years. Hence I believe the table reorg is valid for both disk space savings and performance. In my second example the amount of data loaded into the "transaction log" table can vary dramatically due to "double-day" processing caused by public holidays etc. Therefore if the table wasn't reorganised daily the table would end up being at least twice as large as necessary and therefore impcat the performance of FTS. These examples were both on 8i but I don't think 9i would make any difference. Pls let me know the details if 9i does make a difference. Cheers, Chris Quoting "Mercadante, Thomas F" <[EMAIL PROTECTED]>: > Chris, > > I would argue that in your two examples, nothing needs to be done if you are > using Locally Managed Tablespaces. All of the free space that your deletes > generated would be reused by new inserts. When you say "not good for FTS", > I think you are wrong. Have you tried testing this statement? How much > "slower" is it? > > Of course, I am talking about using Oracle 9i. Prior versions behaved much > differently. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Friday, January 09, 2004 6:09 AM > To: Multiple recipients of list ORACLE-L > > > Richard, > > I agree there are a number of reasons for reorganising tables. LMTs remove > the > need to reorganise a tablespace but not to reorganise a table. Two further > real- > ilfe examples of table reorgs: > > 1) The purge programs have at last been written and run deleting data > 2 > years > old. The system's been running for 4 years. So in simple terms most of the > tables are approx 50% empty. You need to reorg in this case. > > 2) A "transaction log" table is inserted to throughout the day and most of > the > night. A clear down processing job runs at the end of the day and deletes > all > the rows its processed, but more rows are being added. So the table is now < > 1% > full. Not good for FTS. So instead of a conventional reorg we implemented a > nightly "table-swap". This meant locking the source table, copying it's > contents to a replica empty single extent table, target table. The names of > the > target and source tables are swapped, hence "table-swap". The new source > table > is now available to the application and the original source is truncated and > > ready to be the target in 24 hrs time. > > Cheers, > > Chris Dunscombe > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > MessageHi Thomas, > > > > Never say never (oh bugger, I've just gone and done it myself). > > > > A large table accessed via a FTS for various important reporting > requirements > > has permanently shrunk in size from 10G to 100M (say list of Informix > > customers ;) > > > > Business requirements have changed and you need to add some columns to a > > table resulting in mucho row migration. > > > > You were told (incorrectly) that rows would grow significantly after > loading > > (honestly) but now the 80 pctfree value you've set is causing problems for > > other really important reports. > > > > There are of course other cases but you get my point ;) > > > > Cheers > > > > Richard > > - Original Message - > > From: Mercadante, Thomas F > > To: Multiple recipients of list ORACLE-L > > Sent: Thursday, January 08, 2004 6:34 AM > > Subject: RE: table reorganizations > > > > > > Jolene, > > > > Tables should never *need* to be reorganized. This is an old falacy. > If > > you know how big a table is going to grow, say in a year, then place it in > a > > Locally Managed tablespace with extent sizes to hold enough data for one > year > > (say 1M). > > > > You should never have to reorganize a table. > > > > Tom Mercadante > > Oracle Certified Professional > > -Original Message- > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, January 07, 2004 2:39 PM > > To: Multiple recipients of list ORACLE-L > > Subject: table reorganizations > > > > > > What SQL statement do you use to identify tables that need
Re: table reorganizations
Rachel, Thanks for the idea but the system is running 8i. I'll remember it for the future. Chris Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > Chris, > > Have you considered using dbms_redefinition for your second case? That > would allow you to reorg and swap the tables without locking for any > length of time. > > Rachel > > > --- [EMAIL PROTECTED] wrote: > > Richard, > > > > I agree there are a number of reasons for reorganising tables. LMTs > > remove the > > need to reorganise a tablespace but not to reorganise a table. Two > > further real- > > ilfe examples of table reorgs: > > > > 1) The purge programs have at last been written and run deleting data > > > 2 years > > old. The system's been running for 4 years. So in simple terms most > > of the > > tables are approx 50% empty. You need to reorg in this case. > > > > 2) A "transaction log" table is inserted to throughout the day and > > most of the > > night. A clear down processing job runs at the end of the day and > > deletes all > > the rows its processed, but more rows are being added. So the table > > is now < 1% > > full. Not good for FTS. So instead of a conventional reorg we > > implemented a > > nightly "table-swap". This meant locking the source table, copying > > it's > > contents to a replica empty single extent table, target table. The > > names of the > > target and source tables are swapped, hence "table-swap". The new > > source table > > is now available to the application and the original source is > > truncated and > > ready to be the target in 24 hrs time. > > > > Cheers, > > > > Chris Dunscombe > > > > > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > > > MessageHi Thomas, > > > > > > Never say never (oh bugger, I've just gone and done it myself). > > > > > > A large table accessed via a FTS for various important reporting > > requirements > > > has permanently shrunk in size from 10G to 100M (say list of > > Informix > > > customers ;) > > > > > > Business requirements have changed and you need to add some columns > > to a > > > table resulting in mucho row migration. > > > > > > You were told (incorrectly) that rows would grow significantly > > after loading > > > (honestly) but now the 80 pctfree value you've set is causing > > problems for > > > other really important reports. > > > > > > There are of course other cases but you get my point ;) > > > > > > Cheers > > > > > > Richard > > > - Original Message - > > > From: Mercadante, Thomas F > > > To: Multiple recipients of list ORACLE-L > > > Sent: Thursday, January 08, 2004 6:34 AM > > > Subject: RE: table reorganizations > > > > > > > > > Jolene, > > > > > > Tables should never *need* to be reorganized. This is an old > > falacy. If > > > you know how big a table is going to grow, say in a year, then > > place it in a > > > Locally Managed tablespace with extent sizes to hold enough data > > for one year > > > (say 1M). > > > > > > You should never have to reorganize a table. > > > > > > Tom Mercadante > > > Oracle Certified Professional > > > -Original Message- > > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, January 07, 2004 2:39 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: table reorganizations > > > > > > > > > What SQL statement do you use to identify tables that need > > > reorganization? > > > > > > How do you identify tables that are used in full table scans? > > How often > > > do you run this query? > > > > > > Thanks, > > > Jolene > > > > > > > > > Chris Dunscombe > > > > [EMAIL PROTECTED] > > > > - > > Everyone should have http://www.freedom2surf.net/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 8
RE: table reorganizations
Hi Chris > Richard, > > I agree there are a number of reasons for reorganising > tables. LMTs remove the > need to reorganise a tablespace but not to reorganise a > table. Two further real- ilfe examples of table reorgs: > > 1) The purge programs have at last been written and run > deleting data > 2 years > old. The system's been running for 4 years. So in simple > terms most of the > tables are approx 50% empty. You need to reorg in this case. What would be the rationale for this? If the rationale is performance how much faster does it make everything, and how many users are complaining to start with? If purely for space management purposes then I'd ask why the deleted space could not be reused? > 2) A "transaction log" table is inserted to throughout the > day and most of the > night. A clear down processing job runs at the end of the day > and deletes all > the rows its processed, but more rows are being added. So the > table is now < 1% > full. Not good for FTS. So instead of a conventional reorg we > implemented a > nightly "table-swap". This meant locking the source table, > copying it's > contents to a replica empty single extent table, target > table. The names of the > target and source tables are swapped, hence "table-swap". The > new source table > is now available to the application and the original source > is truncated and > ready to be the target in 24 hrs time. Pah. Once a day indeed. Once every 5 minutes... I'm interested in why you implemented "table swap" instead of 'alter table move' which is what we did. > > Cheers, > > Chris Dunscombe Cheers Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: Re: table reorganizations
Another way would be to do exchange partition between a single- or multipartition partitioned table and a regular table. Easier than dbms_redefinition and less locking issues than with manual lock & swap. Tanel. --- Saatja: Rachel Carmichael <[EMAIL PROTECTED]> Kuupäev: 09.01.2004 16:14:33 --- > yes a few seconds. that's why I said "without locking for any length > of > time" and not "doesn't lock at all" :) > > The point being that you can do the redef and still allow access to > the > main table. Do a sync every once in a while while there is high volume > traffic, then do the finish when there is low volume > > if you manually lock the table, then do the copy, it can take > significantly more time > > > --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > > I thought that finish_redef_table does lock table for a few seconds? > > On 01/09/2004 07:54:26 AM, Rachel Carmichael wrote: > > > Chris, > > > > > > Have you considered using dbms_redefinition for your second case? > > That > > > would allow you to reorg and swap the tables without locking for > > any > > > length of time. > > > > > > Rachel > > > > > > > > > --- [EMAIL PROTECTED] wrote: > > > > Richard, > > > > > > > > I agree there are a number of reasons for reorganising tables. > > LMTs > > > > remove the > > > > need to reorganise a tablespace but not to reorganise a table. > > Two > > > > further real- > > > > ilfe examples of table reorgs: > > > > > > > > 1) The purge programs have at last been written and run deleting > > data > > > > > 2 years > > > > old. The system's been running for 4 years. So in simple terms > > most > > > > of the > > > > tables are approx 50% empty. You need to reorg in this case. > > > > > > > > 2) A "transaction log" table is inserted to throughout the day > > and > > > > most of the > > > > night. A clear down processing job runs at the end of the day > and > > > > deletes all > > > > the rows its processed, but more rows are being added. So the > > table > > > > is now < 1% > > > > full. Not good for FTS. So instead of a conventional reorg we > > > > implemented a > > > > nightly "table-swap". This meant locking the source table, > > copying > > > > it's > > > > contents to a replica empty single extent table, target table. > > The > > > > names of the > > > > target and source tables are swapped, hence "table-swap". The > new > > > > source table > > > > is now available to the application and the original source is > > > > truncated and > > > > ready to be the target in 24 hrs time. > > > > > > > > Cheers, > > > > > > > > Chris Dunscombe > > > > > > > > > > > > > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > > > > > > > MessageHi Thomas, > > > > > > > > > > Never say never (oh bugger, I've just gone and done it > myself). > > > > > > > > > > A large table accessed via a FTS for various important > > reporting > > > > requirements > > > > > has permanently shrunk in size from 10G to 100M (say list of > > > > Informix > > > > > customers ;) > > > > > > > > > > Business requirements have changed and you need to add some > > columns > > > > to a > > > > > table resulting in mucho row migration. > > > > > > > > > > You were told (incorrectly) that rows would grow significantly > > > > after loading > > > > > (honestly) but now the 80 pctfree value you've set is causing > > > > problems for > > > > > other really important reports. > > > > > > > > > > There are of course other cases but you get my point ;) > > > > > > > > > > Cheers > > > > > > > > > > Richard > > > > > - Original Message - > > > > > From: Mercadante, Thomas F > > > > > To: Multiple recipients of list ORACLE-L > > > > > Sent: Thursday,
Re: table reorganizations
yes a few seconds. that's why I said "without locking for any length of time" and not "doesn't lock at all" :) The point being that you can do the redef and still allow access to the main table. Do a sync every once in a while while there is high volume traffic, then do the finish when there is low volume if you manually lock the table, then do the copy, it can take significantly more time --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > I thought that finish_redef_table does lock table for a few seconds? > On 01/09/2004 07:54:26 AM, Rachel Carmichael wrote: > > Chris, > > > > Have you considered using dbms_redefinition for your second case? > That > > would allow you to reorg and swap the tables without locking for > any > > length of time. > > > > Rachel > > > > > > --- [EMAIL PROTECTED] wrote: > > > Richard, > > > > > > I agree there are a number of reasons for reorganising tables. > LMTs > > > remove the > > > need to reorganise a tablespace but not to reorganise a table. > Two > > > further real- > > > ilfe examples of table reorgs: > > > > > > 1) The purge programs have at last been written and run deleting > data > > > > 2 years > > > old. The system's been running for 4 years. So in simple terms > most > > > of the > > > tables are approx 50% empty. You need to reorg in this case. > > > > > > 2) A "transaction log" table is inserted to throughout the day > and > > > most of the > > > night. A clear down processing job runs at the end of the day and > > > deletes all > > > the rows its processed, but more rows are being added. So the > table > > > is now < 1% > > > full. Not good for FTS. So instead of a conventional reorg we > > > implemented a > > > nightly "table-swap". This meant locking the source table, > copying > > > it's > > > contents to a replica empty single extent table, target table. > The > > > names of the > > > target and source tables are swapped, hence "table-swap". The new > > > source table > > > is now available to the application and the original source is > > > truncated and > > > ready to be the target in 24 hrs time. > > > > > > Cheers, > > > > > > Chris Dunscombe > > > > > > > > > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > > > > > MessageHi Thomas, > > > > > > > > Never say never (oh bugger, I've just gone and done it myself). > > > > > > > > A large table accessed via a FTS for various important > reporting > > > requirements > > > > has permanently shrunk in size from 10G to 100M (say list of > > > Informix > > > > customers ;) > > > > > > > > Business requirements have changed and you need to add some > columns > > > to a > > > > table resulting in mucho row migration. > > > > > > > > You were told (incorrectly) that rows would grow significantly > > > after loading > > > > (honestly) but now the 80 pctfree value you've set is causing > > > problems for > > > > other really important reports. > > > > > > > > There are of course other cases but you get my point ;) > > > > > > > > Cheers > > > > > > > > Richard > > > > - Original Message - > > > > From: Mercadante, Thomas F > > > > To: Multiple recipients of list ORACLE-L > > > > Sent: Thursday, January 08, 2004 6:34 AM > > > > Subject: RE: table reorganizations > > > > > > > > > > > > Jolene, > > > > > > > > Tables should never *need* to be reorganized. This is an old > > > falacy. If > > > > you know how big a table is going to grow, say in a year, then > > > place it in a > > > > Locally Managed tablespace with extent sizes to hold enough > data > > > for one year > > > > (say 1M). > > > > > > > > You should never have to reorganize a table. > > > > > > > > Tom Mercadante > > > > Oracle Certified Professional > > > > -Original Message- > > > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > > > Sent: W
Re: table reorganizations
I thought that finish_redef_table does lock table for a few seconds? On 01/09/2004 07:54:26 AM, Rachel Carmichael wrote: > Chris, > > Have you considered using dbms_redefinition for your second case? That > would allow you to reorg and swap the tables without locking for any > length of time. > > Rachel > > > --- [EMAIL PROTECTED] wrote: > > Richard, > > > > I agree there are a number of reasons for reorganising tables. LMTs > > remove the > > need to reorganise a tablespace but not to reorganise a table. Two > > further real- > > ilfe examples of table reorgs: > > > > 1) The purge programs have at last been written and run deleting data > > > 2 years > > old. The system's been running for 4 years. So in simple terms most > > of the > > tables are approx 50% empty. You need to reorg in this case. > > > > 2) A "transaction log" table is inserted to throughout the day and > > most of the > > night. A clear down processing job runs at the end of the day and > > deletes all > > the rows its processed, but more rows are being added. So the table > > is now < 1% > > full. Not good for FTS. So instead of a conventional reorg we > > implemented a > > nightly "table-swap". This meant locking the source table, copying > > it's > > contents to a replica empty single extent table, target table. The > > names of the > > target and source tables are swapped, hence "table-swap". The new > > source table > > is now available to the application and the original source is > > truncated and > > ready to be the target in 24 hrs time. > > > > Cheers, > > > > Chris Dunscombe > > > > > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > > > MessageHi Thomas, > > > > > > Never say never (oh bugger, I've just gone and done it myself). > > > > > > A large table accessed via a FTS for various important reporting > > requirements > > > has permanently shrunk in size from 10G to 100M (say list of > > Informix > > > customers ;) > > > > > > Business requirements have changed and you need to add some columns > > to a > > > table resulting in mucho row migration. > > > > > > You were told (incorrectly) that rows would grow significantly > > after loading > > > (honestly) but now the 80 pctfree value you've set is causing > > problems for > > > other really important reports. > > > > > > There are of course other cases but you get my point ;) > > > > > > Cheers > > > > > > Richard > > > - Original Message - > > > From: Mercadante, Thomas F > > > To: Multiple recipients of list ORACLE-L > > > Sent: Thursday, January 08, 2004 6:34 AM > > > Subject: RE: table reorganizations > > > > > > > > > Jolene, > > > > > > Tables should never *need* to be reorganized. This is an old > > falacy. If > > > you know how big a table is going to grow, say in a year, then > > place it in a > > > Locally Managed tablespace with extent sizes to hold enough data > > for one year > > > (say 1M). > > > > > > You should never have to reorganize a table. > > > > > > Tom Mercadante > > > Oracle Certified Professional > > > -Original Message- > > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, January 07, 2004 2:39 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: table reorganizations > > > > > > > > > What SQL statement do you use to identify tables that need > > > reorganization? > > > > > > How do you identify tables that are used in full table scans? > > How often > > > do you run this query? > > > > > > Thanks, > > > Jolene > > > > > > > > > Chris Dunscombe > > > > [EMAIL PROTECTED] > > > > - > > Everyone should have http://www.freedom2surf.net/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and w
Re: table reorganizations
Chris, Have you considered using dbms_redefinition for your second case? That would allow you to reorg and swap the tables without locking for any length of time. Rachel --- [EMAIL PROTECTED] wrote: > Richard, > > I agree there are a number of reasons for reorganising tables. LMTs > remove the > need to reorganise a tablespace but not to reorganise a table. Two > further real- > ilfe examples of table reorgs: > > 1) The purge programs have at last been written and run deleting data > > 2 years > old. The system's been running for 4 years. So in simple terms most > of the > tables are approx 50% empty. You need to reorg in this case. > > 2) A "transaction log" table is inserted to throughout the day and > most of the > night. A clear down processing job runs at the end of the day and > deletes all > the rows its processed, but more rows are being added. So the table > is now < 1% > full. Not good for FTS. So instead of a conventional reorg we > implemented a > nightly "table-swap". This meant locking the source table, copying > it's > contents to a replica empty single extent table, target table. The > names of the > target and source tables are swapped, hence "table-swap". The new > source table > is now available to the application and the original source is > truncated and > ready to be the target in 24 hrs time. > > Cheers, > > Chris Dunscombe > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > MessageHi Thomas, > > > > Never say never (oh bugger, I've just gone and done it myself). > > > > A large table accessed via a FTS for various important reporting > requirements > > has permanently shrunk in size from 10G to 100M (say list of > Informix > > customers ;) > > > > Business requirements have changed and you need to add some columns > to a > > table resulting in mucho row migration. > > > > You were told (incorrectly) that rows would grow significantly > after loading > > (honestly) but now the 80 pctfree value you've set is causing > problems for > > other really important reports. > > > > There are of course other cases but you get my point ;) > > > > Cheers > > > > Richard > > - Original Message - > > From: Mercadante, Thomas F > > To: Multiple recipients of list ORACLE-L > > Sent: Thursday, January 08, 2004 6:34 AM > > Subject: RE: table reorganizations > > > > > > Jolene, > > > > Tables should never *need* to be reorganized. This is an old > falacy. If > > you know how big a table is going to grow, say in a year, then > place it in a > > Locally Managed tablespace with extent sizes to hold enough data > for one year > > (say 1M). > > > > You should never have to reorganize a table. > > > > Tom Mercadante > > Oracle Certified Professional > > -Original Message- > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, January 07, 2004 2:39 PM > > To: Multiple recipients of list ORACLE-L > > Subject: table reorganizations > > > > > > What SQL statement do you use to identify tables that need > > reorganization? > > > > How do you identify tables that are used in full table scans? > How often > > do you run this query? > > > > Thanks, > > Jolene > > > > > Chris Dunscombe > > [EMAIL PROTECTED] > > - > Everyone should have http://www.freedom2surf.net/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > 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: 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: table reorganizations
Chris, I would argue that in your two examples, nothing needs to be done if you are using Locally Managed Tablespaces. All of the free space that your deletes generated would be reused by new inserts. When you say "not good for FTS", I think you are wrong. Have you tried testing this statement? How much "slower" is it? Of course, I am talking about using Oracle 9i. Prior versions behaved much differently. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 09, 2004 6:09 AM To: Multiple recipients of list ORACLE-L Richard, I agree there are a number of reasons for reorganising tables. LMTs remove the need to reorganise a tablespace but not to reorganise a table. Two further real- ilfe examples of table reorgs: 1) The purge programs have at last been written and run deleting data > 2 years old. The system's been running for 4 years. So in simple terms most of the tables are approx 50% empty. You need to reorg in this case. 2) A "transaction log" table is inserted to throughout the day and most of the night. A clear down processing job runs at the end of the day and deletes all the rows its processed, but more rows are being added. So the table is now < 1% full. Not good for FTS. So instead of a conventional reorg we implemented a nightly "table-swap". This meant locking the source table, copying it's contents to a replica empty single extent table, target table. The names of the target and source tables are swapped, hence "table-swap". The new source table is now available to the application and the original source is truncated and ready to be the target in 24 hrs time. Cheers, Chris Dunscombe Quoting Richard Foote <[EMAIL PROTECTED]>: > MessageHi Thomas, > > Never say never (oh bugger, I've just gone and done it myself). > > A large table accessed via a FTS for various important reporting requirements > has permanently shrunk in size from 10G to 100M (say list of Informix > customers ;) > > Business requirements have changed and you need to add some columns to a > table resulting in mucho row migration. > > You were told (incorrectly) that rows would grow significantly after loading > (honestly) but now the 80 pctfree value you've set is causing problems for > other really important reports. > > There are of course other cases but you get my point ;) > > Cheers > > Richard > - Original Message ----- > From: Mercadante, Thomas F > To: Multiple recipients of list ORACLE-L > Sent: Thursday, January 08, 2004 6:34 AM > Subject: RE: table reorganizations > > > Jolene, > > Tables should never *need* to be reorganized. This is an old falacy. If > you know how big a table is going to grow, say in a year, then place it in a > Locally Managed tablespace with extent sizes to hold enough data for one year > (say 1M). > > You should never have to reorganize a table. > > Tom Mercadante > Oracle Certified Professional > -Original Message- > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 07, 2004 2:39 PM > To: Multiple recipients of list ORACLE-L > Subject: table reorganizations > > > What SQL statement do you use to identify tables that need > reorganization? > > How do you identify tables that are used in full table scans? How often > do you run this query? > > Thanks, > Jolene > Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Mercadante, Thomas F 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: table reorganizations
Richard, I agree there are a number of reasons for reorganising tables. LMTs remove the need to reorganise a tablespace but not to reorganise a table. Two further real- ilfe examples of table reorgs: 1) The purge programs have at last been written and run deleting data > 2 years old. The system's been running for 4 years. So in simple terms most of the tables are approx 50% empty. You need to reorg in this case. 2) A "transaction log" table is inserted to throughout the day and most of the night. A clear down processing job runs at the end of the day and deletes all the rows its processed, but more rows are being added. So the table is now < 1% full. Not good for FTS. So instead of a conventional reorg we implemented a nightly "table-swap". This meant locking the source table, copying it's contents to a replica empty single extent table, target table. The names of the target and source tables are swapped, hence "table-swap". The new source table is now available to the application and the original source is truncated and ready to be the target in 24 hrs time. Cheers, Chris Dunscombe Quoting Richard Foote <[EMAIL PROTECTED]>: > MessageHi Thomas, > > Never say never (oh bugger, I've just gone and done it myself). > > A large table accessed via a FTS for various important reporting requirements > has permanently shrunk in size from 10G to 100M (say list of Informix > customers ;) > > Business requirements have changed and you need to add some columns to a > table resulting in mucho row migration. > > You were told (incorrectly) that rows would grow significantly after loading > (honestly) but now the 80 pctfree value you've set is causing problems for > other really important reports. > > There are of course other cases but you get my point ;) > > Cheers > > Richard > - Original Message - > From: Mercadante, Thomas F > To: Multiple recipients of list ORACLE-L > Sent: Thursday, January 08, 2004 6:34 AM > Subject: RE: table reorganizations > > > Jolene, > > Tables should never *need* to be reorganized. This is an old falacy. If > you know how big a table is going to grow, say in a year, then place it in a > Locally Managed tablespace with extent sizes to hold enough data for one year > (say 1M). > > You should never have to reorganize a table. > > Tom Mercadante > Oracle Certified Professional > -Original Message- > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 07, 2004 2:39 PM > To: Multiple recipients of list ORACLE-L > Subject: table reorganizations > > > What SQL statement do you use to identify tables that need > reorganization? > > How do you identify tables that are used in full table scans? How often > do you run this query? > > Thanks, > Jolene > Chris Dunscombe [EMAIL PROTECTED] - Everyone should have http://www.freedom2surf.net/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: table reorganizations - simple/complex
Nelson, I think you're right on both accounts. About how a fragmented table would show up in 10046 data: there are lots of people on the list. Maybe one has the time to try it. If it takes more time, it'll show up in response time *somehow*. If not, then it's simply not a problem for the case being traced. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Comments in-line. -Original Message- Sent: Wednesday, January 07, 2004 10:59 PM To: Multiple recipients of list ORACLE-L >There are a lot of tuning "authorities" that make hard >and fast rules about how to find problem areas by simply >running a few queries. > >It is unfortunately, not that simple. Or for people like >Cary Millsap, Gary Goodman, Steve Adams, Jonathan Lewis >and a number of others, it is, fortunately for them, not >that simple. ;) > > I think Cary and Gary (plus many others) would argue that the hard-and-fast rules accompanied by long, multiple checklists of parameter settings, v$ queries, etc. are what is truly "not simple." The whole point of Method-R (the Millsap/Goodman/Holt tuning method) is to de-mystify and simplify tuning. Eliminate the guess-work. Precisely identify the performance issue and nail it. Everyone who is willing to put in some sweat equity can use Method-R. I'm not sure how a badly fragmented table would show up in a 10046 trace file. If a table reorganization is really required, I think a trace would show excess physical io. Nelson Petersen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Cary Millsap 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: table reorganizations - simple/complex
Comments in-line. -Original Message- Sent: Wednesday, January 07, 2004 10:59 PM To: Multiple recipients of list ORACLE-L >There are a lot of tuning "authorities" that make hard >and fast rules about how to find problem areas by simply >running a few queries. > >It is unfortunately, not that simple. Or for people like >Cary Millsap, Gary Goodman, Steve Adams, Jonathan Lewis >and a number of others, it is, fortunately for them, not >that simple. ;) > > I think Cary and Gary (plus many others) would argue that the hard-and-fast rules accompanied by long, multiple checklists of parameter settings, v$ queries, etc. are what is truly "not simple." The whole point of Method-R (the Millsap/Goodman/Holt tuning method) is to de-mystify and simplify tuning. Eliminate the guess-work. Precisely identify the performance issue and nail it. Everyone who is willing to put in some sweat equity can use Method-R. I'm not sure how a badly fragmented table would show up in a 10046 trace file. If a table reorganization is really required, I think a trace would show excess physical io. Nelson Petersen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: table reorganizations
Title: Message Hi Thomas, Never say never (oh bugger, I've just gone and done it myself). A large table accessed via a FTS for various important reporting requirements has permanently shrunk in size from 10G to 100M (say list of Informix customers ;) Business requirements have changed and you need to add some columns to a table resulting in mucho row migration. You were told (incorrectly) that rows would grow significantly after loading (honestly) but now the 80 pctfree value you've set is causing problems for other really important reports. There are of course other cases but you get my point ;) Cheers Richard - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Thursday, January 08, 2004 6:34 AM Subject: RE: table reorganizations Jolene, Tables should never *need* to be reorganized. This is an old falacy. If you know how big a table is going to grow, say in a year, then place it in a Locally Managed tablespace with extent sizes to hold enough data for one year (say 1M). You should never have to reorganize a table. Tom Mercadante Oracle Certified Professional -Original Message-From: Shrake, Jolene [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004 2:39 PMTo: Multiple recipients of list ORACLE-LSubject: table reorganizations What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene
Re: table reorganizations
> I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) blkdiff > From dba_tables > Where blkdiff > 100; > > To determine reorganization need. > Hi Jolene You already received a number of replies why there are issues with using a general formula as above. IF a table is commonly accessed via a FTS AND, IF sufficient deletes without subsequent re-inserts (permanent table shrinkage, ouch, it's a male thing ;) or IF you've set a shocking PCTUSED which prevents inserts reclaiming deleted space, or IF you've set a shocking PCTFREE with no subsequent row size increase (etc) AND FTS access performance causes notable performance issues, you might have a case for a table re-org. The above conditions are not particularly common (perhaps a table containing future bookings for sleepovers at Michael Jackson's place ? ;) but if they do, consider the clustering factor of your most significant index access while you're at it, assuming there is one. The point I'll like to make are a couple of issues with your formula above. Firstly, it doesn't consider general block overhead details which means for largish tables with a sum of 100 block or more of overhead, the (rather expensive) re-org would achieve nothing. Secondly, it doesn't consider blocks above the HWM which could quite easily exceed the 100 mark depending on extent size. Again the re-org would result in a somewhat disappointing outcome. The formula above will potentially call for the re-org of *all* your larger tables for absolutely no benefit. Glad you asked the question Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: table reorganizations
it just shattered BTW: Hippo Birdie! Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - > soul, you'll see that I'm a real sweetheart. Humble and modest, too. Mirror, mirror > on the wall, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: table reorganizations
On 2004.01.07 22:59, Jared Still wrote: > > I'm surprised at these responses. > > Please don't take offense. It appears that you have > received some very informative answers in addition to > the facetious ones. ( which I *did* expect. Mladen > never lets me down. ) I'm trying to find the right measure. I even believe that my answer was informative in addition to being facetious. Being facetious is, however, the only way known to humanity and me in particular to explain someone that she or he is dealing with misconceptions, superstitions and myths. People are very reluctant to accept that they've been had unless it hits them right on the kisser. I am noble and generous enough to provide the information and the punch at no extra cost. When you look deeper into my soul, you'll see that I'm a real sweetheart. Humble and modest, too. Mirror, mirror on the wall, -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: table reorganizations
> I'm surprised at these responses. Please don't take offense. It appears that you have received some very informative answers in addition to the facetious ones. ( which I *did* expect. Mladen never lets me down. ) To reiterate the point of responses to your question: Running a SQL query to indicate that a table should be reorged to based upon the amount of free space it finds simply doesn't supply enough data to indicate that it's actually necessary. It probably isn't. There are a lot of tuning "authorities" that make hard and fast rules about how to find problem areas by simply running a few queries. It is unfortunately, not that simple. Or for people like Cary Millsap, Gary Goodman, Steve Adams, Jonathan Lewis and a number of others, it is, fortunately for them, not that simple. ;) Jared On Wed, 2004-01-07 at 12:49, Shrake, Jolene wrote: > I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) blkdiff > >From dba_tables > Where blkdiff > 100; > > To determine reorganization need. > > What sql statement is used by others? > > Jolene > > -Original Message- > Sent: Wednesday, January 07, 2004 2:25 PM > To: Multiple recipients of list ORACLE-L > > > I usually recommend Gospel by Jonathan for its completeness > and a wide range of subjects. The book you mentioned is great > for beginner as well. As for the number 42, I'll continue using > it until this Saturday (1/10/2004) when it will become 43. Inflation is > not as big as you think. > PS: > --- > I was born on 1/10/1961, and that makes January 10th so special. I don't > have to work on that great day, mostly because it's Saturday. > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > Lemme guess: you just started on your new job as a DBA? You are > > > another person to which can only wholeheartedly recommend Jonathan's > > > > book. As for your questions, the answer is "42". > > > > actually, if she's just starting out, i'd recommend Marlene, Rachel > > and Jim's book first, then Jonathan's. > > > > and are you sure it's not "57" now due to inflation? > > > > -- > > Bill "Shrek" Thater ORACLE DBA > > "I'm going to work my ticket if I can..." -- Gilwell song > > [EMAIL PROTECTED] > > -- > > -- > > Perfection of means and confusion of ends seem to characterize our > age. - > > Albert Einstein > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Thater, William > > 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). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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: Shrake, Jolene > 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: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 ht
Re: RE: table reorganizations
> Michael Thomas <[EMAIL PROTECTED]> wrote: > Guys, in this case can we chill (in a friendly way) > about the naive questions. At least her company has a > job posting for Senior Oracle DBA. E.g.: Chill? Don't recall toasting... Yeah, looks like the con-sultant might have caused enough trouble. > Maybe they really do need help? Sorry, their web site > is really slow, too. ;-) > I'd settle for finding out what versions of the software and what sort of environment (3rd-party, OLTP, Peopleslop or SLAP) they run. Then any comments on how to address the problem (if there is one) may become relevant. ASSM is not - yet - available in 7 or 8, for example... Although I'm sure there is probably some obscure event at level 99 known only to the original coder and Anjo (recursive?) to achieve similar results. If the moon is rising and the wind is blowing on the right side. There, enough chill? :D Cheers Nuno Souto -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto 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: table reorganizations
Using iSQL*Plus, I like to run 'select * from dba_objects' as my 'TPC for dummies' test in the lab. Hehehe. In isolation (LAN/DB), the query completes in just over 45 seconds, weighing in at a 250MB RAM IE session on W2K client. Agreed its not probably the type of thing to do in production, nor at work on the DB or LAN. Regards, Mike Thomas --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > select * > from dba_objects > where object_id in (select obj# from sys.obj$ sample > (5)) > / > > ps: this is not a serious answer ... don't try this > on your production system. > 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 ! > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > INET: [EMAIL PROTECTED] > __ 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: Michael Thomas 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: table reorganizations
Title: Message Jolene, Tables should never *need* to be reorganized. This is an old falacy. If you know how big a table is going to grow, say in a year, then place it in a Locally Managed tablespace with extent sizes to hold enough data for one year (say 1M). You should never have to reorganize a table. Tom Mercadante Oracle Certified Professional -Original Message-From: Shrake, Jolene [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004 2:39 PMTo: Multiple recipients of list ORACLE-LSubject: table reorganizations What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene
RE: table reorganizations
select * from dba_objects where object_id in (select obj# from sys.obj$ sample (5)) / ps: this is not a serious answer ... don't try this on your production system. 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 ! -- 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: RE: table reorganizations
Hi, Guys, in this case can we chill (in a friendly way) about the naive questions. At least her company has a job posting for Senior Oracle DBA. E.g.: http://pella.kenexa.com/pellav4/newhr/jobdesc.asp?ID=1194 Maybe they really do need help? Sorry, their web site is really slow, too. ;-) HTH. Regards, Mike Thomas --- Nuno Pinto do Souto <[EMAIL PROTECTED]> wrote: > > Shrake, Jolene <[EMAIL PROTECTED]> wrote: > > > > I'm surprised at these responses. I'm asking what > sql statement most > > people use to identify tables that need > reorganization because of > > "holes". > > You don't have to be. There is plenty of material > available today online > that demonstrates the futility of "religious" > (meaning: dictated by dogma) > reorganizations. > > > We had an Oracle consultant here and he uses > > A con-sultant, perheaps? > > > What sql statement is used by others? > > None. There is no such thing as a statement that > will pinpoint > a need for a reorg. Unless you can prove to > yourself that reorganizing > any given table will improve anything in your > system, then just going > through the motions because there is a "hole" is > useless. > "Holes" are not necessarily the same as "disasters". > > If you are using a reasonably recent version of > Oracle (something you > should ALWAYS mention upfront is the version you are > running), > then you can get all the information you need from > the data in > the dictionary as well as the statistics info. > > And guess what: that is also all the information > Oracle itself needs and uses > to work around any potential problems "holes" might > have caused > in the past. > > Bottom line: only reorganize if you have a specific > reason, purpose and > target for doing so. Just doing it because a > con-sultant's SQL statement > says so, is turning yourself into a cookie-cutter > DBA. > Which may give your boss a very warm feeling about > your position, > but achieves preciously nothing in real terms. > > Cheers > Nuno Souto > [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Nuno Pinto do Souto > 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: Michael Thomas 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: table reorganizations
Yep, I agree that reorganizing table in order to get it more compact is pointless if you have more data coming in all the time anyway (most of big tables tend to grow). But there is one (not very likely) case where rebuild might help to reuse "hidden" space - it's with freelist managed tables where you usually have quite small rows, but have inserted a bunch of very large rows for some reason - when a block is let say 50% full, PCTUSED is 40 and you try to insert a row, which *would* fill this block over PCTFREE limit, then this block is unlinked from free list and insert is attempted to next block, causing the space from previous block being lost even for small rows (how many unsuitable blocks are unlinked in that way, depends on few hidden parameters and number of configured freelists for the segment). But in practice, I haven't faced such kind of problem yet, but in tables with greatly varying row sizes, it can cause inefficient space usage problems. ASSM relieves this problem greatly, because it knows in general level how full blocks are and doesn't even try to insert a large row to a block with low free space. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 11:19 PM > I guess some of the folks on the list are in a "playful" > mood today... > > The need for a table "reorganization" depends on how it is > used. > > The query you cite might be illuminating if the table in > question is mostly accessed by "full table scans", as it > seems to identify tables with large "gaps" due to deletions. > During an FTS, these gaps would still be "traversed", > resulting in what might turn out to be excessive I/O > required to accomplish the task. > > However, if the table in question is commonly accessed via > indexed lookups or scans, then reorganizing these tables to > "close these gaps" might well be a complete waste of time. > The reason being that the "table access by ROWID" action > that is the last step of table access via indexes does not > scan emptied blocks. It directly addresses populated blocks > in the table only. Thus, accessing rows in a table that is > 99.99% empty takes no longer than accessing rows in a table > that is 100% full, using this access method. Of course, if > someone wants to throw clustering factor in, then that > assertion starts to get a little squishy, but the fact > remains that the effort expended in reorging the table > clearly does not provide anything near an adequate "return > on investment". > > So, the knowledge of how the table is accessed is clearly > part of the answer. This puts the equation beyond the scope > of a simple query on the data dictionary, although I'm > pretty sure that the V$SEGMENT_STATISTICS view in Oracle9i > could provide some of the insight into the usage of the > table. > > So, if the table in question is typically accessed via full > table scan, the query you cited is useful. If the table in > question is rarely (if ever) accessed via full table scan > (or shouldn't be), then the query you cited should at least > be changed to indicate a much much much larger "blkdiff" > constant. Indeed, table reorgs in such circumstances wouuld > help so rarely that they aren't really worth worrying about. > > Hope this helps... > > -Tim > > > > I'm surprised at these responses. I'm asking what sql > > statement most people use to identify tables that need > > reorganization because of "holes". > > > > We had an Oracle consultant here and he uses > > > > Select table_name, > > blocks-((num_rows*avg_row_len/)*(1+(pct_free/1 > > 00))) blkdiff From dba_tables > > Where blkdiff > 100; > > > > To determine reorganization need. > > > > What sql statement is used by others? > > > > Jolene > > > > -Original Message- > > Sent: Wednesday, January 07, 2004 2:25 PM > > To: Multiple recipients of list ORACLE-L > > > > > > I usually recommend Gospel by Jonathan for its > > completeness and a wide range of subjects. The book you > > mentioned is great for beginner as well. As for the > > number 42, I'll continue using it until this Saturday > > (1/10/2004) when it will become 43. Inflation is not as > > big as you think. PS: > > --- > > I was born on 1/10/1961, and that makes January 10th so > > special. I don't have to work on that great day, mostly > > because it's Saturday. > > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > > > Lemme guess: you just started on your new job as a > > > > DBA? You are another person to which can only > > wholeheartedly recommend Jonathan's > > > > book. As for your questions, the answer is "42". > > > > > > actually, if she's just starting out, i'd recommend > > > Marlene, Rachel and Jim's book first, then Jonathan's. > > > > > > and are you sure it's not "57" now due to inflation? > > > > > > -- > > > Bill "Shrek" Thater ORACLE DBA > > > "I'm going t
RE: table reorganizations
ahem. pretty free with the non-existent budget, aren't you? I can probably afford virtual beers, or even the virtual single-malt of your choice :) --- "Loughmiller, Greg" <[EMAIL PROTECTED]> wrote: > beers for all in celebration of the birthday!! Rachel's buying:-) > > greg > > -Original Message- > Sent: Wednesday, January 07, 2004 3:25 PM > To: Multiple recipients of list ORACLE-L > > > I usually recommend Gospel by Jonathan for its completeness > and a wide range of subjects. The book you mentioned is great > for beginner as well. As for the number 42, I'll continue using > it until this Saturday (1/10/2004) when it will become 43. Inflation > is not as big as you think. > PS: > --- > I was born on 1/10/1961, and that makes January 10th so special. > I don't have to work on that great day, mostly because it's Saturday. > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > Lemme guess: you just started on your new job as a DBA? You are > > > another person to which can only wholeheartedly recommend > Jonathan's > > > book. As for your questions, the answer is "42". > > > > actually, if she's just starting out, i'd recommend Marlene, Rachel > and > > Jim's book first, then Jonathan's. > > > > and are you sure it's not "57" now due to inflation? > > > > -- > > Bill "Shrek" Thater ORACLE DBA > > "I'm going to work my ticket if I can..." -- Gilwell song > > [EMAIL PROTECTED] > > > > > Perfection of means and confusion of ends seem to characterize our > age. - > > Albert Einstein > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Thater, William > > 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). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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: 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: table reorganizations
First, with things like ASSM you no longer have to worry about that. Second, why would you want to reorganize tables? Just because some artificially determined number is not what your consultant says it should be? Do you have any chained rows? Did you analyze table for chained rows? What particular problem rears its ugly head if the tables are not re-organized? Just keep your buffer cache hit ratio (BCHR) close to 100 and you're fine (sorry folks, I couldn't resist). The first rule of tuning is "if it ain't broken it doesn't need fixing". Do you have any problems, like application working unacceptably slow or you just want to do the right thing and reorganize some tables? One of the things that my favorite DBA authors (Cary Millsap, Jonathan Lewis, Marlene T., Rachel Carmichael and Gaja V., Chris Lawson and Tom Kyte ) say is that one should tune the applications, not the instance parameters. Tables that have 100 blocks more then some consultant think they should, are not candidates for a reorg. I've had empty tables which have had several gigabytes of allocated space and were completely empty, in preparation of a data load. Then the tapes arrived, load was done in minutes. Your consultant would have shrunk my carefully allocated tables and thus cause load to go on for hours because of the dynamic space management. Can't you do us all a favor and just shoot that consultant? Oh yes, and get yourself a real DBA, or you can always contract hotsos (http://www.hotsos.com). They will tune your system so fast that the sparks will be flying. Ask them to fix your BCHR. On 01/07/2004 03:49:33 PM, "Shrake, Jolene" wrote: > I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) blkdiff > From dba_tables > Where blkdiff > 100; > > To determine reorganization need. > > What sql statement is used by others? > > Jolene > > -Original Message- > Sent: Wednesday, January 07, 2004 2:25 PM > To: Multiple recipients of list ORACLE-L > > > I usually recommend Gospel by Jonathan for its completeness > and a wide range of subjects. The book you mentioned is great > for beginner as well. As for the number 42, I'll continue using > it until this Saturday (1/10/2004) when it will become 43. Inflation is > not as big as you think. > PS: > --- > I was born on 1/10/1961, and that makes January 10th so special. I don't > have to work on that great day, mostly because it's Saturday. > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > Lemme guess: you just started on your new job as a DBA? You are > > > another person to which can only wholeheartedly recommend Jonathan's > > > > book. As for your questions, the answer is "42". > > > > actually, if she's just starting out, i'd recommend Marlene, Rachel > > and Jim's book first, then Jonathan's. > > > > and are you sure it's not "57" now due to inflation? > > > > -- > > Bill "Shrek" Thater ORACLE DBA > > "I'm going to work my ticket if I can..." -- Gilwell song > > [EMAIL PROTECTED] > > -- > > -- > > Perfection of means and confusion of ends seem to characterize our > age. - > > Albert Einstein > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Thater, William > > 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). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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 > -- >
Re: RE: table reorganizations
> Shrake, Jolene <[EMAIL PROTECTED]> wrote: > > I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". You don't have to be. There is plenty of material available today online that demonstrates the futility of "religious" (meaning: dictated by dogma) reorganizations. > We had an Oracle consultant here and he uses A con-sultant, perheaps? > What sql statement is used by others? None. There is no such thing as a statement that will pinpoint a need for a reorg. Unless you can prove to yourself that reorganizing any given table will improve anything in your system, then just going through the motions because there is a "hole" is useless. "Holes" are not necessarily the same as "disasters". If you are using a reasonably recent version of Oracle (something you should ALWAYS mention upfront is the version you are running), then you can get all the information you need from the data in the dictionary as well as the statistics info. And guess what: that is also all the information Oracle itself needs and uses to work around any potential problems "holes" might have caused in the past. Bottom line: only reorganize if you have a specific reason, purpose and target for doing so. Just doing it because a con-sultant's SQL statement says so, is turning yourself into a cookie-cutter DBA. Which may give your boss a very warm feeling about your position, but achieves preciously nothing in real terms. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto 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: table reorganizations
Or in yet other words, is it worth spending two or three days (preparation + actual reorg - preferably on a sunday morning between 2 and 4am) on an inherently risky operation to shave 0.01% off response times ? Nobody will notice, or hardly. There is certainly much more to be gained checking queries which are run. Now, if you can identify with certainty that a critical query would significantly benefit from a reorg, do it. HTH, SF Rachel Carmichael wrote: > > The point of these questions is... why do you think you have to > reorganize the tables? > > Define a "hole". How does it get created? Is it ever filled in (as in, > do you ever insert rows)? Do you ever delete or update? > > Figure out WHY you want to do something before you try to solve it. > > or, in the same vein as the prior posts "if it ain't broke, don't fix > it" > > --- "Shrake, Jolene" <[EMAIL PROTECTED]> wrote: > > I'm surprised at these responses. I'm asking what sql statement most > > people use to identify tables that need reorganization because of > > "holes". > > > > We had an Oracle consultant here and he uses > > > > Select table_name, > > blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) > > blkdiff > > From dba_tables > > Where blkdiff > 100; > > > > To determine reorganization need. > > > > What sql statement is used by others? > > > > Jolene > > > > -Original Message- > > Sent: Wednesday, January 07, 2004 2:25 PM > > To: Multiple recipients of list ORACLE-L > > > > > > I usually recommend Gospel by Jonathan for its completeness > > and a wide range of subjects. The book you mentioned is great > > for beginner as well. As for the number 42, I'll continue using > > it until this Saturday (1/10/2004) when it will become 43. Inflation > > is > > not as big as you think. > > PS: > > --- > > I was born on 1/10/1961, and that makes January 10th so special. I > > don't > > have to work on that great day, mostly because it's Saturday. > > > > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > > > Lemme guess: you just started on your new job as a DBA? You are > > > > another person to which can only wholeheartedly recommend > > Jonathan's > > > > > > book. As for your questions, the answer is "42". > > > > > > actually, if she's just starting out, i'd recommend Marlene, Rachel > > > > > and Jim's book first, then Jonathan's. > > > > > > and are you sure it's not "57" now due to inflation? > > > > > > -- > > > Bill "Shrek" Thater ORACLE DBA > > > "I'm going to work my ticket if I can..." -- Gilwell song > > > [EMAIL PROTECTED] > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: table reorganizations
I guess some of the folks on the list are in a "playful" mood today... The need for a table "reorganization" depends on how it is used. The query you cite might be illuminating if the table in question is mostly accessed by "full table scans", as it seems to identify tables with large "gaps" due to deletions. During an FTS, these gaps would still be "traversed", resulting in what might turn out to be excessive I/O required to accomplish the task. However, if the table in question is commonly accessed via indexed lookups or scans, then reorganizing these tables to "close these gaps" might well be a complete waste of time. The reason being that the "table access by ROWID" action that is the last step of table access via indexes does not scan emptied blocks. It directly addresses populated blocks in the table only. Thus, accessing rows in a table that is 99.99% empty takes no longer than accessing rows in a table that is 100% full, using this access method. Of course, if someone wants to throw clustering factor in, then that assertion starts to get a little squishy, but the fact remains that the effort expended in reorging the table clearly does not provide anything near an adequate "return on investment". So, the knowledge of how the table is accessed is clearly part of the answer. This puts the equation beyond the scope of a simple query on the data dictionary, although I'm pretty sure that the V$SEGMENT_STATISTICS view in Oracle9i could provide some of the insight into the usage of the table. So, if the table in question is typically accessed via full table scan, the query you cited is useful. If the table in question is rarely (if ever) accessed via full table scan (or shouldn't be), then the query you cited should at least be changed to indicate a much much much larger "blkdiff" constant. Indeed, table reorgs in such circumstances wouuld help so rarely that they aren't really worth worrying about. Hope this helps... -Tim > I'm surprised at these responses. I'm asking what sql > statement most people use to identify tables that need > reorganization because of "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/)*(1+(pct_free/1 > 00))) blkdiff From dba_tables > Where blkdiff > 100; > > To determine reorganization need. > > What sql statement is used by others? > > Jolene > > -Original Message- > Sent: Wednesday, January 07, 2004 2:25 PM > To: Multiple recipients of list ORACLE-L > > > I usually recommend Gospel by Jonathan for its > completeness and a wide range of subjects. The book you > mentioned is great for beginner as well. As for the > number 42, I'll continue using it until this Saturday > (1/10/2004) when it will become 43. Inflation is not as > big as you think. PS: > --- > I was born on 1/10/1961, and that makes January 10th so > special. I don't have to work on that great day, mostly > because it's Saturday. > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > Lemme guess: you just started on your new job as a > > > DBA? You are another person to which can only > wholeheartedly recommend Jonathan's > > > book. As for your questions, the answer is "42". > > > > actually, if she's just starting out, i'd recommend > > Marlene, Rachel and Jim's book first, then Jonathan's. > > > > and are you sure it's not "57" now due to inflation? > > > > -- > > Bill "Shrek" Thater ORACLE DBA > > "I'm going to work my ticket if I can..." -- Gilwell > > song [EMAIL PROTECTED] > > > -- > > -- > > Perfection of means and confusion of ends seem to > characterize our age. - > > Albert Einstein > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net -- > > Author: Thater, William > > 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). > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: Mladen Gogala > 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
RE: table reorganizations
The point of these questions is... why do you think you have to reorganize the tables? Define a "hole". How does it get created? Is it ever filled in (as in, do you ever insert rows)? Do you ever delete or update? Figure out WHY you want to do something before you try to solve it. or, in the same vein as the prior posts "if it ain't broke, don't fix it" --- "Shrake, Jolene" <[EMAIL PROTECTED]> wrote: > I'm surprised at these responses. I'm asking what sql statement most > people use to identify tables that need reorganization because of > "holes". > > We had an Oracle consultant here and he uses > > Select table_name, > blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) > blkdiff > From dba_tables > Where blkdiff > 100; > > To determine reorganization need. > > What sql statement is used by others? > > Jolene > > -Original Message- > Sent: Wednesday, January 07, 2004 2:25 PM > To: Multiple recipients of list ORACLE-L > > > I usually recommend Gospel by Jonathan for its completeness > and a wide range of subjects. The book you mentioned is great > for beginner as well. As for the number 42, I'll continue using > it until this Saturday (1/10/2004) when it will become 43. Inflation > is > not as big as you think. > PS: > --- > I was born on 1/10/1961, and that makes January 10th so special. I > don't > have to work on that great day, mostly because it's Saturday. > > > On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > > Mladen Gogala scribbled on the wall in glitter crayon: > > > > > Lemme guess: you just started on your new job as a DBA? You are > > > another person to which can only wholeheartedly recommend > Jonathan's > > > > book. As for your questions, the answer is "42". > > > > actually, if she's just starting out, i'd recommend Marlene, Rachel > > > and Jim's book first, then Jonathan's. > > > > and are you sure it's not "57" now due to inflation? > > > > -- > > Bill "Shrek" Thater ORACLE DBA > > "I'm going to work my ticket if I can..." -- Gilwell song > > [EMAIL PROTECTED] > > > -- > > -- > > Perfection of means and confusion of ends seem to characterize our > age. - > > Albert Einstein > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Thater, William > > 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). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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: Shrake, Jolene > 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: 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
RE: table reorganizations
Title: RE: table reorganizations beers for all in celebration of the birthday!! Rachel's buying:-) greg -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 07, 2004 3:25 PM To: Multiple recipients of list ORACLE-L Subject: Re: table reorganizations I usually recommend Gospel by Jonathan for its completeness and a wide range of subjects. The book you mentioned is great for beginner as well. As for the number 42, I'll continue using it until this Saturday (1/10/2004) when it will become 43. Inflation is not as big as you think. PS: --- I was born on 1/10/1961, and that makes January 10th so special. I don't have to work on that great day, mostly because it's Saturday. On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > Mladen Gogala scribbled on the wall in glitter crayon: > > > Lemme guess: you just started on your new job as a DBA? You are > > another person to which can only wholeheartedly recommend Jonathan's > > book. As for your questions, the answer is "42". > > actually, if she's just starting out, i'd recommend Marlene, Rachel and > Jim's book first, then Jonathan's. > > and are you sure it's not "57" now due to inflation? > > -- > Bill "Shrek" Thater ORACLE DBA > "I'm going to work my ticket if I can..." -- Gilwell song > [EMAIL PROTECTED] > > Perfection of means and confusion of ends seem to characterize our age. - > Albert Einstein > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thater, William > 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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: table reorganizations
I'm surprised at these responses. I'm asking what sql statement most people use to identify tables that need reorganization because of "holes". We had an Oracle consultant here and he uses Select table_name, blocks-((num_rows*avg_row_len/)*(1+(pct_free/100))) blkdiff >From dba_tables Where blkdiff > 100; To determine reorganization need. What sql statement is used by others? Jolene -Original Message- Sent: Wednesday, January 07, 2004 2:25 PM To: Multiple recipients of list ORACLE-L I usually recommend Gospel by Jonathan for its completeness and a wide range of subjects. The book you mentioned is great for beginner as well. As for the number 42, I'll continue using it until this Saturday (1/10/2004) when it will become 43. Inflation is not as big as you think. PS: --- I was born on 1/10/1961, and that makes January 10th so special. I don't have to work on that great day, mostly because it's Saturday. On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > Mladen Gogala scribbled on the wall in glitter crayon: > > > Lemme guess: you just started on your new job as a DBA? You are > > another person to which can only wholeheartedly recommend Jonathan's > > book. As for your questions, the answer is "42". > > actually, if she's just starting out, i'd recommend Marlene, Rachel > and Jim's book first, then Jonathan's. > > and are you sure it's not "57" now due to inflation? > > -- > Bill "Shrek" Thater ORACLE DBA > "I'm going to work my ticket if I can..." -- Gilwell song > [EMAIL PROTECTED] > -- > -- > Perfection of means and confusion of ends seem to characterize our age. - > Albert Einstein > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thater, William > 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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Shrake, Jolene 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: table reorganizations
And to add to what Stephane said about the high water mark and chaining, another possible reason for reorging a table is for getting better clustering around a particular column(s) that are typically used as constraining criteria. By concentrating the like values in as few blocks as possible, you can dramatically reduce your IO, resulting in significant performance increases for queries constraining on those columns by which you ordered/clustered the data. Yes, this could also be done naturally without having to reorg by (1) using an IOT, or (2) have the table self clustered on that column, but in our case, neither was applicable or desired for various reasons. I think I've seen Kyte mention this as well, and the tuning guide may mention it. But this is really a highly specific instance and not necessarily all that common, though I've worked on some DW's where data was loaded on a daily basis and this was a very common task that took place on more recent partitions every weekend. Parallel CTAS the data out, build the indexes in parallel, exchange the data back in, all taking very little time. You would literally drop queries going in by the index from minutes to seconds. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Stephane Faroult > Sent: Wednesday, January 07, 2004 2:14 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: table reorganizations > > > > "Shrake, Jolene" wrote: > > > > What SQL statement do you use to identify tables that need > > reorganization? > > > > How do you identify tables that are used in full table scans? How > > often do you run this query? > > > > Thanks, > > Jolene > > Jolene, > > If your tables are reasonably sized initially, very few reasons may > justify a reorganization (moreover, the mere size of some tables rules > it out from the start ...). The only reasonable cases are substantial > chaining, when there is no 'good' reason for that (ie if a row can fit > into a block), which you will see if you collect statistics in > DBA/USER_TABLES, and the other one is a high water mark in a table which > is supposed to normally contain few rows. This one is harder to check, > the easiest is probably to SET AUTOTRACE under SQL*Plus and run > something like SELECT /*+ FULL */ COUNT(*) and check how many blocks > (consistent gets + db block gets) were visited. If it's very high > compared to what you would have normally expected, reorganizing may be > necessary. But this only affects tables in which you can have massive > deletes. > Your second question gives the impression that you consider full table > scans as a bad thing, which they are not necessarily. What is bad is > what is much slower than it could be, and occurs too often for comfort. > One of the places you can check is V$SQL; With Oracle 9.x, make sure > that timed_statistics is set to TRUE et looks for statements with the > highest elapsed_time. For older versions, buffer_gets is a good > indicator. > > HTH, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > 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: Larry Elkins 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: table reorganizations
Mladen Gogala scribbled on the wall in glitter crayon: > I was born on 1/10/1961, and that makes January 10th so special. > I don't have to work on that great day, mostly because it's Saturday. happy early birthday, you young whippersnapper you.;-) -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Consider the past and you shall know the future. - Chinese Proverb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: table reorganizations
And for us dylsexics it has always been 24 At 01:09 PM 1/7/2004, you wrote: and are you sure it's not "57" now due to inflation? -- Bill "Shrek" Thater ORACLE DBA Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: table reorganizations
I usually recommend Gospel by Jonathan for its completeness and a wide range of subjects. The book you mentioned is great for beginner as well. As for the number 42, I'll continue using it until this Saturday (1/10/2004) when it will become 43. Inflation is not as big as you think. PS: --- I was born on 1/10/1961, and that makes January 10th so special. I don't have to work on that great day, mostly because it's Saturday. On 01/07/2004 03:09:53 PM, "Thater, William" wrote: > Mladen Gogala scribbled on the wall in glitter crayon: > > > Lemme guess: you just started on your new job as a DBA? You are > > another person to which can only wholeheartedly recommend Jonathan's > > book. As for your questions, the answer is "42". > > actually, if she's just starting out, i'd recommend Marlene, Rachel and > Jim's book first, then Jonathan's. > > and are you sure it's not "57" now due to inflation? > > -- > Bill "Shrek" Thater ORACLE DBA > "I'm going to work my ticket if I can..." -- Gilwell song > [EMAIL PROTECTED] > > Perfection of means and confusion of ends seem to characterize our age. - > Albert Einstein > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thater, William > 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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: table reorganizations
Actually, read "The Goal" by Eliyahu Goldratt first, then the techie books. 57 is the magic number at Heinz... "Thater, William" wrote: > Mladen Gogala scribbled on the wall in glitter crayon: > > > Lemme guess: you just started on your new job as a DBA? You are > > another person to which can only wholeheartedly recommend Jonathan's > > book. As for your questions, the answer is "42". > > actually, if she's just starting out, i'd recommend Marlene, Rachel and > Jim's book first, then Jonathan's. > > and are you sure it's not "57" now due to inflation? > > -- > Bill "Shrek" Thater ORACLE DBA > "I'm going to work my ticket if I can..." -- Gilwell song > [EMAIL PROTECTED] > > Perfection of means and confusion of ends seem to characterize our age. - > Albert Einstein > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thater, William > 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: Daniel Fink 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: table reorganizations
SQL> select owner, object_name from dba_objects 2 where object_type like 'TABLE%' and status like 'NEEDS REORG%' no rows selected which obviously tells me that my system is fine. No reorgs required. At 12:59 PM 1/7/2004, you wrote: Lemme guess: you just started on your new job as a DBA? You are another person to which can only wholeheartedly recommend Jonathan's book. As for your questions, the answer is "42". On 01/07/2004 02:39:26 PM, "Shrake, Jolene" wrote: > What SQL statement do you use to identify tables that need > reorganization? > Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: table reorganizations
> "Shrake, Jolene" wrote: > > What SQL statement do you use to identify tables that need > reorganization? > > How do you identify tables that are used in full table scans? How > often do you run this query? > > Thanks, > Jolene Jolene, If your tables are reasonably sized initially, very few reasons may justify a reorganization (moreover, the mere size of some tables rules it out from the start ...). The only reasonable cases are substantial chaining, when there is no 'good' reason for that (ie if a row can fit into a block), which you will see if you collect statistics in DBA/USER_TABLES, and the other one is a high water mark in a table which is supposed to normally contain few rows. This one is harder to check, the easiest is probably to SET AUTOTRACE under SQL*Plus and run something like SELECT /*+ FULL */ COUNT(*) and check how many blocks (consistent gets + db block gets) were visited. If it's very high compared to what you would have normally expected, reorganizing may be necessary. But this only affects tables in which you can have massive deletes. Your second question gives the impression that you consider full table scans as a bad thing, which they are not necessarily. What is bad is what is much slower than it could be, and occurs too often for comfort. One of the places you can check is V$SQL; With Oracle 9.x, make sure that timed_statistics is set to TRUE et looks for statements with the highest elapsed_time. For older versions, buffer_gets is a good indicator. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: table reorganizations
Mladen Gogala scribbled on the wall in glitter crayon: > Lemme guess: you just started on your new job as a DBA? You are > another person to which can only wholeheartedly recommend Jonathan's > book. As for your questions, the answer is "42". actually, if she's just starting out, i'd recommend Marlene, Rachel and Jim's book first, then Jonathan's. and are you sure it's not "57" now due to inflation? -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] Perfection of means and confusion of ends seem to characterize our age. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: table reorganizations
Lemme guess: you just started on your new job as a DBA? You are another person to which can only wholeheartedly recommend Jonathan's book. As for your questions, the answer is "42". On 01/07/2004 02:39:26 PM, "Shrake, Jolene" wrote: > What SQL statement do you use to identify tables that need > reorganization? > > How do you identify tables that are used in full table scans? How often > do you run this query? > > Thanks, > Jolene > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: table reorganizations
Title: Message Jolene, When I first saw your posting, I considered if it was intended as bait;-) You'll have to forgive me for thinking so, as these are popular topics of contention. Now, for the forthright (and hopefully bait-free) answer: Please define what you mean by "re-organization". If you're on at least 8i and locally-managed tablespaces, your tables should never need to be re-organized. Dozens or even hundreds of extents are not likely to be a problem. The short answer: In general, don't worry about re-organizing your tables. Use locally managed tablespaces with uniform extent sizes. Pick a few different uniform extent sizes. When a table really grows large, move to a tablespace with a larger uniform size. See the "How to Stop Defragmenting and Start Living" paper, available on MetaLink. (If you don't have MetaLink access, I think you can find it via Google search as well.) Full table scans: Don't worry about which tables have full table scans going on. Worry instead about your critical business processes, and how they are performing. Do you see one that's not performing up to par? Investigate that. You may (or may not) find slow performance caused by an inappropriate full table scan. It could also be caused by inefficient index range scan, where replacing it with a full table scan is appropriate. But, the point is, by concentrating on the slowest of the critical business processes, you're most likely to find yourself focusing on the most important pieces of your system which simultaneously have the most room for improvement. Hope that helps, -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -Original Message-From: Shrake, Jolene [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:39 PMTo: Multiple recipients of list ORACLE-LSubject: table reorganizations What SQL statement do you use to identify tables that need reorganization? How do you identify tables that are used in full table scans? How often do you run this query? Thanks, Jolene
