This content has been marked as final. Show 7 replies
Please post your Server, OS and Database configuration.
Check using below query what it is doing & estimate time to complete a particular operation
You may set estimate_percent to 10 & set degree to at least 10 or 20 if done in off peak hours to minimize time, check impact if any
set line 200 set pages 900 col opname for a20 col start_time for a20 col START_TIME for a40 col target for a30 select distinct l.SID,l.serial#,s.machine,l.OPNAME,l.target,l.SOFAR,l.TOTALWORK,(l.TOTALWORK-l.SOFAR)/l.TOTALWORK*100 "% Remain", to_char(l.START_TIME,'dd-mon-yy hh24:mi:ss') "START_TIME", vp.spid, (l.ELAPSED_SECONDS)/60/60 "Elapsed Hr",(l.TIME_REMAINING)/60/60 "Time_remain Hr",c.sql_text ,s.program,sw.event , c.sql_id from v$session_longops l,v$session s ,v$sql c ,v$session_wait sw , v$process vp where l.SOFAR != l.TOTALWORK and l.sid=s.sid and s.paddr=vp.addr and s.sid=sw.sid and s.sql_hash_value=c.hash_value ;
Check this thread
DBMS_STATS.gather_schema_stats JOB was running slowly
If your schema is large it will take a long time...Depending on resources available to complete the job and other contention while the job is running, it could be fast or slow.
Thanks for the responses, however not resolved yet.
Does all_tab_modifications view give details for tables which are having stale stats? We can check the data count for these tables in all_tab_modifications view to check if any table has huge data. Does table lock slower the stats gather? Can the estimate_percent and degree be changed to save time for stats gather?
Hi,1 person found this helpful
You'll need to confirm with the Oracle docs but from my knowledge it works through from tables without stats, then stale ones, etc. Yes, you could reduce the time taken for gatherhing stats by reducing the sampling, but your stats will be less accurate (potentially).
Degree will allow more parallel processes to run to gather the stats so it could help you. I say could because it might not...Like I said before it depends on how much resource you are already using.
First check your disk and CPU usage to see if they are capable of generating more throughput, which is what you'll need if you wnat it to run faster. If your server and disks can't handle it there isn't much you can do to make it quicker.
1) Use higher degree of parallelism. Check with your DBA, how many CPUs you have and set the DOP acordingly. If you have lot of data, it might take long time
2) Why don't you collect stats only for objects where stats is stale ?
3) Is it the case that, while stats are being collected, lot of underlying data is being updated/inserted/deleted ?
4) Generate AWR report for some of the time windows while stats is being collected and check wait events on the AWR report