Oracle Database -- NEW REPORTING SUBPROGRAMS IN DBMS_STATS PACKAGE

 

Knowing when and how to gather statistics in a timely manner is critical to maintain acceptable performance on any system.

Determining what statistics gathering operations are currently executing in an environment and how changes to the statistics methodology will impact the system can be difficult and time consuming.

Reporting subprograms in DBMS_STATS package make it easier to monitor what statistics gathering activities are currently going on and what impact changes to the parameter settings of these operations will have. 

The DBMS_STATS subprograms are  REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION and REPORT_GATHER_*_STATS.

 The report shows detailed information about what statistics gathering operations have occurred, during a specified time window. It gives details on when each operation occurred, its status, and the number of objects covered and it can be displayed in either text or HTML format.

COLUMN REPORT FORMAT A300

VARIABLE my_report CLOB;

BEGIN

:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (

  since => SYSDATE-7

, until => SYSDATE

, detail_level => 'TYPICAL'

, format => 'TEXT'

, auto_only => TRUE

);

END;

/

print my_report;

 variable mystatrep2 clob;

  set long 1000000

   begin

   :mystatrep2 := dbms_stats.report_stats_operations(

    since=>SYSTIMESTAMP-16,

    until=>SYSTIMESTAMP-1,

   detail_level=>'TYPICAL',

    format=>'TEXT'); 

  end;

   /

  print mystatrep2