Online Statistics Gathering has been introduced in 12c and is a very handy feature for ETL and batch jobs developers. However the devil is in the detail. There are some points to remember. Let’s take a closer look.<\/p>\n
Introduction <\/strong><\/p>\n If you are not familiar with the concept of this feature yet, here are some useful links:<\/p>\n But I’ll try to give a short introduction too. The official name of the feature is Online Statistics Gathering for Bulk Loads<\/strong>. So when exactly would the table statistics be automatically gathered and when not? <\/p>\n It’s quite clear with C<\/strong>reate T<\/strong>able A<\/strong>s S<\/strong>elect (CTAS): they would (if no restrictions apply). The next type of supported bulk load scenario is Parameter, Hints and Execution Plans<\/strong><\/p>\n There is an underscore parameter _optimizer_gather_stats_on_load<\/strong><\/em> which is TRUE by default. Should you want to disable the feature, this parameter can be set to FALSE. There are also two optimizer hints: How do you know whether online statistics will be gathered? Doing explain plan you will see an extra step But in fact you cannot know. This step only means the online statistics collection will be considered at runtime, because the optimizer doesn’t check whether the segment is really empty at parse time.<\/p>\n What about MERGE?<\/strong><\/p>\n This question comes in mind, because in fact the INSERT part of the MERGE will also be done using direct path write if we were using an APPEND hint or doing it in parallel. As an ETL scenario it is not uncommon to load only records that not already exist in the target. And since you don’t need to provide both WHEN MATCHED and WHEN NOT MATCHED (since 10g), one could construct a MERGE doing only an insert which, from my point of view, will be semantically the same as an INSERT INTO … AS SELECT with a NOT IN or NOT EXISTS subquery. The latter will gather statistics online. But what can we expect from MERGE? MERGE is not mentioned in the documentation, but in my opinion it’s worth checking. <\/p>\n Listing 1 – No Online Statistics Gathering with MERGE <\/strong><\/em><\/p>\n Unfortunately no. Despite of doing a direct path write into an empty table, MERGE doesn’t piggyback optimizer statistics. Of course it’s not a big problem, you should just be aware of it. In one of my recent projects I was wondering, why don’t my dimensions have any statistics after initial load? These dimensions were quite stable: once initially loaded they rarely got few new records. So I decided not to gather optimizer statistics after each load, but rely on standard Oracle statistics gathering job instead. And for initial load the Online Statistics Gathering will do the job, right? NO! Because the load process was implemented using MERGE. So I had two options: to re-implement the mappings using an INSERT or to explicitly gather optimizer statistics after initial load (or still after each load). Opted for the latter.<\/p>\n What does „empty“ exactly mean?<\/strong><\/p>\n An interesting question, quite relevant for designing ETL processes. If we plan to implement some intermediate tables and rely on online statistics gathering, how to ensure this works each time we repeat the load job? Let’s test it.<\/p>\n Listing 2 – when will the segment be considered empty?<\/strong><\/em><\/p>\n This example provides some quite interesting findings:<\/p>\n The only way we can rely on online statistics gathering in a repeatable process without dropping the table is to use TRUNCATE TABLE without an option or with explicit DROP STORAGE option before inserting new data.<\/p>\n The fact that the optimizer statistics are immediately available after INSERT statement is indeed documented. But the placement of this sentence at the end of 10.3.3.2 Global Statistics During Inserts into Empty Partitioned Tables <\/a> is IMHO wrong or at least understated. <\/p>\n Cursor invalidation<\/strong> Listing 3 – Cursor invalidation, the results are not consistent though!<\/strong><\/em><\/p>\n At this point I had strong doubt, whether it was still worth further investigation. I still believe the cursor will be invalidated, but is it a problem within an ETL process? In my opinion not really. The online statistics only kick in if loading into an empty segment. <\/p>\n Following cases are possible: <\/p>\n Some pitfalls and restrictions<\/strong><\/p>\n There are some restrictions documented<\/a>, but should you have doubts, keep in mind to re-read this section (and MOS Notes) for your version. There are better explanations in 12.2. Some restrictions were lifted, some of them where lifted even in 12.1 but this seems not to be reflected in the documentation.<\/p>\n What kind of statistics?<\/em><\/p>\n You get only table and basic column statistics. No histograms. No index statistics will be gathered. Recently I could observe a nasty side effect. There was an index on a big table which occasionally was empty at the time the standard statistics maintenance job was active. After that both table and index statistics were reflecting that fact (number or distinct keys = 0). And while table stats were re-gathered online, the index stats were not. And guess what? The optimizer has chosen to use index statistics for its cardinality estimates resulting in a nested loop. So you should cater for index statistics yourself.<\/p>\n Columns<\/em><\/p>\n Such a simple thing as not specifying all columns in INSERT prevents online statistics gathering:<\/p>\n Listing 4 – not all columns specified – no statistics gathering<\/strong><\/em><\/p>\n This was not mentioned at all in the 12.1 documentation. But 12.2 has a very good explanation. If all missing columns have default values online statistics gathering will kick in. Now I asked myself, if this works, why should virtual columns be a problem, as 12.1 documentation says? Tested it and indeed, it works with virtual columns even in 12.1 (Listing 5) <\/p>\n Listing 5 – columns with default values and virtual columns<\/strong><\/em><\/p>\n I should say, I don’t really understand, why not specifying a column which has no default value should be a problem. After all, if I gather statistics afterwards and some columns are completely empty, I’ll get statistics on them reflecting exactly that fact. <\/p>\n Extended Stats<\/em><\/p>\n There is a bug 18425876 – if you have created extended stats before filling the table, this prevents online statistics gathering. I could reproduce this bug only in 12.1.0.1 though. MOS states it was fixed in 12.2, but it seems it have been backported to 12.1.0.2 too.<\/p>\n Listing 6 – extended statistics prevent online statistics gathering<\/strong><\/em><\/p>\n In the second part<\/a> we’ll take a look at partitioning.<\/p>\n\n
INSERT INTO ... SELECT<\/strong><\/code> into an empty table<\/strong> using a direct path insert. That means, your INSERT should be used with a hint APPEND or should be done in parallel. Don’t forget to enable parallel DML at the session level or with a hint enable_parallel_dml<\/strong><\/em> – the parallel<\/em><\/strong> hint alone is not enough. <\/p>\nGATHER_OPTIMIZER_STATISTICS<\/strong><\/code> and NO_GATHER_OPTIMIZER_STATISTICS<\/strong><\/code>. Don’t misunderstand them! You cannot tell the optimizer to gather (piggyback) statistics online if the main condition (bulk insert into an empty segment) is not met. These hints are only useful in conjunction with the global setting via _optimizer_gather_stats_on_load<\/strong><\/em>: if set to TRUE, you can disable the feature for individual statements via NO_GATHER_OPTIMIZER_STATISTICS<\/strong><\/code> and vice versa, if globally set to FALSE, you can selectively enable piggybacking using GATHER_OPTIMIZER_STATISTICS<\/strong><\/code> hint.<\/p>\nOPTIMIZER STATISTICS GATHERING<\/strong><\/code>.<\/p>\n\r\n-------------------------------------------------\r\n| Id | Operation | Name |\r\n-------------------------------------------------\r\n| 0 | INSERT STATEMENT | |\r\n| 1 | LOAD AS SELECT | T2 |\r\n| 2 | OPTIMIZER STATISTICS GATHERING | |\r\n| 3 | TABLE ACCESS FULL | T |\r\n-------------------------------------------------\r\n<\/pre>\n
\r\n\r\nSQL> CREATE TABLE t_merge (id number, s varchar2(20))\r\nTable created.\r\n\r\nSQL> --- MERGE\r\nSQL> MERGE \/*+ append *\/ \r\nINTO t_merge t\r\nUSING (\r\n SELECT level AS id , lpad (level, 20, '0') s\r\n FROM dual\r\n CONNECT BY level <= 10000) q\r\nON (q.id = t.id)\r\nWHEN NOT MATCHED THEN INSERT VALUES (q.id, q.s)\r\n10000 rows merged.\r\n\r\n-- This error message means we were doing a bulk insert (direct path write) into T_MERGE\r\nSQL> SELECT count(*) FROM t_merge\r\n>> SELECT count(*) FROM t_merge\r\n *\r\nError at line 1\r\nORA-12838: cannot read\/modify an object after modifying it in parallel \r\n\r\nSQL> COMMIT\r\nCommit complete.\r\n\r\nSQL> -- but we don't have any statistics\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_MERGE')\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_MERGE \r\n1 row selected.\r\n\r\n<\/pre>\n\r\nSQL> CREATE TABLE t_delete (id NUMBER, s VARCHAR2(20))\r\nTable created.\r\nSQL> CREATE TABLE t_truncate_reuse (id NUMBER, s VARCHAR2(20))\r\nTable created.\r\nSQL> CREATE TABLE t_truncate_drop (id NUMBER, s VARCHAR2(20))\r\nTable created.\r\nSQL> CREATE TABLE t_rollback (id NUMBER, s VARCHAR2(20))\r\nTable created.\r\nSQL> INSERT \/*+ append *\/ INTO t_delete\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_truncate_drop \r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_truncate_reuse\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\nSQL> COMMIT\r\nCommit complete.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_rollback\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\n\r\nSQL> -- Statistics are published without COMMIT\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name = 'T_ROLLBACK'\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_ROLLBACK 14.04.18 10000\r\n1 row selected.\r\n\r\nSQL> ROLLBACK\r\nRollback complete.\r\n\r\nSQL> -- but after ROLLBACK they are gone\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_DELETE', 'T_ROLLBACK', \r\n 'T_TRUNCATE_DROP', 'T_TRUNCATE_REUSE')\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_TRUNCATE_REUSE 14.04.18 10000\r\nT_TRUNCATE_DROP 14.04.18 10000\r\nT_ROLLBACK \r\nT_DELETE 14.04.18 10000\r\n\r\n4 rows selected.\r\n\r\nSQL> -- now delete rows and truncate tables\r\nSQL> DELETE FROM t_delete\r\n10000 rows deleted.\r\nSQL> TRUNCATE TABLE t_truncate_drop DROP STORAGE\r\nTable truncated.\r\nSQL> TRUNCATE TABLE t_truncate_reuse REUSE STORAGE\r\nTable truncated.\r\n\r\nSQL> -- INSERT more data and check if statistics had been updated\r\nSQL> INSERT \/*+ append *\/ INTO t_delete\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 20000\r\n20000 rows created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_truncate_drop \r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 20000\r\n20000 rows created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_truncate_reuse\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 20000\r\n20000 rows created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_rollback\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 20000\r\n20000 rows created.\r\n\r\nSQL> COMMIT\r\nCommit complete.\r\n\r\nSQL> -- do we have the new statistics?\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name in ('T_DELETE', 'T_ROLLBACK', \r\n 'T_TRUNCATE_DROP', 'T_TRUNCATE_REUSE')\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_ROLLBACK \r\nT_DELETE 14.04.18 10000\r\nT_TRUNCATE_REUSE 14.04.18 10000\r\nT_TRUNCATE_DROP 14.04.18 20000\r\n\r\n4 rows selected.\r\n\r\n<\/pre>\n\n
INSERT \/*+ append*\/ <\/code>will not gather statistics.<\/li>\n
\nInitially I wanted to investigate the behavior concerning cursor invalidation, but I didn’t managed to get a consistent test results. If you are gathering object statistics via DBMS_STATS you have control over cursor invalidation using the parameter NO_INVALIDATE. With Online Statistics Gathering you have no control. With the following script I believed to prove that the cursor was invalidated immediately after the INSERT which gathered stats, even without committing. But running this some more times I saw that it was not invalidated every time. On the other hand, if I turned the INSERT to be a conventional load, the cursor was invalidated too, sometimes not. <\/p>\n\r\n\r\nSQL> set linesize 300 pages 10000\r\nSQL> column table_name format a30\r\nSQL> DROP table t_rollback purge\r\nTable dropped.\r\nSQL> CREATE TABLE t_rollback (id NUMBER, s VARCHAR2(20))\r\nTable created.\r\n\r\nSQL> -- we will check if this cursor will be invalidated\r\nSQL> SELECT \/* TEST_CURSOR_INVALIDATION *\/* FROM t_rollback\r\nno rows selected.\r\n\r\nSQL> COLUMN sql_id new_value sql_id\r\n\r\nSQL> -- find the cursor in the shared pool\r\nSQL> SELECT sql_id, LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS\r\nFROM v$sql s\r\nWHERE sql_text LIKE '%TEST_CURSOR_INVALIDATION%' \r\nAND UPPER(sql_text) NOT LIKE '%V$SQL%'\r\n\r\nSQL_ID LOADED_VERSIONS EXECUTIONS INVALIDATIONS\r\n------------- --------------- ---------- -------------\r\n027kac8gfsud8 1 1 0\r\n1 row selected.\r\n\r\nSQL> -- Insert data but not commit\r\nSQL> INSERT \/*+ append *\/ INTO t_rollback\r\nSELECT level AS id , lpad (level, 20, '0') s\r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\n\r\nSQL> -- the statistics are already published\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name = 'T_ROLLBACK'\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_ROLLBACK 14.04.18 10000\r\n1 row selected.\r\n\r\nSQL> -- and the Cursor has been invalidated\r\nSQL> SELECT sql_id, LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS\r\nFROM v$sql s\r\nWHERE sql_id = '027kac8gfsud8'\r\n\r\nSQL_ID LOADED_VERSIONS EXECUTIONS INVALIDATIONS\r\n------------- --------------- ---------- -------------\r\n027kac8gfsud8 1 1 1\r\n1 row selected.\r\n\r\nSQL> ROLLBACK\r\nRollback complete.\r\n\r\nSQL> -- statistics are gone after rollback\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name = 'T_ROLLBACK'\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_ROLLBACK \r\n1 row selected.\r\n\r\n<\/pre>\n
\n
\r\n\r\nSQL> CREATE TABLE t_not_all_columns (id number, s varchar2(20))\r\nTable created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO t_not_all_columns (ID)\r\nSELECT level AS id \r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10000 rows created.\r\n\r\nSQL> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_NOT_ALL_COLUMNS')\r\n\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_NOT_ALL_COLUMNS \r\n1 row selected.\r\n\r\n<\/pre>\n\r\n\r\n> SELECT * FROM v$version\r\nBANNER CON_ID\r\n-------------------------------------------------------------------------------- ----------\r\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0\r\nPL\/SQL Release 12.1.0.2.0 - Production 0\r\nCORE\t12.1.0.2.0\tProduction 0\r\nTNS for Linux: Version 12.1.0.2.0 - Production 0\r\nNLSRTL Version 12.1.0.2.0 - Production 0\r\n\r\n> ----------------\r\n> --not all columns\r\n> DROP table t_not_all_columns purge\r\n\r\ntable T_NOT_ALL_COLUMNS dropped.\r\n\r\n> CREATE TABLE t_not_all_columns \r\n( id number\r\n, s varchar2(20))\r\n\r\ntable T_NOT_ALL_COLUMNS created.\r\n\r\n> INSERT \/*+ append *\/ INTO t_not_all_columns (ID)\r\nSELECT level AS id \r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10,000 rows inserted.\r\n\r\n> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_NOT_ALL_COLUMNS')\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_NOT_ALL_COLUMNS \r\n\r\n> -----------\r\n> --default\r\n> DROP table t_not_all_columns_default purge\r\n\r\ntable T_NOT_ALL_COLUMNS_DEFAULT dropped.\r\n\r\n> CREATE TABLE t_not_all_columns_default \r\n( id number\r\n, s varchar2(20) default 'ABC')\r\n\r\ntable T_NOT_ALL_COLUMNS_DEFAULT created.\r\n\r\n> INSERT \/*+ append *\/ INTO t_not_all_columns_default (ID)\r\nSELECT level AS id \r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10,000 rows inserted.\r\n\r\n> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_NOT_ALL_COLUMNS_DEFAULT')\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_NOT_ALL_COLUMNS_DEFAULT 29-MAY-18 10000\r\n\r\n> ----------\r\n> --virtual\r\n> DROP table t_not_all_columns_virtual purge\r\n\r\ntable T_NOT_ALL_COLUMNS_VIRTUAL dropped.\r\n\r\n> CREATE TABLE t_not_all_columns_virtual \r\n( id number\r\n, s varchar2(20) generated always as ('ABC') virtual)\r\n\r\ntable T_NOT_ALL_COLUMNS_VIRTUAL created.\r\n\r\n> INSERT \/*+ append *\/ INTO t_not_all_columns_virtual (ID)\r\nSELECT level AS id \r\nFROM dual\r\nCONNECT BY level <= 10000\r\n10,000 rows inserted.\r\n\r\n> SELECT table_name, last_analyzed, num_rows\r\nFROM user_tables \r\nWHERE table_name IN ('T_NOT_ALL_COLUMNS_VIRTUAL')\r\nTABLE_NAME LAST_ANALYZED NUM_ROWS\r\n------------------------------ ------------- ----------\r\nT_NOT_ALL_COLUMNS_VIRTUAL 29-MAY-18 10000\r\n \r\n<\/pre>\n\r\nSQL> SELECT * FROM v$version;\r\n\r\nBANNER CON_ID \r\n-------------------------------------------------------------------------------- ---------- \r\nOracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 \r\n...\r\nSQL> \r\nSQL> \r\nSQL> DROP table t_ext_stats purge;\r\n\r\nTable dropped.\r\n\r\nSQL> \r\nSQL> CREATE TABLE t_ext_stats\r\n 2 ( id number\r\n 3 , s varchar2(20));\r\n\r\nTable created.\r\n\r\nSQL> \r\nSQL> variable x varchar2(50)\r\nSQL> \r\nSQL> exec :x:= DBMS_STATS.CREATE_EXTENDED_STATS(user, 'T_EXT_STATS','(ID,S)')\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> \r\nSQL> INSERT \/*+ append *\/ INTO\t t_ext_stats (ID, S)\r\n 2 SELECT level AS id, 'ABC'\r\n 3 FROM dual\r\n 4 CONNECT BY level <= 10000;\r\n\r\n10000 rows created.\r\n\r\nSQL> \r\nSQL> \r\nSQL> SELECT table_name, last_analyzed, num_rows\r\n 2 FROM user_tables\r\n 3 WHERE table_name IN ('T_EXT_STATS');\r\n\r\nTABLE_NAME LAST_ANAL NUM_ROWS \r\n------------------------------ --------- ---------- \r\nT_EXT_STATS \r\n<\/pre>\nRelated Posts<\/h3>