By Franck Pachot
.
I’ve written about 12c online statistics gathering in a UKOUG OracleScene article. My opinion is clear about it: you sill need to gather stale stats afterwards or you have mising, stale and inconsistent object statistics. This post is about cases where online statistics gathering does not occur (and are not documented) – which is another reason why we can’t rely on it.
The case where it works
You can check on the article about how online gathering statistics works (or come to our 12c new feature workshop where we cover and practice all 12c optimizer new features)
In order to do something else here I’m showing how to trace it by activating the 0x10000 trace flag for dbms_stats:
SQL> connect demo/demo@//localhost/PDB1
Connected.
SQL> set serveroutput on
SQL> exec dbms_stats.set_global_prefs('TRACE',1+65536);
PL/SQL procedure successfully completed.
SQL> drop table DEMO;
Table dropped.
SQL> create table DEMO ( n number ) pctfree 99;
Table created.
SQL> insert /*+ append */ into DEMO select rownum from dual connect by 1000>=level;
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 1 1 1 to_char(count("N")) 100
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 2 1 1 substrb(dump(min("N"),16,0,64),1,240) 9
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 3 1 1 substrb(dump(max("N"),16,0,64),1,240) 17
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 1 1 1 to_char(count("N")) 100
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 2 1 1 substrb(dump(min("N"),16,0,64),1,240) 9
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 3 1 1 substrb(dump(max("N"),16,0,64),1,240) 17
DBMS_STATS: postprocess online optimizer stats gathering for DEMO.DEMO: save statis
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 1 1 1000 1000 0 2891 1000
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 2 2 Typ=2 Len=2: c1,2 NULL NULL
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 3 3 Typ=2 Len=2: c2,b NULL NULL
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 1 1 1 to_char(count("N")) 100
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 2 1 1 substrb(dump(min("N"),16,0,64),1,240) 9
DBMS_STATS: SELMAPPOS CLISTIDX INTCOL SELITEM GATHFLG
DBMS_STATS: ------------------------------------------------------------------------
DBMS_STATS: 3 1 1 substrb(dump(max("N"),16,0,64),1,240) 17
1000 rows created.
From the trace, online statistics gathering occured for that direct-path load.
We can see it also in the execution plan:
SQL> select * from table(dbms_xplan.display_cursor('1k2r9n41c7xba'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID 1k2r9n41c7xba, child number 0
-------------------------------------
insert /*+ append */ into DEMO select rownum from dual connect by 1000>=level
Plan hash value: 1600317434
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 2 (100)| |
| 1 | LOAD AS SELECT | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | |
| 4 | CONNECT BY WITHOUT FILTERING | | | | |
| 5 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
and statistics are there:
SQL> select last_analyzed,num_rows,blocks from user_tables where table_name='DEMO';
LAST_ANAL NUM_ROWS BLOCKS
--------- ---------- ----------
21-FEB-15 1000 179
Don’t forget to set the trace off:
SQL> exec dbms_stats.set_global_prefs('TRACE',0);
PL/SQL procedure successfully completed.
Ok. That is the known case. Table statistics are there.
startup restrict
When you want to do some online maintenance, being sure that the application is not connected, you start the database in restrict mode.
SQL> alter system enable restricted session;
System altered.
Then you can do you imports, reorg, bulk load, etc. and be sure that nobody will write or read into the table you are working on. Imagine you have tested the previous load and you have observed that the online gathered statistics are sufficient. Now you run the same in production in restricted mode.
SQL> connect demo/demo@//localhost/PDB1
Connected.
SQL> set serveroutput on
SQL> exec dbms_stats.set_global_prefs('TRACE',1+65536);
PL/SQL procedure successfully completed.
SQL> drop table DEMO;
Table dropped.
SQL> create table DEMO ( n number ) pctfree 99;
Table created.
SQL> insert /*+ append */ into DEMO select rownum from dual connect by 1000>=level;
1000 rows created.
No trace related to online statistics gathering.
SQL> select * from table(dbms_xplan.display_cursor('1k2r9n41c7xba'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 1k2r9n41c7xba, child number 0
-------------------------------------
insert /*+ append */ into DEMO select rownum from dual connect by 1000>=level
Plan hash value: 1600317434
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 2 (100)| |
| 1 | LOAD AS SELECT | | | | |
| 2 | COUNT | | | | |
| 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
no STATISTICS GATHERING operation.
SQL> select last_analyzed,num_rows,blocks from user_tables where table_name='DEMO';
LAST_ANAL NUM_ROWS BLOCKS
--------- ---------- ----------
and no statistics.
10053 trace
Because we can’t see the STATISTICS GATHERING operation in the execution plan, I know that it’s an optimizer decision done at compilation time. I’ve dump the 10053 trace and got the following line:
ONLINEST: Checking validity of online stats gathering
ONLINEST: Failed validity check: database not open, in restricted/migrate mode, suspended, readonly, instance not open or OCI not available.
So we have a few reasons where online statistics does not occur and that are not documented as Restrictions for Online Statistics Gathering for Bulk Loadsand restricted mode is one of them.
Thin JDBC
Because the preceding line mentions OCI I wanted to be sure that online statistics gathering occurs even when connected though thin jdbc, and I used the sqlcl beta from SQL Developer 4.1 Early Adopter. Note that I’m not in restricted session anymore.
sql.bat demo/demo@//192.168.78.113/pdb1
SQLcl: Release 4.1.0 Beta on Sat Feb 21 21:10:59 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> show jdbc
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Major Version: 12
Database Minor Version: 1
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.1.0.2.0
Driver Major Version: 12
Driver Minor Version: 1
Driver URL: jdbc:oracle:thin:@//192.168.78.113/pdb1
SQL> create table DEMO ( n number ) pctfree 99;
Table DEMO created.
SQL> insert /*+ append */ into DEMO select rownum from dual connect by level select last_analyzed,num_rows,blocks from user_tables where table_name='DEMO';
LAST_ANALYZED NUM_ROWS BLOCKS
--------------------------- ---------- ----------
21.02.15 1000 100
Ok. no problem. I don’t know what that ‘OCI not available’ is but it works even though JDBC Thin.
Conclusion
As I already said for other reasons, don’t rely on online statistics gathering and always gather stale stats afterwards. It’s good to have it as it saves some work to do by dbms_stats later. There are cases where it is better than no statistics (when combined with GTT private statistics for example) but don’t rely on it. but don’t rely on it.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)