RE: Compare the *size* of different schemas
Thanks for replying. I can't do clones (we do for our apps database) because one schema in the production database becomes 6 (soon to be 12) schemas in a single QA database. In addition we can't refresh them all at the same time (nor do we have the space to have 12 separate databases). I do use compress=N. The problem is table x has added 3 new extents since the last export. They're relatively small but the target tablespace may or may not have free blocks of that size available. It may be overkill but it's killing me (or should I say QA wants to kill me everytime the import fails). :-) I'm going to attempt to move the 'create uniform sized tablespaces' up on my todo list, but probably won't even get to play with it until next year sometime (like March). Linda -Original Message- Sent: Friday, November 21, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Linda, When I saw your reference to 'cloning' below, I first thought you were taking physical copies of your files. Perhaps this is something you might want to consider in the future. For one thing, you just have to add up file sizes to see how much space is required on the target machine. With exp/imp I think that you would make your life much simpler using first COMPRESS=N when exporting and then just checking total sizes. You will no longer need big contiguous chunks - having as much space on the target database as on the source database will usually be enough. I don't see why you want to get down to the extent level - it looks like an overkill. HTH SF >- --- Original Message --- - >From: "Seley, Linda" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Fri, 21 Nov 2003 07:14:43 > >I'm wondering if someone has a better solution than >mine (see below) to the following: We have a >number of schemas that get cloned from our >production schema (more on that later). I need to >be able to compare the size of the production >schema to the target schema and determine how much >the objects have grown. The part I'm working >on/struggling with is how to determine whether or >not the target tablespace has enough space to >handle the additional space required. > >What I have so far: I have a "statistics" database >that I've modified to store object information for >each schema (which database/tablespace it came >from, how big it is now, how big the next extent >will be, etc). I've also got the query that says >this schema is x bytes/blocks larger in the >production database than the target database. From >this I'm able to figure out how many extents will >be needed in the target database to handle the size >growth. > >My problem: I can't just compare the size of the >next extent to the largest free chunk in the >tablespace. While that's useful information it >won't alert me if I've only got room for one extent >but will need two. If the target schemas were >refreshed regularly then this might work since any >given object should not have extended more than >once (or a small number of times) but sometimes >weeks or months go between refreshes. Along the >same lines I can't add all of the extents and try >to fit them in the total free space because the >blocks may not be contiguous. (We have a mixture >of extent sizes, I'll convert someday, really I >will!) In addition, if there are 5 tables that >have grown I'd like to be able to determine if >table 1 is going to use up all of the free space >and tables 2-5 won't have enough space to extend. >Etc. > >My 'best' solution: Build a table of existing free >space for each target database/tablespace and do >mock updates attempting to mimic Oracle's behavior >then, from that, determine if I will run out of >space. This seems cumbersome and time-consuming >but it's the only reasonably accurate solution I've >come up with. Does anyone have a better idea? Has >anyone done something similar? > >Some background about our environment: Currently >we're exporting/importing to get the production >data into the other schemas. In some instances we >drop the tables first then import, in others we >truncate then import. In the future some of the >tables that are being truncated will be >incrementally updated (unless the structure changes >then they'll be dropped and re-imported). The >table structures are identical, in general the >initial and next extents are identical but that >isn't true for all objects. The target schemas are >used for development, test, reporting, etc. > >Thanks for taking the time to read this! > >Linda >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.net >-- >Author: Seley, Linda > 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
RE: Compare the *size* of different schemas
Linda, When I saw your reference to 'cloning' below, I first thought you were taking physical copies of your files. Perhaps this is something you might want to consider in the future. For one thing, you just have to add up file sizes to see how much space is required on the target machine. With exp/imp I think that you would make your life much simpler using first COMPRESS=N when exporting and then just checking total sizes. You will no longer need big contiguous chunks - having as much space on the target database as on the source database will usually be enough. I don't see why you want to get down to the extent level - it looks like an overkill. HTH SF >- --- Original Message --- - >From: "Seley, Linda" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Fri, 21 Nov 2003 07:14:43 > >I'm wondering if someone has a better solution than >mine (see below) to the following: We have a >number of schemas that get cloned from our >production schema (more on that later). I need to >be able to compare the size of the production >schema to the target schema and determine how much >the objects have grown. The part I'm working >on/struggling with is how to determine whether or >not the target tablespace has enough space to >handle the additional space required. > >What I have so far: I have a "statistics" database >that I've modified to store object information for >each schema (which database/tablespace it came >from, how big it is now, how big the next extent >will be, etc). I've also got the query that says >this schema is x bytes/blocks larger in the >production database than the target database. From >this I'm able to figure out how many extents will >be needed in the target database to handle the size >growth. > >My problem: I can't just compare the size of the >next extent to the largest free chunk in the >tablespace. While that's useful information it >won't alert me if I've only got room for one extent >but will need two. If the target schemas were >refreshed regularly then this might work since any >given object should not have extended more than >once (or a small number of times) but sometimes >weeks or months go between refreshes. Along the >same lines I can't add all of the extents and try >to fit them in the total free space because the >blocks may not be contiguous. (We have a mixture >of extent sizes, I'll convert someday, really I >will!) In addition, if there are 5 tables that >have grown I'd like to be able to determine if >table 1 is going to use up all of the free space >and tables 2-5 won't have enough space to extend. >Etc. > >My 'best' solution: Build a table of existing free >space for each target database/tablespace and do >mock updates attempting to mimic Oracle's behavior >then, from that, determine if I will run out of >space. This seems cumbersome and time-consuming >but it's the only reasonably accurate solution I've >come up with. Does anyone have a better idea? Has >anyone done something similar? > >Some background about our environment: Currently >we're exporting/importing to get the production >data into the other schemas. In some instances we >drop the tables first then import, in others we >truncate then import. In the future some of the >tables that are being truncated will be >incrementally updated (unless the structure changes >then they'll be dropped and re-imported). The >table structures are identical, in general the >initial and next extents are identical but that >isn't true for all objects. The target schemas are >used for development, test, reporting, etc. > >Thanks for taking the time to read this! > >Linda >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.net >-- >Author: Seley, Linda > 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). >--- >-- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You
RE: Compare the *size* of different schemas
We aren't running statistics against these schemas. When we were they caused extremely poor performance so they were removed. I haven't been able to get sign-off yet on re-instating them in test. Given that it's an ASP system with relatively few tuned queries I think it's likely that they'd continue to cause us grief. We're currently on 8.1.7 but even then we couldn't do compression for QA/reporting if it would impact performance at all. Development, maybe, depending on the the impact. Thanks! Linda -Original Message- Sent: Friday, November 21, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Linda, a stupid question: why are you comparing extents instead of number of records? If my memory serves me right, there used to be things like NUM_ROWS and AVG_ROW_LEN in ALL_TABLES or DBA_TABLES. All you need is to run DBMS_STATS regularly and, voila, you've got yourself an accurate rowcount. What is more, you can turn on segment compression on the non-production copies, if the database version is 9.2 or more. That will lower the space in the QA and development copies, at the expense of performance, of course. On 11/21/2003 10:14:43 AM, "Seley, Linda" wrote: > I'm wondering if someone has a better solution than mine (see below) to the > following: We have a number of schemas that get cloned from our production schema > (more on that later). I need to be able to compare the size of the production > schema to the target schema and determine how much the objects have grown. The part > I'm working on/struggling with is how to determine whether or not the target > tablespace has enough space to handle the additional space required. > > What I have so far: I have a "statistics" database that I've modified to store > object information for each schema (which database/tablespace it came from, how big > it is now, how big the next extent will be, etc). I've also got the query that says > this schema is x bytes/blocks larger in the production database than the target > database. From this I'm able to figure out how many extents will be needed in the > target database to handle the size growth. > > My problem: I can't just compare the size of the next extent to the largest free > chunk in the tablespace. While that's useful information it won't alert me if I've > only got room for one extent but will need two. If the target schemas were > refreshed regularly then this might work since any given object should not have > extended more than once (or a small number of times) but sometimes weeks or months > go between refreshes. Along the same lines I can't add all of the extents and try > to fit them in the total free space because the blocks may not be contiguous. (We > have a mixture of extent sizes, I'll convert someday, really I will!) In addition, > if there are 5 tables that have grown I'd like to be able to determine if table 1 is > going to use up all of the free space and tables 2-5 won't have enough space to > extend. Etc. > > My 'best' solution: Build a table of existing free space for each target > database/tablespace and do mock updates attempting to mimic Oracle's behavior then, > from that, determine if I will run out of space. This seems cumbersome and > time-consuming but it's the only reasonably accurate solution I've come up with. > Does anyone have a better idea? Has anyone done something similar? > > Some background about our environment: Currently we're exporting/importing to get > the production data into the other schemas. In some instances we drop the tables > first then import, in others we truncate then import. In the future some of the > tables that are being truncated will be incrementally updated (unless the structure > changes then they'll be dropped and re-imported). The table structures are > identical, in general the initial and next extents are identical but that isn't true > for all objects. The target schemas are used for development, test, reporting, etc. > > Thanks for taking the time to read this! > > Linda > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Seley, Linda > 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 Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistrans
Re: Compare the *size* of different schemas
Linda, a stupid question: why are you comparing extents instead of number of records? If my memory serves me right, there used to be things like NUM_ROWS and AVG_ROW_LEN in ALL_TABLES or DBA_TABLES. All you need is to run DBMS_STATS regularly and, voila, you've got yourself an accurate rowcount. What is more, you can turn on segment compression on the non-production copies, if the database version is 9.2 or more. That will lower the space in the QA and development copies, at the expense of performance, of course. On 11/21/2003 10:14:43 AM, "Seley, Linda" wrote: > I'm wondering if someone has a better solution than mine (see below) to the > following: We have a number of schemas that get cloned from our production schema > (more on that later). I need to be able to compare the size of the production > schema to the target schema and determine how much the objects have grown. The part > I'm working on/struggling with is how to determine whether or not the target > tablespace has enough space to handle the additional space required. > > What I have so far: I have a "statistics" database that I've modified to store > object information for each schema (which database/tablespace it came from, how big > it is now, how big the next extent will be, etc). I've also got the query that says > this schema is x bytes/blocks larger in the production database than the target > database. From this I'm able to figure out how many extents will be needed in the > target database to handle the size growth. > > My problem: I can't just compare the size of the next extent to the largest free > chunk in the tablespace. While that's useful information it won't alert me if I've > only got room for one extent but will need two. If the target schemas were > refreshed regularly then this might work since any given object should not have > extended more than once (or a small number of times) but sometimes weeks or months > go between refreshes. Along the same lines I can't add all of the extents and try > to fit them in the total free space because the blocks may not be contiguous. (We > have a mixture of extent sizes, I'll convert someday, really I will!) In addition, > if there are 5 tables that have grown I'd like to be able to determine if table 1 is > going to use up all of the free space and tables 2-5 won't have enough space to > extend. Etc. > > My 'best' solution: Build a table of existing free space for each target > database/tablespace and do mock updates attempting to mimic Oracle's behavior then, > from that, determine if I will run out of space. This seems cumbersome and > time-consuming but it's the only reasonably accurate solution I've come up with. > Does anyone have a better idea? Has anyone done something similar? > > Some background about our environment: Currently we're exporting/importing to get > the production data into the other schemas. In some instances we drop the tables > first then import, in others we truncate then import. In the future some of the > tables that are being truncated will be incrementally updated (unless the structure > changes then they'll be dropped and re-imported). The table structures are > identical, in general the initial and next extents are identical but that isn't true > for all objects. The target schemas are used for development, test, reporting, etc. > > Thanks for taking the time to read this! > > Linda > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Seley, Linda > 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 Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any
