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: LSeley_at_IQNavigator.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

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 such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Seley, Linda
  INET: LSeley_at_IQNavigator.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).