Link to home
Start Free TrialLog in
Avatar of sikyala
sikyalaFlag for United States of America

asked on

how to improve stats gathered on database objects

I understand that Oracle 10g by default runs a job that updates the statistics on all database objects. However, I looked at the reports generated from this job and noticed that statistics on most of objects are timing out. Therefore, the statistics remains stale. I am trying to get total records counts for all non system schema account tables. I have run a script that queries dba_tables. However, because the statistics is stale or hasn't been successfully run on most of the tables I am not getting an accurate count. The database is A RAC environment with 2 nodes and  is 6 terabytes in size. There are tables that have partitions using range. The system is production and is heavily used. Even during non business hours there are ingestion scripts being run. I am trying to figure out a way to get an accurate count at a point in time and not jeopardize the availability of the database. The database is version 10.2.0.4 however, due to other issues it is not safe to apply anymore patches. However, we are currently migrating to a 11g virtual environment and getting these row counts is a part of that migration. Can anyone suggest a way to get an accurate count under these circumstances?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Even with stats the num_rows from the tables view isn't accurate.  As you mentioned, new data is being added all the time.

The only accurate way is a select count(*) from the tables.

Back to stats:
You can always manually generate the stats with the dbms_stats package.

Have you looked at user_indexes and the num_rows column?  Generating stats for a primary key index should be faster than the table and the num_rows for a primary key should have as accurate of info as the one from the tables view.
Avatar of sikyala

ASKER

ok I will try looking at the user_indexes. i have tables that have huge amounts of data billions of records. Doing a select count always causes it to hang and not return results for long periods of time and so I am tring to avoid having to do that.
>>hang and not return results for long periods of time and so I am tring to avoid having to do that.

It all depends on the level of accuracy you are comfortable with.
Just to add you can use parallel clause in the count query to retrieve it little bit faster than you used to run a normal select (for eg: select /*+ PARALLEL (t,4) */ count(1) from Tab t;

before you run with parallel check out the explain plan of the queries,   with parallel without parallel, there is a sample illustration and results of using parallel clause in tom kyte's blog

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:885628301719
Avatar of sikyala

ASKER

I tried the select with parallel and it still timed out on me. I checked and some of the tables don't have a primary key. So I don't know what alternative I would have.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sikyala

ASKER

thanks