Hi All,
Database : ORacle 11g R2 11.2.0.3
OS :- Linux x64
Machine :- Exadata X3
I had an script to collect schema statitics everyday , every 3 hours. This script use to work fine & get over within 3 hours , i.e. before the next job run happens. Recently I changed a few parameters in the method now it it taking a huge time , more tahn 3 hours. So the next cycle of job starts & it goes in hang state.
Old script to gather schema stats :- ( gets completed in less than 3 hours)
dbms_stats.gather_schema_stats(ownname=>'PVR_HIST',
estimate_percent=> dbms_stats.auto_sample_size,
method_opt=> 'FOR ALL COLUMNS SIZE 1',
granularity=> 'ALL',
degree=> 12,
options=>'GATHER EMPTY',
cascade=> true);
New script to gather schema stats :- (takes a huge time and still keeps running)
dbms_stats.gather_schema_stats(ownname=>'PVR_HIST',
estimate_percent=> dbms_stats.auto_sample_size,
method_opt=> 'FOR ALL COLUMNS SIZE 1',
granularity=> 'ALL',
degree=> dbms_stats.auto_degree,
options=>'GATHER EMPTY',
cascade=> true,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE );
The size of the schema is huge :- 540.227417 GB
SELECT sum(bytes)/1024/1024/1024
FROM dba_segments
WHERE owner = 'PVR_HIST';
SUM(BYTES)/1024/1024/1024
-------------------------
540.227417
Can anyone guide me , why the new script with some new/changed parameters it taking a long time. Sometimes it takes 8 - 10 hours & still remains running.
Thanks in advance.