7 Replies Latest reply on Feb 19, 2013 2:25 PM by rahulras

    dbms_stats.gather_schema_stats running for long hours

      We are trying to run dbms_stats.gather_schema_stats procedure on our production DB. The parameters passed are as below.

      p_options= GATHER STALE

      The above parameters are passed to below proc.

      If p_gather_stats_cd='SCHEMA' then
      dbms_stats.gather_schema_stats (
      ownname => p_schema_name,
      estimate_percent => nvl(p_estimate_percent,DBMS_STATS.AUTO_SAMPLE_SIZE),
      method_opt => nvl(p_method_opt,'FOR ALL COLUMNS SIZE AUTO'),
      degree => nvl(p_degree,DBMS_STATS.DEFAULT_DEGREE),
      cascade => nvl(p_cascade,DBMS_STATS.AUTO_CASCADE),
      options => p_options,
      granularity => 'ALL'
      end if;

      The schema stats gathering is taking long hours to run. Mostly all default parameters are passed.
      It took 7 hours last week and 10 hours this week to gather entire schema stats?
      How can we reduce the time taken to run the stats gather job?
        • 1. Re: dbms_stats.gather_schema_stats running for long hours
          Please post your Server, OS and Database configuration.

          Pradip Patel
          • 2. Re: dbms_stats.gather_schema_stats running for long hours

            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 ;
            Ajay More
            • 3. Re: dbms_stats.gather_schema_stats running for long hours
              Check this thread
              DBMS_STATS.gather_schema_stats JOB was running slowly
              • 4. Re: dbms_stats.gather_schema_stats running for long hours
                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.
                • 5. Re: dbms_stats.gather_schema_stats running for long hours
                  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?
                  • 6. Re: dbms_stats.gather_schema_stats running for long hours

                    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 person found this helpful
                    • 7. Re: dbms_stats.gather_schema_stats running for long hours
                      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