Oracle Database 12c: Statistics created as the data was loaded
Mahir M. Quluzade (noreply@blogger.com)发表于 2015-08-04 18:17:24
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
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:
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.
Load a table
Conclusion
Gathering table statistics automatically as the table data was loaded in Oracle Database 12c.
Regards
Mahir M. Quluzade
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