how to improve stats gathered on database objects
Posted on 2014-04-22
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?