DBMS_STATS.GATHER_SCHEMA_STATS
I wanted to see if somebody has any insight on my the situation I faced:
Few fews ago, I implemented collecting stats and changed the optimizer to ALL_ROWS in Oracle 9.2.0.8 database. After much research and Oracle SR's help.. I ran this to collect stats. SHort story I turned on Monitoring and wanted to use GATHER_AUTO as an option but somehow oracle decided to compute stats and not use any sample size and with one of the tables with 32 million rows it was taking 33 hrs so we went to GATHER OPTION with the below syntax and it took 51 minutes.
set timing on;
exec dbms_stats.gather_schema_stats (ownname => 'CEPROD01',cascade => TRUE,metho
d_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => dbms_stats.auto_sample_
size,DEGREE => 5);
The problem arose when after 3 weeks in production, I started using the above sql and the stats wasn't gathered even after 2 hrs and then saw there were some updates going against our biggest 32 million row table and then had the middle ware stopped to stop those updates adn then let my script run for another 1 hrs.. but it wouldn't complete.. Decided to kill the ksh scrpt and my sql session which was collecting stats and started again. And then used the sample size of 20% but still it wasn't complete after 3hrs.. And due to application need to be available I had to kill the job again since it already had previous stats. Any idea why was this behaviour this time since first time it completed in 51 min and the change in the data wasn't much (including updates, deltes and inserts, checked from dba_tab_modifications but like 200,000).
I didnot delete previous stats before running the gather again assuming it will overwrite. Need to know why this would happen before I make another attempt to gather stats in production.
Any help would be appreciated.
Thanks