3 Replies Latest reply: May 12, 2011 10:35 PM by Hemant K Chitale RSS

    table Gather stats time taken

    gl*325468*48
      Oracle : 11.2.0.2.0
      OS : AIX 6.0
      BIT : 64

      Issue : while Gather stats on a table we can get
      select table_name,LAST_ANALYZED from dba_tables

      This can give the time when it was completed. How to find the time taken for the table for gathering the stats ?
      is there any way we can get the time taken for the gather stats time taken ? so that we can plan the time for the next run from the dictionary tables?



      Pl. let me know

      Thanks in advance

      Edited by: gl**** on May 13, 2011 7:11 AM
        • 1. Re: table Gather stats time taken
          gl*325468*48
          I got the answer

          Ex :

          select TARGET,START_TIME ,END_TIME-START_TIME RUNTIME ,
          END_TIME END_TIME
          from dba_optstat_operations WHERE OPERATION='gather_table_stats'
          AND TARGET LIKE 'FACTS%' AND TRUNC(START_TIME) ='12-MAY-2011'
          ORDER BY 2
          • 2. Re: table Gather stats time taken
            gl*325468*48
            I got the answer

            Ex :

            select TARGET,START_TIME ,END_TIME-START_TIME RUNTIME ,
            END_TIME END_TIME
            from dba_optstat_operations WHERE OPERATION='gather_table_stats'
            AND TARGET LIKE 'FACTS%' AND TRUNC(START_TIME) ='12-MAY-2011'
            ORDER BY 2

            or

            select TARGET,to_char(START_TIME,'dd-mon-yyyy:HH:MI:SS') START_TIME ,END_TIME-START_TIME RUNTIME ,
            TO_CHAR(END_TIME,'dd-mon-yyyy:HH:MI:SS') END_TIME
            from dba_optstat_operations WHERE OPERATION='gather_table_stats'
            AND TARGET LIKE 'FACTS%' AND TRUNC(START_TIME) ='12-MAY-2011'
            ORDER BY 2
            • 3. Re: table Gather stats time taken
              Hemant K Chitale
              That won't work if a GATHER_SCHEMA_STATS has been executed --- you can get the Start and End times for the GATHER_SCHEMA_STATS but not the start and end times for individual tables in the schema. In this case, I query DBA_TABLES and order by the LAST_ANALYZED_DATA, and use an analytical function for the "dfifference"s.


              Hemant K Chitale

              Edited by: Hemant K Chitale on May 13, 2011 11:35 AM