Re: Separate Indexes and Data
A fine response, Richard. Thank you. One of the guys coding the index stuff was/is Jonathan Klein, and I remember asking him some years ago about reuse of index blocks, and he - at that point - said that he was pretty sure he put the reuse of leaf blocks into 7.1, but that branch blocks didn't get reused. That was then, and I think it has changed since. Not that it matters that much - there are few branch blocks compared to leaf blocks, and it's not often a branch blocks is completely emtied anyway :). Mogens Richard Foote wrote: Hi All, I'm having all sorts of problems getting these emails in a logical order (if at all). This is the first post on this subject I've received since I posted to Rachael, I haven't even received my own post yet !! Anyways, going back in order First to John, no, not all monotonically here today, gone tomorrow indexes require rebuilding. Note that fully emptied index blocks get placed on the freelist and are fully reusable by subsequent index splits. Therefore if you perform batch deletes over a specific period whereby most deleted entries fully empty a range of index nodes, then frequent rebuilding is highly questionable. Yes, Index Scans/Fast Full Index Scans etc. could be impacted in the interim, it kinda depends on *when* the same volume of data is to be reinserted. Jared, please do write your article (the more solid articles out there the better)!! However note that Jonathan Lewis has written a couple of nice articles over at www.dbazine.com regarding some truths about indexes and index rebuilding. Unfortunately the same site hosts truly awful articles by John Weeg and Mike Hordila who both promote some shocking untruths/myths regarding indexes (that Oracle indexes become unbalanced, that deleted space is never reused, that 4 extents is sufficient for an index, etc. etc.) so one needs to exercise caution when reading stuff from there. Jay, note that indexes generally *do* release space from deleted entries !! Deleted space from a index node within the current index structure can be totally reused by subsequent inserts. And as mentioned earlier, fully emptied blocks can be reused by subsequent index block splits. The requirement to rebuild an index is *extremely rare*. This subject has been raised a number of times recently on the Oracle newgroups (eg. http://groups.google.com/groups?q=g:thl4040185351ddq=hl=enlr=ie=UTF-8selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au http://groups.google.com/groups?q=g:thl4040185351ddq=hl=enlr=ie=UTF-8selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au ). It feels like fighting a lossing battle but one can only try. Yes bulk deletes without subsequent re-inserts or without re-inserts within a reasonable period requires both table and hence index rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically* increasing index entries might require index rebuilds (or coalescing) to compact index structure for both range scan and fast full index scans. But these are generally *exceptions*, not the norm. Hope this mail makes it ?? Cheers Richard - Original Message - *From:* John Kanagaraj mailto:[EMAIL PROTECTED] *To:* Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] *Sent:* Tuesday, October 14, 2003 5:44 AM *Subject:* RE: RE: Separate Indexes and Data Jared, Any indexes supporting a In-Today; Gone-Tomorrow status table will require index rebuilds. Most of them have monotonically increasing numbers which lends itself to a 'holey' index... (I have a bunch of them with Oracle Apps Concurrent Manager and Workflow tables) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *Sent:* Monday, October 13, 2003 11:39 AM *To:* Multiple recipients of list ORACLE-L *Subject:* RE: RE: Separate Indexes and Data hmmm... fodder for an article I've been contemplating. Indexes: to rebuild or not to rebuild - that is the question There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To:Multiple
Re: RE: Separate Indexes and Data
Title: Message Hi All,I'm having all sorts of problems getting these emails in a logical order (if at all). This is the first post on this subject I've received since I posted to Rachael, I haven't even received my own post yet !!Anyways, going back in orderFirst to John, no, not all monotonically "here today, gone tomorrow" indexes require rebuilding. Note that fully "emptied" index blocks get placed on the freelist and are fully reusable by subsequent index splits. Therefore if you perform batch deletes over a specific period whereby most deleted entries fully empty a range of index nodes, then frequent rebuilding is highly questionable. Yes, Index Scans/Fast Full Index Scans etc.could be impacted in the interim, it kinda depends on *when* the same volume of data is to be reinserted. Jared, please do write your article (the more solid articles out there the better)!! However note that Jonathan Lewis has written a couple of nice articles over at www.dbazine.com regarding some truths about indexes and index rebuilding. Unfortunately the same site hosts truly awful articles by John Weeg and Mike Hordila who bothpromote some shocking untruths/myths regarding indexes (that Oracle indexes become unbalanced, that deleted space is never reused, that 4 extents is sufficient for an index, etc. etc.) so one needs to exercise caution when reading stuff from there.Jay, note that indexes generally *do* release space from deleted entries !! Deleted space from a index node within the current index structure can be totally reused by subsequent inserts. And as mentioned earlier, fully emptied blocks can be reused by subsequent index block splits. The requirement to rebuild an index is *extremely rare*. This subject has been raised a number of times recently on the Oracle newgroups (eg. http://groups.google.com/groups?q=g:thl4040185351ddq=hl=enlr=ie=UTF-8selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au). It feelslike fighting a lossing battle but one can only try. Yes bulk deletes without subsequent re-inserts or without re-inserts within a "reasonable" period requires both table and hence index rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically* increasing index entries might require index rebuilds (or coalescing) to compact index structure for both range scan and fast full index scans. But these are generally *exceptions*, not the norm. Hope this mail makes it ?? Cheers Richard - Original Message - From: John Kanagaraj To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 14, 2003 5:44 AM Subject: RE: RE: Separate Indexes and Data Jared, Any indexes supporting a "In-Today; Gone-Tomorrow" status table will require index rebuilds. Most of them have monotonically increasing numbers which lends itself to a 'holey' index... (I have a bunch of them with Oracle Apps Concurrent Manager and Workflow tables) John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 13, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: Separate Indexes and Datahmmm... fodder for an article I've been contemplating. "Indexes: to rebuild or not to rebuild - that is the question" There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Separate Indexes and DataI assume that what Rachel is referring to is the fact that indexes willgenerally not release much space when the underlying rows are deleted. Theyjust keep growing, so if you have a large indexed table that frequentlydeletes and inserts the indexes can grow to fairly ridiculous sizes over aperiod of time. We just went through the exercise of rebuilding indexes ona db supporting a 3rd party app and reclaimed about 70% of the allocatedindex space.Jay MillerSr. Oracle DBAx68355-Original Message-Sent: Sunday, October 12, 2003 7:39 AMTo: Multiple
RE: RE: Separate Indexes and Data
I assume that what Rachel is referring to is the fact that indexes will generally not release much space when the underlying rows are deleted. They just keep growing, so if you have a large indexed table that frequently deletes and inserts the indexes can grow to fairly ridiculous sizes over a period of time. We just went through the exercise of rebuilding indexes on a db supporting a 3rd party app and reclaimed about 70% of the allocated index space. Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Sunday, October 12, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used
RE: RE: Separate Indexes and Data
hmmm... fodder for an article I've been contemplating. Indexes: to rebuild or not to rebuild - that is the question There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Separate Indexes and Data I assume that what Rachel is referring to is the fact that indexes will generally not release much space when the underlying rows are deleted. They just keep growing, so if you have a large indexed table that frequently deletes and inserts the indexes can grow to fairly ridiculous sizes over a period of time. We just went through the exercise of rebuilding indexes on a db supporting a 3rd party app and reclaimed about 70% of the allocated index space. Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Sunday, October 12, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild
RE: RE: Separate Indexes and Data
the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: "MacGregor, Ian A." [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled "How To Start Defragmenting and Start Living" or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called "The OFA Standard--Oracle for Open Systems," and section 5 of "Configuring Oracle Server for VLDB," both available for free at www.hotsos.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Thomas Day Sent: Monday, September 29, 2003 9:05 AM To: Multiple recipients of list ORACLE-L My struggle is not with the directory layout OFA. It is with the "mythical" OFA that every DBA that I have talked to knows all about. Where ORACLE says that if you are a good and competent DBA you will separate
RE: RE: Separate Indexes and Data
what Jay said :) in the beginning (hopefully this will have settled down), there were a large number of deletes of daily data as the load process was refined (ie, bugs were fixed) --- [EMAIL PROTECTED] wrote: I assume that what Rachel is referring to is the fact that indexes will generally not release much space when the underlying rows are deleted. They just keep growing, so if you have a large indexed table that frequently deletes and inserts the indexes can grow to fairly ridiculous sizes over a period of time. We just went through the exercise of rebuilding indexes on a db supporting a 3rd party app and reclaimed about 70% of the allocated index space. Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Sunday, October 12, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables
Re: RE: Separate Indexes and Data
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB, both available for === message truncated === __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- 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: RE: Separate Indexes and Data
Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear
RE: locally managed autoallocate (was: Separate Indexes and Data)
-Original Message- From: Jesse, Rich Sent: Wednesday, October 01, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: locally managed autoallocate (was: Separate Indexes and Data) Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) (blush) Obviously, that's supposed to say left in the tableSPACE. 9.0.1 'unable to allocate extent' C:\Documents and Settings\Niallsqlplus niall/niall SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 2 21:59:45 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create tablespace test_auto 2 datafile 'c:\oracle\oradata\home9i\test_auto.dbf' size 1152k 3 extent management local; Tablespace created. SQL create table t1(n number,charcol char(200)) 2 tablespace test_auto 3 ; Table created. SQL select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 1 SQL begin 2 for i in 1..14 loop 3 execute immediate 'alter table t1 allocate extent'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL analyze table t1 compute statistics; Table analyzed. SQL select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 15 SQL alter table t1 allocate extent; Table altered. SQL select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 16 SQL alter table t1 allocate extent; alter table t1 allocate extent * ERROR at line 1: ORA-01653: unable to extend table NIALL.T1 by 128 in tablespace TEST_AUTO SQL select bytes from dba_free_space where tablespace_name='TEST_AUTO'; BYTES -- 65536 SQL create table tX(n number,charcol char(200)) 2 tablespace test_auto; Table created. 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: locally managed autoallocate (was: Separate Indexes and Data)
Well, sort of. If I had a 9i DB to play with, I'd try this myself... 1) Create an SMT. 2) Create 50 tables in that TS. 3) Drop every even table just created. 4) Extend one of the remaining tables until you can't. Depending on the size of the TS created and the blocksize, you could have a situation where there's enough freespace in the TS, but not contiguous. Thus, fragmentation. Granted, this is isn't a typical case, but over a year or two, I could see it happening here. Maybe I'll install 9iR2 at home tonite to try this...sigh Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Niall Litchfield [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 5:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: locally managed autoallocate (was: Separate Indexes and Data) -Original Message- From: Jesse, Rich Sent: Wednesday, October 01, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: locally managed autoallocate (was: Separate Indexes and Data) Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) (blush) Obviously, that's supposed to say left in the tableSPACE. 9.0.1 'unable to allocate extent' C:\Documents and Settings\Niallsqlplus niall/niall SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 2 21:59:45 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create tablespace test_auto 2 datafile 'c:\oracle\oradata\home9i\test_auto.dbf' size 1152k 3 extent management local; Tablespace created. SQL create table t1(n number,charcol char(200)) 2 tablespace test_auto 3 ; Table created. SQL select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 1 [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Separate Indexes and Data
Couldn't you do this with a simple: select owner, table_name from all_tables where tablespace_name= 'index_tbs'; ? Or of course use IN for a list of tablespaces? Or am I missing something? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: 30 September 2003 22:45To: Multiple recipients of list ORACLE-LSubject: RE: Separate Indexes and DataGood question Ian. If anyone does have a different backup schedule for index tbs , I would be interested to know how they ensure that the index TBS do not have any data segments in them. Jared "MacGregor, Ian A." [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 10:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Separate Indexes and DataI'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables.On the size of the segments: The paper entitled "How To Start Defragmenting and Start Living" or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paperNever mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregorStanford Linear Accelerator Center [EMAIL PROTECTED]-Original Message-Sent: Monday, September 29, 2003 8:10 AMTo: Multiple recipients of list ORACLE-LThomas,It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including:* Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Anotherexample: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either.* Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity.I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be*able* to do that if your I/O rates indicate that you should.For the original OFA Standard definition, please see section 3 of the document called "The OFA Standard--Oracle for Open Systems," and section 5 of "Configuring Oracle Server for VLDB," both available for free at www.hotsos.com.Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney- Hotsos Symposium 2004: March 7-10 Dallas- Visit www.hotsos.com for schedule details...-Original Message-Thomas DaySent: Monday, September 29, 2003 9:05 AMTo: Multiple recipients of list
RE: RE: Separate Indexes and Data
Whilst the vast majority of extents will be the published ones - 64k, 1m, et al, you occasionally get variants. I have only three gripes with auto-allocate: a) you can't perform the can I extend check on your tablespaces. You cannot predict with 100% certainty what the size of the next extent will be. You have to make assumptions. b) you minimise but not eliminate the fragmentation problem. Its rare but its relatively easy to concoct an example where you have 'n' bytes of free space, but cannot allocate an extent of size less than 'n' bytes c) Cynicism. If there is no problem with 'n' extents (n insert high number here) , why does Oracle implement a solution designed to keep a lid on their number. Besides extent map blocks, is there some serious problem that Oracle is not telling us at the (say) million extent mark? If there is, then what is the problem. If there is not, then why doesn't Oracle abandon the concept altogether and just enforce something similar to what we see in file systems where every extent is a strict (say) 1m in size. Cheers Connor --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: My criticism of the defrag paper was that it did not address what to do when a segment grew large enough to belong in a tablespace with a larger uniform extent size. Moving the segment creates holes in its original tablespace which may close only in the fullness of time. Physical backups of the files comprising the original tablespace include this wasted space, this is compounded by how many days backup you keep available, and the number of copies of backups. You have chosen to get around the segment migration problem by using one very large extent size for everything. Don't you find 5M extents wasteful? What is your block size and the median number of used blocks for your segments outside of the system tablespace? How many such segments are there?. Also many of us use a single backup system to support multiple databases. The number of segments outside the system tablespace here is over 125,. Making all segments at least 5M in size would have a major impact on file sizes, which in turn would have a major impact on backup times, and possibly the size of the tape library needed. I'm interested in the flaws in autoallocate. Does it allocate the wrong amount of space? Ian MacGregor [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 30, 2003 10:50 AM To: Multiple recipients of list ORACLE-L the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I
RE: locally managed autoallocate (was: Separate Indexes and Data)
-Original Message- From: Jacques Kilchoer [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:34 PM To: Multiple recipients of list ORACLE-L Subject: locally managed autoallocate (was: Separate Indexes and Data) Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt necessarily work this way and some people are finding large extent sizes with just a few extents and when tables are dropped this is leading to fragmentation. It hasnt happened to me, but the posts on dejanews were from some pretty good posters. So Im playing conservative. We also had one of the contributors here mention issues. I think Jonathan Lewis has explained the algorithm before, but it's also something that we have investigated here. The algorithm (ignoring some details) is: There will be 4 extent sizes used, 64K, 1M, 8M, 64M As long as object allocation is 1M or less, 64K extent sizes are used, When object allocation is between 1M and 64M, 1M extent sizes are used. When object allocation is between 64M and 1G, 8M extent sizes are used. When object allocation is more than 1G, 64M extent sizes are used. However, when you initially create the object, the extents are determined by figuring out the space allocated to the newly created object taking into account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So the object might start off with 1M extents instead of starting off with 64K extents. The algorithm is similar to the one outlined above but it is more complicated. The NEXT and PCTINCREASE seem to be ignored after the object is created. e.g. create table ... tablespace locally_managed_autoallocate storage (initial 1M next 512K minextents 15 pctincrease 0) ... ; Initial allocation will be 1M + (15 - 1) * 512K = 8M When you create the table, you will see eight extents, each of one megabyte. There are additional wrinkles, but I don't think the algorithm has bugs. I don't think that there really is fragmentation in the sense that an unused extent will remain unused forever. All extents will be in one of the 4 sizes mentioned above, and all are subject to reuse at some point. Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) 64K chunks. I still maintain that system-managed tablespaces are barely better than DMTs -- fragmentation is still potentially a problem and needs to be monitored. On the flip-side, LMT segments need to be watched too in case they are growing beyond the design of the TS (e.g. more than 1024 or how ever many extents). I'd much rather deal with the latter because it's much less likely to happen unexpectedly in our environment. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: locally managed autoallocate (was: Separate Indexes and Data)
-Original Message- From: Jesse, Rich Sent: Wednesday, October 01, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: locally managed autoallocate (was: Separate Indexes and Data) Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) (blush) Obviously, that's supposed to say left in the tableSPACE. 64K chunks. I still maintain that system-managed tablespaces are barely better than DMTs -- fragmentation is still potentially a problem and needs to be monitored. On the flip-side, LMT segments need to be watched too in case they are growing beyond the design of the TS (e.g. more than 1024 or how ever many extents). I'd much rather deal with the latter because it's much less likely to happen unexpectedly in our environment. Rich Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Separate Indexes and Data
Part of the problem is self-inflicted. We currently use separate tablespaces for each major project. For instance: chemical inventory gets its own data and index tablespaces, dosimeter data gets the same, network configuration data as well. For many projects once the design has matured new segment creation is rare. The holes remain. Also data segments cannot be moved willy-nilly, users do not like getting unusable index errors. There are also tables which cannot be easily moved such as tables with longs. These were created before LOBs were available. Moving data also entails a certain amount of risk. Inside a project, we let developers create the tables and indexes which are specific to that project. Very few actually create indexes in their proper tablespaces. Corrective action creates more holes. This is one reason why I am looking at index and data segments in the same tablespace. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 30, 2003 4:25 PM To: Multiple recipients of list ORACLE-L But those holes of exactly the right size for new objects to fit into. Since you'll presumably move it once it gets about 1,000 extents or so that isn't a huge amount of space that's being wasted. Jay Miller Sr. Oracle DBA -Original Message- Sent: Tuesday, September 30, 2003 4:45 PM To: Multiple recipients of list ORACLE-L My criticism of the defrag paper was that it did not address what to do when a segment grew large enough to belong in a tablespace with a larger uniform extent size. Moving the segment creates holes in its original tablespace which may close only in the fullness of time. Physical backups of the files comprising the original tablespace include this wasted space, this is compounded by how many days backup you keep available, and the number of copies of backups. -- 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: MacGregor, Ian A. 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: Separate Indexes and Data
Sure, you could do that. It just doesn't seem like a good way to deal with the possibility of an index tablespace possibly having data segments in it when backing up only data segment tablespaces. Unless you have *really* large databases with very generous restore time requirements , I don't see the point in trying to do this anyway. Jared On Wed, 2003-10-01 at 05:14, Mark Leith wrote: Couldn't you do this with a simple: select owner, table_name from all_tables where tablespace_name = 'index_tbs'; ? Or of course use IN for a list of tablespaces? Or am I missing something? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: 30 September 2003 22:45 To: Multiple recipients of list ORACLE-L Subject: RE: Separate Indexes and Data Good question Ian. If anyone does have a different backup schedule for index tbs , I would be interested to know how they ensure that the index TBS do not have any data segments in them. Jared MacGregor, Ian A. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 10:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB
RE: Separate Indexes and Data
I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB, both available for free at www.hotsos.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Thomas Day Sent: Monday, September 29, 2003 9:05 AM To: Multiple recipients of list ORACLE-L My struggle is not with the directory layout OFA. It is with the mythical OFA that every DBA that I have talked to knows all about. Where ORACLE says that if you are a good and competent DBA you will separate your table data and your index data into two separate tablespaces so that one disk head can be reading index entries while another disk head is reading the table data. You've never run into that? Tim Gorman tim @sagelogix.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: ml-errorsSubject: Re: BAARF 09/28/2003 09:44 PM Please respond to ORACLE-L Thomas, Please pardon me, but you are off-target in your criticisms of OFA. It has never advocated separating tables from indexes for performance purposes. Ironically, your email
Re: RE: Separate Indexes and Data
the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB, both available for free at www.hotsos.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events
RE: RE: Separate Indexes and Data
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Separate Indexes and Data the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our I haven't been following this thread closely, but why are uniform extents no longer good for 9i? 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to I don't believe it's a flaw, it's by design. At least according to Tom Kyte's new book (first chapter's on the web) it is, which is why I can't see me using it for our DBs. fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Separate Indexes and Data
From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 02:09:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Separate Indexes and Data -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Separate Indexes and Data the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our I haven't been following this thread closely, but why are uniform extents no longer good for 9i? Kyte recommends using auto-segment management 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to I don't believe it's a flaw, it's by design. At least according to Tom Kyte's new book (first chapter's on the web) it is, which is why I can't see me using it for our DBs. Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt necessarily work this way and some people are finding large extent sizes with just a few extents and when tables are dropped this is leading to fragmentation. It hasnt happened to me, but the posts on dejanews were from some pretty good posters. So Im playing conservative. We also had one of the contributors here mention issues. fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: [EMAIL PROTECTED] 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: Separate Indexes and Data
Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap
RE: RE: Separate Indexes and Data
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 1:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Separate Indexes and Data From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 02:09:32 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Separate Indexes and Data -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Separate Indexes and Data the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our I haven't been following this thread closely, but why are uniform extents no longer good for 9i? Kyte recommends using auto-segment management Actually, the qualifier from Mr. Kyte is to use system-managed LMTs when you not know how big your objects will become (Ch 3, p19). I don't think this is a blanket statement not to use uniform extents. Our DB tables grow linearly (near enough anyway) to correctly estimate storage requirements for at least a fiscal year. Granted, there is a minimal amount of play involved, mostly revolving around how our business expects to be doing in the coming year. I'm much more concerned about fragmentation left from the creation and deletion of DB objects. With LMTs, it's one thing I don't have to worry about. I'm looking forward to LMTs in 9iR2! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Separate Indexes and Data
Hi! In VLDB environments, it is mostly cheaper to restore and recover the index tablespace datafile in case of block corruption. In my experience, I've been lucky and have been able to get rid of corruptions that way, but I'm sure some people have worse experiences, especially when redologs are corrupted as well. Anyway, rebuilding a huge index is much more expensive operation than recovering restoring a datafile (rebuild requires a lot of IO, CPU and temp space). With 9i, the recovery is even better, if you use RMAN - you just can restorerecover one single block is you want. There is one nice exception - if the corruption occurs in a local index partition, it is possible to rebuild only this particular partition, without need to recover anything. (Partitioning can be considered alsi as a high availability feature, in addition to performance improvements). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 8:34 PM I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB, both available for free at www.hotsos.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Thomas Day Sent: Monday, September 29, 2003 9:05 AM To: Multiple recipients of list ORACLE-L My struggle is not with the directory layout
RE: RE: Separate Indexes and Data
My criticism of the defrag paper was that it did not address what to do when a segment grew large enough to belong in a tablespace with a larger uniform extent size. Moving the segment creates holes in its original tablespace which may close only in the fullness of time. Physical backups of the files comprising the original tablespace include this wasted space, this is compounded by how many days backup you keep available, and the number of copies of backups. You have chosen to get around the segment migration problem by using one very large extent size for everything. Don't you find 5M extents wasteful? What is your block size and the median number of used blocks for your segments outside of the system tablespace? How many such segments are there?. Also many of us use a single backup system to support multiple databases. The number of segments outside the system tablespace here is over 125,. Making all segments at least 5M in size would have a major impact on file sizes, which in turn would have a major impact on backup times, and possibly the size of the tape library needed. I'm interested in the flaws in autoallocate. Does it allocate the wrong amount of space? Ian MacGregor [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 30, 2003 10:50 AM To: Multiple recipients of list ORACLE-L the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data
RE: Separate Indexes and Data
Good question Ian. If anyone does have a different backup schedule for index tbs , I would be interested to know how they ensure that the index TBS do not have any data segments in them. Jared MacGregor, Ian A. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 10:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Thomas, It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including: * Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another example: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either. * Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity. I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be *able* to do that if your I/O rates indicate that you should. For the original OFA Standard definition, please see section 3 of the document called The OFA Standard--Oracle for Open Systems, and section 5 of Configuring Oracle Server for VLDB, both available for free at www.hotsos.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Thomas Day Sent: Monday, September 29, 2003 9:05 AM To: Multiple recipients of list ORACLE-L My struggle is not with the directory layout OFA. It is with the mythical OFA that every DBA that I have talked to knows all about. Where ORACLE says that if you are a good and competent DBA you will separate your table data and your index data into two separate tablespaces so that one disk head can be reading index entries while another disk head is reading the table data. You've never run into that? Tim Gorman tim @sagelogix.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: ml-errorsSubject: Re: BAARF 09/28/2003 09:44
RE: RE: Separate Indexes and Data
But those holes of exactly the right size for new objects to fit into. Since you'll presumably move it once it gets about 1,000 extents or so that isn't a huge amount of space that's being wasted. Jay Miller Sr. Oracle DBA -Original Message- Sent: Tuesday, September 30, 2003 4:45 PM To: Multiple recipients of list ORACLE-L My criticism of the defrag paper was that it did not address what to do when a segment grew large enough to belong in a tablespace with a larger uniform extent size. Moving the segment creates holes in its original tablespace which may close only in the fullness of time. Physical backups of the files comprising the original tablespace include this wasted space, this is compounded by how many days backup you keep available, and the number of copies of backups. -- 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: Separate Indexes and Data
You can always schedule a script which drops all table segments from index tablespaces ;) Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 01, 2003 12:44 AM Subject: RE: Separate Indexes and Data Good question Ian. If anyone does have a different backup schedule for index tbs , I would be interested to know how they ensure that the index TBS do not have any data segments in them. Jared "MacGregor, Ian A." [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2003 10:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Separate Indexes and DataI'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables.On the size of the segments: The paper entitled "How To Start Defragmenting and Start Living" or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paperNever mentioned what to do when an object that used to fit nicely into the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes. Ian MacGregorStanford Linear Accelerator Center [EMAIL PROTECTED]-Original Message-Sent: Monday, September 29, 2003 8:10 AMTo: Multiple recipients of list ORACLE-LThomas,It *is* a good idea to separate index data from heap data into different tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including:* Index segments have different backup and recovery requirements than their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Anotherexample: If you construct your backup schedule to make media recovery time a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either.* Index segments are usually smaller than their corresponding heap segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity.I don't think I ever wrote that you need to put indexes and their corresponding tables/clusters on separate disks, but you do need to be*able* to do that if your I/O rates indicate that you should.For the original OFA Standard definition, please see section 3 of the document called "The OFA Standard--Oracle for Open Systems," and section 5 of "Configuring Oracle Server for VLDB," both available for free at www.hotsos.com.Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney- Hotsos Symposium 2004: March 7-10 Dallas- Visit www.hotsos.com for schedule details...-Original Message-Thomas DaySent: Monday, September 29, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LMy struggle is not with the directory layout OFA.It is with the "my
locally managed autoallocate (was: Separate Indexes and Data)
Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt necessarily work this way and some people are finding large extent sizes with just a few extents and when tables are dropped this is leading to fragmentation. It hasnt happened to me, but the posts on dejanews were from some pretty good posters. So Im playing conservative. We also had one of the contributors here mention issues. I think Jonathan Lewis has explained the algorithm before, but it's also something that we have investigated here. The algorithm (ignoring some details) is: There will be 4 extent sizes used, 64K, 1M, 8M, 64M As long as object allocation is 1M or less, 64K extent sizes are used, When object allocation is between 1M and 64M, 1M extent sizes are used. When object allocation is between 64M and 1G, 8M extent sizes are used. When object allocation is more than 1G, 64M extent sizes are used. However, when you initially create the object, the extents are determined by figuring out the space allocated to the newly created object taking into account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So the object might start off with 1M extents instead of starting off with 64K extents. The algorithm is similar to the one outlined above but it is more complicated. The NEXT and PCTINCREASE seem to be ignored after the object is created. e.g. create table ... tablespace locally_managed_autoallocate storage (initial 1M next 512K minextents 15 pctincrease 0) ... ; Initial allocation will be 1M + (15 - 1) * 512K = 8M When you create the table, you will see eight extents, each of one megabyte. There are additional wrinkles, but I don't think the algorithm has bugs. I don't think that there really is fragmentation in the sense that an unused extent will remain unused forever. All extents will be in one of the 4 sizes mentioned above, and all are subject to reuse at some point. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: locally managed autoallocate (was: Separate Indexes and Data)
Yes, and there is one thing to add: If you do not specify INTIAL, the extent allocation starts with 5 blocks for the intial extent. For 8k, it's 40k, but in an autoallocating LMT extent cannot be smaller then 64k, so it is the amount of the space allocated. The interesting question is: what happens with blocksize-16k? Will there be 64k or two extents of 64k, i.e. 128k? Here is the answer: SQL create tablespace test1 2 datafile '/data/oradata/data/test101.dbf' size 64M reuse 3 autoextend on next 64m maxsize 513M 4 extent management local autoallocate 5 segment space management auto 6 blocksize 16k 7 / Tablespace created. SQL create table a (a number) tablespace test1; Table created. SQL select owner,segment_name,extent_id,blocks 2 from dba_extents 3 where segment_name='A'and tablespace_name='TEST1' 4 and owner=user 5 / OWNER SEGMENT_NA EXTENT_ID BLOCKS -- -- -- -- OPS$MGOGALAA 0 64 16k*64=1M. That means that oracle will allocate a full megabyte for the initial extent. It cannot take 64k, because it's smaller then 5*16k (that number of 5 blocks is hardwired into the RDBMS since time immemorial) and it cannot take two extents because that would, in turn, mean that the initial extent is smaller then 5 blocks. Therefore, it takes 1M. Jonathan Lewis was right. Here is one tecnique for optimizing the disk consumption in such cases: SQL drop tablespace test1 including contents and datafiles; Tablespace dropped. SQL On 2003.09.30 20:34, Jacques Kilchoer wrote: Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt necessarily work this way and some people are finding large extent sizes with just a few extents and when tables are dropped this is leading to fragmentation. It hasnt happened to me, but the posts on dejanews were from some pretty good posters. So Im playing conservative. We also had one of the contributors here mention issues. I think Jonathan Lewis has explained the algorithm before, but it's also something that we have investigated here. The algorithm (ignoring some details) is: There will be 4 extent sizes used, 64K, 1M, 8M, 64M As long as object allocation is 1M or less, 64K extent sizes are used, When object allocation is between 1M and 64M, 1M extent sizes are used. When object allocation is between 64M and 1G, 8M extent sizes are used. When object allocation is more than 1G, 64M extent sizes are used. However, when you initially create the object, the extents are determined by figuring out the space allocated to the newly created object taking into account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So the object might start off with 1M extents instead of starting off with 64K extents. The algorithm is similar to the one outlined above but it is more complicated. The NEXT and PCTINCREASE seem to be ignored after the object is created. e.g. create table ... tablespace locally_managed_autoallocate storage (initial 1M next 512K minextents 15 pctincrease 0) ... ; Initial allocation will be 1M + (15 - 1) * 512K = 8M When you create the table, you will see eight extents, each of one megabyte. There are additional wrinkles, but I don't think the algorithm has bugs. I don't think that there really is fragmentation in the sense that an unused extent will remain unused forever. All extents will be in one of the 4 sizes mentioned above, and all are subject to reuse at some point. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
