5 Replies Latest reply: May 14, 2013 7:15 AM by 926398 RSS

    How to monitor progress of dbms_stat.gather_database_stats

    926398
      We are running oracle 11.2.0.3 rdbms on AIX 6. We are using datapump to import a full database copy of a 2.5TB database that was exported with the exclude=statistics parameter. ONce the database import completes I have been asked to run the dbms_stat.gather_database_stats on the database and was wanting to know if there is any way to monitor this process to understand how far along it is or if there is any way to estimate how long this will take?
      I am guessing the only way to know is to run it and see but I am just getting pressure from mgmt to give an estimated time for this job to complete and I'm not sure what to tell them...
        • 1. Re: How to monitor progress of dbms_stat.gather_database_stats
          Alvaro
          As usual the answer is "it depends".

          It depends on your I/O throughput, CPU, level of paralelization you set for the job, some config options you can set on the job (if it will sample 100% or 20% of the segments for instance), even how the data is stored on the segments.

          I really don't see how you could provide a correct estimation for them. This is one of those things really hard to predict time-wise imo.

          The only thing you could get an estimate is if you run it, and monitor it through the v$session, and v$session_longops.
          • 2. Re: How to monitor progress of dbms_stat.gather_database_stats
            saratpvv
            You can monitor using v$session_longops - try this

            select x.job_name,b.state,b.job_mode,b.degree
            , x.owner_name,z.sql_text, p.message
            , p.totalwork, p.sofar
            , round((p.sofar/p.totalwork)*100,2) done
            , p.time_remaining
            from dba_datapump_jobs b
            left join dba_datapump_sessions x on (x.job_name = b.job_name)
            left join v$session y on (y.saddr = x.saddr)
            left join v$sql z on (y.sql_id = z.sql_id)
            left join v$session_longops p ON (p.sql_id = y.sql_id)
            WHERE y.module='Data Pump Worker'
            AND p.time_remaining > 0;
            • 3. Re: How to monitor progress of dbms_stat.gather_database_stats
              Hemant K Chitale
              You can't estimate the duration withour running the gather_database_stats. You can monitor it while it is running. (e.g. count the tables that have got updated statistics based on DBA_TABLES)

              And if you run gather_database_stats, the default behaviour might well be to exclude tables which aren't "stale" while in the imported database the expectation is to gather stats on all tables.
              (Similarly, the behaviour to gather column histograms (method_opt) may differ in an existing database from a newly imported database)

              Hemant K Chitale

              Edited by: Hemant K Chitale on May 14, 2013 10:13 AM
              • 4. Re: How to monitor progress of dbms_stat.gather_database_stats
                Suddhasatwa_Bhaumik
                Hello OP,

                If you have TOAD, you can connect to your database (by setting the proper TNS entry in TOAD's configuration) and see the progress of DBMS_STATS.GATHER_SCHEMA_STATS (or, by that means, any other active session) in the Sessions window of TOAD.

                Hope this helps.

                Thanks,
                Suddhasatwa
                • 5. Re: How to monitor progress of dbms_stat.gather_database_stats
                  926398
                  Thanks to all, very good answers wish I could give you all credit.