15 October, 2013

Gather Statistics Enhancements in 12c -- 4

In 11gR2, I have demonstrated how Gather_Stats on a Global Temporary Table defined as ON COMMIT DELETE ROWS actually does a COMMIT before gathering statistics thus actually deleting the rows before gathering statistics.

The behaviour has changed in 12c.  Here's a demo :
[oracle@oel6 ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 15 15:42:21 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
Last Successful login time: Tue Aug 20 2013 22:57:13 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create global temporary table MY_GTT_DELETE_12c
  2  (object_id number, object_name varchar2(30)) on commit DELETE rows;

Table created.

SQL> insert into MY_GTT_DELETE_12c 
  2  select object_id, object_name from dba_objects
  3  where object_id is not null;

91491 rows created.

SQL> select count(*) from MY_GTT_DELETE_12c;

  COUNT(*)
----------
     91491

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_DELETE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_DELETE_12C;

  COUNT(*)
----------
     91491

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_DELETE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL> 
SQL> create global temporary table
  2  MY_GTT_PRESERVE_12C
  3  (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL> insert into MY_GTT_PRESERVE_12C
  2  select object_id, object_name
  3  from dba_objects
  4  where object_id is not null;

91492 rows created.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_PRESERVE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_PRESERVE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL> 

Thus, the rows were "preserved" even though DBMS_STATS normally issues a COMMIT when it begins.  Apparently, additional code has been created to handle GTT.  Unfortunately, this means that DBMS_STATS does not "see" the rows -- now in both cases of ON COMMIT DELETE / PRESERVE rows.  Oracle's design is to have "private" statistics on GTTs.
.
.
.

1 comment:

Anonymous said...

Thanks Hemant Nice posts.
Here is the link for 12c Post on my website. Please see and share if you feel useful.

http://www.oracle-info.com/12c-database/

Thanks
Sureshgandhi