I'm modifying my statistics gathering process to take advantage of Oracle's ability to figure out what's stale and what's not. I know I can use dbms_stats.gather_schema_stats(options=>'GATHER STALE') to get statistics, but that just goes off into oblivion and I can't track it's progress. I decided to use the 'LIST STALE' and 'LIST EMPTY' options to get the tables that need statistics according to Oracle. I'm then going to record that list of tables and the amount of time spent analyzing each object.
One thing that I'm not sure of is with partitioned tables. Say I have a table with 20 partitions partitioned by date named part_01 through part_20. Each partition contains a month's worth of data. Months 1 through 17 contain about 120,000 records each. On the first day of the month, part_18 gets 20,000 rows and I COMPUTE statistics on the entire table. On the second day of the month, I add another 20,000 rows. I expect that dbms_stats.gather_schema_stats(options=>'LIST STALE') would list the partition as having stale statistics, but would it list the entire table as well?
I'm setting up a test....
Subscribe to:
Post Comments (Atom)
1 comment:
Not quite relevant but, when I implemented 'gather stale' on a partitioned data warehouse I tracked 'last analyzed' from user_tab_partitions and user_tables. For my database I found that the partitions recently inserted into were analyzed as were the global table stats. Untouched partitions where not reanalyzed.
Post a Comment