Oracle table size statistics

[Handruin]

Anyone out there know Oracle? I'm trying to figure out a good way to capture the growth of a know set of tables (lets say 100 tables) at a capture rate of every 5 minutes. This is on a development system, so performance can be affected. We're basically trying to predict capacity usage for one of our own products. I can write the tool or script to capture every 5 minutes, but what I need to make sure is that I'm running the correct SQL statements to get the right data.

I read a post over at stack overflow that I think answers my need, but do any of you have experience with this sort of thing?

 

[time]

Some of the techniques seem unnecessarily sophisticated for what you want - or for anything, in fact. And please ignore the warnings about updating statistics, it's extremely unlikely your instance isn't set up to use the Cost Based Optimizer, the alternative (Rule Based) came out of The Ark when the CBO wasn't sufficiently sophisticated (although I just read that Oracle Applications still used RBO until 11i - sheesh!). And if updating statistics buggers up a query plan, it's better you find out in Development rather than Production!

When I've wanted to see database storage utilization, I just organize a bunch of test activity and then check the sizes afterwards. I don't follow why you need to check every 5 minutes?

Blocks are an adequate indicator as long as the tables are growing, i.e. the High Water Mark is increasing. Otherwise, just count the rows; as long as the primary index isn't too large, it should be PDQ. Oracle will cache this, so if the tables don't change, the results should come from cache after the first time.

I'm just guessing, but if all you need to know is how big your entire database is growing, you only need to look at TableSpace utilization.

 

[Handruin]

Is it easy to check if my setup already has the CBO so that I can alleviate that concern?

As for the testing, we already do have several testing scripts that can produce repeatable use case scenarios. We plan to do both the 5 minute capture during a longer test suite run in which we'll have the count of transactions in our scripting and then unit tests for each script with a single instance to measure the size it generates per instance.

What do you mean by simple counting the rows? Are all rows the same size? That seems contrary to what I would have expected.

I do need to know how large it's growing but also we need to produce a figure which says something along the lines of "If you use the product to store n number of tickets/calls it will consume n amount of storage space in Oracle." That way a customer can gauge how much storage to plan per year if their call center is expecting 15,000 tickets on average.

So in theory I could do something like this:

begin  
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');  
end;




select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) ROW_SIZE_KB, (BLOCKS * 8)  BLOCK_SIZE_KB  
from USER_TABLES  
order by TABLE_NAME

 

[time]

Sorry, I've never used the RBO, I was just trying to be complete. According to OraFAQ: "Rule Based Optimizer is not supported in Oracle 10g and above releases".

So forget about it. I have no idea what those two guys in your thread were on about, the most up to date statistics will (unsurprisingly) usually give the best results. If they don't, you've uncovered a time bomb.

You can count the rows and multiply by whatever the average row length is. Especially in your case, any comment text strings will cause the storage requirements to fluctuate wildly. Try to remember, this is only a test run and an estimate of what a production system will see, so a single static estimate of row length should be adequate. If not, do the dynamic calc as in your query.

BTW, the code in the Ask Tom link embedded in your link uses dbms_space.free_space, which does not require you to update stats first. Or there's dbms_space.space_usage and dbms_space.object_space_usage, all of which give you storage in bytes. But they have to be called for each table in turn in PL/SQL.

A script would look something like this:

SET serveroutput on

DECLARE

v_space_used NUMBER;​

BEGIN

dbms_space.object_space_usage('schema name', 'table name', 'TABLE', NULL, v_space_used);

dbms_output.put_line('Space Used: ' || TO_CHAR(v_space_used));​

END;
/

There's a lot of different ways to skin a cat in Oracle. Just go for whatever is the simplest for your situation.

 

[Handruin]

Thanks a bunch for your help with this, time. I'll give that a try and see how it works out. I'll try both the row-based approach and also the sample script you gave me just to get different data points. Thanks again for the help with this.

 

[time]

Not sure I've done anything yet - you seem to be on the right track. Just don't get bamboozled with the fancy stuff that some people like to throw around, and also remember that a lot of web examples are very old and have been superseded by later versions of Oracle.

I'm actually struggling 'cause I haven't even looked at Oracle for a year, and don't have a copy installed anywhere I can access. But if you need anything more concrete, I'd be happy to help by either installing 10g or just asking my friend who does have a copy running.