This discussion is archived
3 Replies Latest reply: May 12, 2011 8:35 PM by Hemant K Chitale RSS

table Gather stats time taken

gl*325468*48 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points