So I’ve been trying to track down why I seem to have bad stats in one of my production 11.2.0.2 databases. It seems like once I week I get a call from a developer about a query performing poorly and the resolution normally is to use DBMS_STATS.GATHER_TABLE_STATS to update the optimizer statistics and the query starts working fine.
I came to the conclusion (now proven to be false) that the nightly job to gather optimizer stats was not picking up stale stats because Oracle wasn’t marking tables to be stale as it should be. Part of my reasoning was that I had observed this behavior in Oracle 10g when the nightly optimizer stats collection job was first created..or was that back in 9i? I can’t remember. Life seemed to work much better in 11.1 but when I upgraded to 11.2 I seem to have bad stats on my tables. Why isn’t Oracle collecting stats as it should?
Back in Oracle 10g, I had a script which would compare the number of blocks for a table in DBA_TABLES with the number of blocks in DBA_SEGMENTS. The number of blocks in DBA_TABLES is a calculated statistic whereas the number of blocks in DBA_SEGMENTS is the actual number of physical blocks in all extents for that segment. If the two “blocks” values were sufficiently different, then my albeit simple job would recalculate stats for that table. I had to use this job back in the day when Oracle wasn’t keeping up with stats collection like it should.
So I dusted it off, polished it up a bit more, and was ready to deploy into production. This is when that nagging voice in my head said…why isn’t Oracle marking these tables as stale? Well let’s verify that the tables aren’t marked as stale. So I issued the following query:
SELECT COUNT(*) FROM dba_tab_modifications;
Imagine my surprise when the count was more than 7,000 tables! What the heck is going on here. There are 7,000 tables that need up-to-date optimizer stats. I had assumed incorrectly that this was the problem area. Maybe the problem is that the job is not running.
Oracle 11g now has autotasks instead of the 10g scheduled job. So lets verify that the autotask is enabled:
SQL> select client_name,status from dba_autotask_client ;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
It is often seen in davidfraymusic.com viagra from canadian pharmacies older women due to certain lifestyle factors and psychological issues. Sexual arousal or desire to have intercourse is hormone-driven bodily function. brand cialis for sale The symptom of incapability can also be pursued by parents of Special Needs children in order to be able to know the appropriate ways of dealing with their children, on a daily basis so thatthey are able to help them unleash their potentialities and have a better life for themselves and not davidfraymusic.com viagra in usa remain as a dependent. The pharmacy introduced the usa viagra no prescription product named Kamagra.
auto space advisor ENABLED
sql tuning advisor ENABLED
Yep, there it is on the first line of output. It should be running nightly. So lets see the last time it ran:
SQL> select * from ( 2 select log_id,log_date from dba_scheduler_job_log where additional_info like '%GATHER_STATS_PROG%' 3 order by log_date desc) 4 where rownum <=5; LOG_ID LOG_DATE ---------- ---------------------------------------- 220881 05-OCT-11 11.07.02.290606 PM -05:00 220215 04-OCT-11 10.30.17.393433 PM -05:00 219835 03-OCT-11 10.18.46.991253 PM -05:00 219447 02-OCT-11 10.09.26.693427 PM -05:00 219383 02-OCT-11 06.09.51.330365 PM -05:00
Well here is the problem! This job has not run since Oct 5, 2011. No wonder I have 7,000 tables in DBA_TAB_MODIFICATIONS.
At this time, I have not been able to ascertain why the job is not running. Everything points to the fact that it should be running. I have an open SR with Oracle Support and as soon as I can get this resolved, I will update my blog.
