Search My Oracle Blog

Custom Search

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

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