I have started to execute gathering statistics for one of my schema :
DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'DWH', CASCADE=>TRUE,ESTIMATE_PERCENT=>10);
My db size is only 20 GB. But the procedure is still running for 7 hours. Can any one suggest how i can find why the task taking so much time or how to monitor the running procedure???
And ,what does the last two string means by CASCADE=>TRUE,
Cascade => true means it will gather stats for all dependent segments linke indexes/constrats/pk/FK.
estimate_percent is the sampling ratio considered while gathering statistics , passing 100% optimizer will consider all number of rows for sampling.
Try to find out the wait event of that particular session
Also try flushing shared_pool before running stats
col sql_text for a80
set pages 200
set line 900
col PROGRAM for a20
col MACHINE for a20
col CPU 9999999999
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
col event for a40
from (select /*+ rule */
s.last_call_et / 60,
from v$sqlarea sa, v$session s, v$process vp, v$session_wait sw
where sa.address = s.sql_address
and sw.sid = s.sid
and s.paddr = vp.addr
order by CPU_TIME desc)
where rownum < 10;
identify the session gathering stats and see what it is waiting on using V$SESSION. Could be a library cache lock or something similar holding the whole thing up. If it's I/O-related waits then you'll probably need to tune the stats jobs parameters.