20 August, 2013

Gather Statistics Enhancements in 12c -- 3

Here's how an enhancement to the DBMS_STATS package can generate reports :


SQL> exec dbms_stats.gather_schema_stats('HEMANT');

PL/SQL procedure successfully completed.

SQL> declare
  2  mystatsreport clob;
  3  begin
  4  mystatsreport := dbms_stats.report_stats_operations(
  5  since=>SYSTIMESTAMP-1,
  6  until=>SYSTIMESTAMP,
  7  detail_level=>'TYPICAL',
  8  format=>'TEXT');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

SQL> set long 100000
SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation      | Target | Start Time
    | End Time      | Status    | Total Tasks | Successful
 Tasks | Failed Tasks | Active Tasks  |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 623        | gather_schema_stats | HEMANT | 20-AUG-13 11.11.02.927041 PM +08
:00 | 20-AUG-13 11.11.06.505036 PM +08:00 | COMPLETED | 3     | 3
       | 0       | 0       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL> 
SQL> variable mystatrep2 clob;
SQL> set long 1000000
SQL> begin
  2  :mystatrep2 := dbms_stats.report_stats_operations(
  3  since=>SYSTIMESTAMP-16,
  4  until=>SYSTIMESTAMP-1,
  5  detail_level=>'TYPICAL',
  6  format=>'TEXT');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print mystatrep2
MYSTATREP2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation        | Target | Start Time
  | End Time      | Status    | Total Tasks | Successfu
l Tasks | Failed Tasks | Active Tasks |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 603        | purge_stats        |        | 11-AUG-13 12.40.53.9264
33 AM  | 11-AUG-13 12.40.54.321760 AM    | COMPLETED | 0      | 0
 | 0        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 583        | gather_database_stats (auto) | AUTO   | 11-AUG-13 12.35.42.5560
98 AM  | 11-AUG-13 12.40.53.926137 AM    | COMPLETED | 806      | 802
 | 4        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL> 

I can generate reports of Gather_Stats executions.
Note : It doesn't report the"automatic" Gather_Stats that I demonstrated on 06-Aug (for a CTAS) and on 11-Aug (for a Direct Path INSERT into an empty table).

.
.
.

No comments: