Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016