How Know Elapsed Times For Statistics

From a long time ago many of us, maybe all people, in our scripts include routines or code to take time executions for gather of statistics, but it is no really necessary becouse of DBA_OPTSTAT_OPERATIONS view, it exits from release 11g. This view give us a timestamp of start and finish of the stat operations.

Let me show the next example.

 
COLUMN OPERATION FORMAT A30
COLUMN TARGET FORMAT A50

REM
REM Next query return all rows from DBA_OPTSTAT_OPERATIONS
REM 
SELECT 
  OPERATION, 
  TARGET, 
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
ORDER BY 3;

REM
REM Next query return all times for global database stats
REM 
SELECT  
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
WHERE OPERATION ='gather_database_stats(auto)'
ORDER BY 1;

REM
REM Next query return all times for a table
REM Remark: Target concat schema and object name
REM 
SELECT  
  OPERATION, 
  TARGET, 
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
WHERE TARGET ='REPORTS.TRACK_NAV_WEB'
ORDER BY 3;

Advantages;

– It is automatic.
– It is storage in a dba view, many user/process/third apps can use it.
– It make simple your personal scripts.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s