Sunday, November 20, 2011


We had ORA-01652 error from one production database recently. The culprit is GATHER_TABLE_STATS  job. By the way, this DB is

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
*** 2011-11-20 20:28:51.105
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Originally it was error out and paging at 4AM which is really not preferred timing for On Call DBA.
Since this DB tend to have higher load during early morning any way. I changed maintenance windows to the late afternoon.
To change this auto statistics collection job time use this command.

attribute => 'repeat_interval',
value => 'freq=daily;byday=SUN,MON,TUE,WED,THU,FRI,SAT;byhour=17;byminute=0; bysecond=0');

However this didn't address the root cause of the issue apparently. A couple of days later the job failed again with same error. 

Increase TEMP tablespace is not an option. The TEMP TBS on this DB is 95G. This job run for 3 hours and used them all. Adding more TEMP will only delay the inevitable.
I decide to changed estimate percent from auto sampling to 1% , this fixed the issue. I did some research on google about this but there's not much useful past discussion.
Only found this asktom thread pretty helpful by pointing the right statement to change the default GATHER_TABLE_STATS job
ops$tkyte%ORA10GR2> select dbms_stats.get_param( 'estimate_percent' ) from dual;


reset or set them with these:

No comments: