Oracle Database 12c: Statistics created as the data was loaded

As you know, Oracle Magazine July/Aug 2015 issue released. You can free subscribe to Oracle Magazine digital edition and you can read here or you can read technical  articles here.

I subscribed Oracle Magazine, in current issue have very interesting articles.

One of interesting article of Oracle Expert Tomas Kyte (as known AskTom) with title: On Learning from Mistakes


In this article Tom talks about Cost Based Optimizer and shown how optimizer learning from mistakes. I don't want review this article, but there have about interesting new feature of Oracle Database 12c.   

Tom wrote:

The fact that the NOW column of the query against USER_TABLES is the same as the LAST_ANALYZED column shows that statistics were created as the data was loaded—this is a new feature of Oracle Database 12c. Since Oracle Database 10g, the database has been computing statistics automatically for indexes when you create or rebuild them. With Oracle Database 12c, the database does the same for tables. Further, as you can see in the example in Listing 1, there are basic statistics on the columns in the table as well.

I want share my test environments outputs with you:


[oracle@oel62-ora12c2 Desktop]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 Desktop]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 4 17:32:00 2015

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


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

SQL> select banner from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select name,cdb from v$database;

NAME   CDB
--------- ---
PRMCDB   YES

SQL> select name, open_mode from v$pdbs
  2  ;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         READ WRITE
PRMPDB03         READ WRITE

SQL> conn mahir/mahir@prmpdb02
Connected.
SQL> select table_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_tables;

no rows selected

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_indexes;

no rows selected

Not any table or index created. I will try create a table with index and check last analyzed column in user_tables and user_indexes tables.

SQL> create table t (n number, v varchar2(100));    

Table created.

SQL> insert into t select level, 'Num :'||level from dual connect by level<1e6;

999999 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed, num_rows from user_tables;

TABLE_NAME   LAST_ANALYZED        NUM_ROWS
------------------------ ------------------- ----------
T

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_indexes;

no rows selected

SQL> alter table t add primary key (n); 

Table altered.

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_indexes;

INDEX_NAME        LAST_ANALYZED
------------------------ -------------------
SYS_C009994   04.08.2015 17:45:27

SQL> insert into t select n + 1e6, 'Num:'||to_char(n+1e6) from t;

999999 rows created.

SQL> commit;

Commit complete.

SQL> alter index SYS_C009994 rebuild;

Index altered.

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_indexes;

INDEX_NAME  LAST_ANALYZED
----------------------- -------------------
SYS_C009994  04.08.2015 17:50:10


Load a table

SQL> select table_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed, num_rows from user_tables;

TABLE_NAME   LAST_ANALYZED        NUM_ROWS
------------------------ ------------------- ----------
T

SQL> create table T1 as select *  from t;

Table created.

SQL> select table_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed, num_rows from user_tables;

TABLE_NAME   LAST_ANALYZED        NUM_ROWS
------------------------ ------------------- ----------
T
T1    04.08.2015 17:52:11 1999998

SQL> create index idx_t1n on t1(n);

Index created.

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed from user_indexes;

INDEX_NAME   LAST_ANALYZED
------------------------ -------------------
SYS_C009994   04.08.2015 17:50:10
IDX_T1N    04.08.2015 17:54:37



Conclusion 

Gathering table statistics automatically as the table data was loaded in Oracle Database 12c.


Regards
Mahir M. Quluzade



Comments

Popular posts from this blog

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

Oracle Database 12c Release 2 - SQL*Plus History Command

Oracle Database 18c - Install On-Premises for Linux