{"id":946,"date":"2018-11-04T23:26:32","date_gmt":"2018-11-04T21:26:32","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=946"},"modified":"2018-11-04T23:32:44","modified_gmt":"2018-11-04T21:32:44","slug":"online-statistics-gathering-for-etl-part-3","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/online-statistics-gathering-for-etl-part-3\/","title":{"rendered":"Online Statistics Gathering for ETL \u2013 Part 3"},"content":{"rendered":"

Preparing my session on Online Statistics Gathering for ETL for the DOAG conference<\/a>, I noticed some points that I didn’t covered in the previous two blog posts. The first point is showing the problem that may arise if indexes are involved. The second one is about partition exchange load and it completes the topic of partitioned tables started in part 2. No blog posting on Oracle products is nowadays complete without mentioning the cloud. The third point is about Autonomous Data Warehouse Cloud Service and Online Statistics Gathering improvements.<\/p>\n

Don’t forget your indexes!<\/strong><\/p>\n

Had you also had the situation that after a failure the ETL processes suddenly became significantly slower? The following may have been the reason.<\/p>\n

With online statistics gathering only table and base column statistics are gathered. If you have indexes you should care of them on your own. What does it mean for ETL? Well, the first best practice says, don’t overindex your DWH. If you considered dropping an index and still opted for it, then it is highly likely that you are already following another best practice for bulk loads into a table\/partition: drop or set unusable your indexes\/index partitions before loading and recreate\/rebuild them afterwards. Index statistics will be gathered automatically (yes, for indexes the online statistics gathering was introduced already in 9i). Otherwise make sure, you will never gather index stats on empty table before loading. In this case the older index statistics are better then newer (but empty).<\/p>\n

Consider following example. <\/p>\n

\"\"<\/a>
\n
\nFigure 2: Index statistics are lost because of DBMS_STATS run in between<\/em><\/p>\n

You have a table with index, containing data, with both table and index stats in place. You then truncate the table, but for some reason don’t load new data immediately after that (failure in ETL process or maybe you are yet testing and haven’t yet automated everything) . Truncate doesn’t throw away table and index statistics. But what if statistics on the table and index will be gathered by the regular DBMS_STATS job during the maintenance window? After that NUM_ROWS for the table and also NUM_DISTINCT_KEYS for the index will be 0. After loading we will get newer stats for the table via online statistics gathering, but unfortunately not for the index. And guess what the optimizer will do with the index? It will obviously base its cardinality estimation on it and use the index!<\/p>\n

\r\nSQL> create table t_osg ( n number , n2 number default 1, pad varchar2(300));\r\n\r\nTable T_OSG created.\r\n\r\nSQL> insert \/*+ append *\/ into t_osg \r\n  2  select level n, \r\n  3         case when mod(level, 3) = 0 then null else  mod(level, 3)  end,\r\n  4         rpad ('***************',300,'\/') \r\n  5  from   dual connect by level <= 100000;\r\n\r\n100,000 rows inserted.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> -- Second table as a copy of the first \r\nSQL> create table t_osg2 as select * from t_osg;\r\n\r\nTable T_OSG2 created.\r\n\r\nSQL> create unique index t1_i on t_osg (n);\r\n\r\nIndex T1_I created.\r\n\r\nSQL> select index_name, distinct_keys, last_analyzed \r\n  2  from user_indexes i where index_name = 'T1_I';\r\n\r\nINDEX_NAME                     DISTINCT_KEYS LAST_ANALYZED      \r\n------------------------------ ------------- -------------------\r\nT1_I                                  100000 28.10:2018 06:46:14\r\n\r\nSQL> \r\nSQL> select table_name, num_rows, last_analyzed \r\n  2  from user_tables \r\n  3  where table_name like 'T_OSG%';\r\n\r\nTABLE_NAME                       NUM_ROWS LAST_ANALYZED      \r\n------------------------------ ---------- -------------------\r\nT_OSG                              100000 28.10:2018 06:46:11\r\nT_OSG2                             100000 28.10:2018 06:46:13\r\n\r\nSQL> \r\nSQL> explain plan for \r\n  2  select  t1.n, t2.n2, t1.pad ppp1 \r\n  3  from    t_osg t1 join  t_osg2 t2 on t1.n = t2.n;\r\n\r\nExplained.\r\n\r\nSQL> \r\nSQL> select * from table(dbms_xplan.display);\r\n\r\nPlan hash value: 3216524395\r\n \r\n-------------------------------------------------------------------------------------\r\n| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |        |   100K|    29M|       |  4101   (1)| 00:00:01 |\r\n|*  1 |  HASH JOIN         |        |   100K|    29M|  1960K|  4101   (1)| 00:00:01 |\r\n|   2 |   TABLE ACCESS FULL| T_OSG2 |   100K|   781K|       |  1251   (1)| 00:00:01 |\r\n|   3 |   TABLE ACCESS FULL| T_OSG  |   100K|    29M|       |  1251   (1)| 00:00:01 |\r\n-------------------------------------------------------------------------------------\r\n \r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n \r\n   1 - access("T1"."N"="T2"."N")\r\n\r\n15 rows selected. \r\n\r\n<\/pre>\n
Listing 1: Setup for the test case. The normal execution plan contains a hash join.<\/em><\/p>\n
Create two tables T_OSG<\/strong> and T_OSG2<\/strong> with an index on T_OSG<\/strong>, verify that both tables and index have statistics. Verify the execution plan for the join of two tables: it is a hash join, doesn’t use the index and the estimations are accurate. Now let’s simulate the problem:<\/p>\n
\r\n\r\nSQL> --- Truncate the first table T_OSG\r\nSQL> truncate table t_osg;\r\n\r\nTable T_OSG truncated.\r\n\r\nSQL> \r\nSQL> -- The standard oracle job runs in the maintenace window \r\nSQL> begin\r\n  2    dbms_stats.gather_table_stats(ownname=> user, tabname=>'T_OSG' );\r\n  3  end;\r\n  4  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> \r\nSQL> -- Stats are gone\r\nSQL> select index_name, distinct_keys, last_analyzed \r\n  2  from user_indexes i where index_name = 'T1_I';\r\n\r\nINDEX_NAME                     DISTINCT_KEYS LAST_ANALYZED      \r\n------------------------------ ------------- -------------------\r\nT1_I                                       0 28.10:2018 06:46:16\r\n\r\nSQL> \r\nSQL> select table_name, num_rows, last_analyzed \r\n  2  from user_tables \r\n  3  where table_name like 'T_OSG%';\r\n\r\nTABLE_NAME                       NUM_ROWS LAST_ANALYZED      \r\n------------------------------ ---------- -------------------\r\nT_OSG2                             100000 28.10:2018 06:46:13\r\nT_OSG                                   0 28.10:2018 06:46:15\r\n\r\nSQL> \r\nSQL> -- Load data once more\r\nSQL> \r\nSQL> insert \/*+ append *\/ into t_osg \r\n  2  select level n, \r\n  3         case when mod(level, 3) = 0 then null else  mod(level, 3)  end,\r\n  4         rpad ('***************',300,'\/') \r\n  5  from   dual connect by level <= 100000;\r\n\r\n100,000 rows inserted.\r\n\r\nSQL> \r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> \r\nSQL> -- table stats are there, but not the index stats\r\nSQL> select index_name, distinct_keys, last_analyzed \r\n  2  from user_indexes i where index_name = 'T1_I';\r\n\r\nINDEX_NAME                     DISTINCT_KEYS LAST_ANALYZED      \r\n------------------------------ ------------- -------------------\r\nT1_I                                       0 28.10:2018 06:46:16\r\n\r\nSQL> \r\nSQL> select table_name, num_rows, last_analyzed \r\n  2  from user_tables \r\n  3  where table_name like 'T_OSG%';\r\n\r\nTABLE_NAME                       NUM_ROWS LAST_ANALYZED      \r\n------------------------------ ---------- -------------------\r\nT_OSG2                             100000 28.10:2018 06:46:13\r\nT_OSG                              100000 28.10:2018 06:46:17\r\n\r\nSQL> \r\nSQL> \r\nSQL> explain plan for \r\n  2  select  t1.n, t2.n2, t1.pad ppp2 \r\n  3  from    t_osg t1 join  t_osg2 t2 on t1.n = t2.n;\r\n\r\nExplained.\r\n\r\nSQL> \r\nSQL> select * from table(dbms_xplan.display);\r\n\r\nPlan hash value: 3291465260\r\n \r\n---------------------------------------------------------------------------------------\r\n| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT             |        |   100K|    29M|  1254   (1)| 00:00:01 |\r\n|   1 |  NESTED LOOPS                |        |   100K|    29M|  1254   (1)| 00:00:01 |\r\n|   2 |   NESTED LOOPS               |        |   100K|    29M|  1254   (1)| 00:00:01 |\r\n|   3 |    TABLE ACCESS FULL         | T_OSG2 |   100K|   781K|  1251   (1)| 00:00:01 |\r\n|*  4 |    INDEX UNIQUE SCAN         | T1_I   |     1 |       |     0   (0)| 00:00:01 |\r\n|   5 |   TABLE ACCESS BY INDEX ROWID| T_OSG  |     1 |   306 |     0   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------\r\n \r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n \r\n   4 - access("T1"."N"="T2"."N")\r\n\r\n17 rows selected. \r\n\r\n<\/pre>\n
Listing 2: switched to nested loops because of index statistics<\/em> <\/p>\n
After truncating the table and gathering statistics on it, the index statistics are indicating the index is empty. As expected, after repeated direct-path load into the table the index statistics are not refreshed. How does the execution plan for the join look like now? The empty index seems to be very appealing for the optimizer as access path for the table T_OSG<\/strong> leading to Nested Loops as join method.  <\/p>\n
Bulk Load via Partition Exchange <\/strong><\/p>\n
In the second part of the OSG series<\/a> we’ve considered different scenarios of bulk loading data into partitioning tables, except  for the case of partition exchange loading, which is in fact a very efficient way of loading mass data. With partition exchange load (PEL) you are loading data into a separate table created especially for exchange. After load this table will be “exchanged”  with the desired partition  without copying of data, just data dictionary operation. You know, in 12c, if you are loading data into an empty exchange table via direct path load, its statistics will be gathered online. But how about partitioned table after exchange operation? <\/p>\n
\"\"<\/a><\/p>\n
Figure 1: Partition Exchange Load <\/em><\/p>\n
Let’s start creating a partitioned table T_PART<\/strong>, setting the INCREMENTAL<\/strong> preference for global statistics gathering to be TRUE<\/strong>, insert some data into one particular partition (P2) and gather table statistics. We can see partition level and global statistics:<\/p>\n
\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\n  2  partition by range (pkey)\r\n  3  (partition P0 values less than (0),\r\n  4  partition P1 values less than (1),\r\n  5  partition P2 values less than (2),\r\n  6  partition P3 values less than (3));\r\n\r\nTable T_PART created.\r\n\r\nSQL> exec dbms_stats.set_table_prefs(null,'T_PART','INCREMENTAL','TRUE')\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO   t_part PARTITION (P2)\r\n  2  SELECT 1 pkey, level AS id , lpad (level, 20, '0') s\r\n  3  FROM   dual\r\n  4  CONNECT BY level <= 10000;\r\n\r\n10,000 rows inserted.\r\n\r\nSQL> COMMIT;\r\n\r\nCommit complete.\r\n\r\nSQL> begin\r\n  2     dbms_stats.gather_table_stats (null,'T_PART');\r\n  3  end;\r\n  4  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> SELECT table_name, partition_name, global_stats, last_analyzed, num_rows\r\n  2  FROM   user_tab_statistics s\r\n  3  WHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART     P3    YES   28.10:2018 08:26:33          0\r\nT_PART     P0    YES   28.10:2018 08:26:33          0\r\nT_PART           YES   28.10:2018 08:26:33      10000\r\nT_PART     P2    NO    28.10:2018 08:26:33      10000\r\nT_PART     P1    YES   28.10:2018 08:26:33          0\r\n\r\n<\/pre>\n
Listing 3: Setup for partition exchange<\/em><\/p>\n
Now let’s load data with partition exchange. First, we need a non-partitioned table with identical structure as our target table. Fortunately, if you are on 12.2, you can use just one DDL-command for this (line  2). It’s a new feature, see more in Oracle SQL Reference<\/a> and also on Dani Schnider’s Blog.<\/a> Also new in 12c (12.1) is the possibility to calculate synopsis on non-partitioned tables. To do so we have to set preferences INCREMENTAL<\/strong> to TRUE<\/strong> and  INCREMENTAL_LEVEL<\/strong> to TABLE<\/strong> for our newly created table T_EXCHANGE<\/strong> (lines 6-10). After direct-path load into T_EXCHANGE<\/strong> we are now having table and basic column statistics along with synopsis (lines 25-50)<\/p>\n
\r\nSQL> -- create a table for exchange (12.2 feature)\r\nSQL> CREATE TABLE t_exchange FOR EXCHANGE WITH TABLE t_part;\r\n\r\nTable T_EXCHANGE created.\r\n\r\nSQL> BEGIN\r\n  2     dbms_stats.set_table_prefs (null,'t_exchange','INCREMENTAL','TRUE');\r\n  3     dbms_stats.set_table_prefs (null,'t_exchange','INCREMENTAL_LEVEL','TABLE');\r\n  4  END;\r\n  5  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO   t_exchange \r\n  2  SELECT 2 pkey, level AS id , lpad (level, 20, '0') s\r\n  3  FROM   dual\r\n  4  CONNECT BY level <= 10000;\r\n\r\n10,000 rows inserted.\r\n\r\nSQL> COMMIT;\r\n\r\nCommit complete.\r\n\r\nSQL> SELECT table_name, partition_name, global_stats, last_analyzed, num_rows\r\n  2  FROM   user_tab_statistics s\r\n  3  WHERE  table_name IN ('T_EXCHANGE');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_EXCHANGE       NO    28.10:2018 08:37:39      10000\r\n\r\nSQL> -- Check Synopsis\r\nSQL> SELECT o.name         Table_Name,\r\n  2         c.name         "Column",\r\n  3         h.analyzetime  "Synopsis Creation Time",\r\n  4         h.bo#\r\n  5  FROM   sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,\r\n  6         sys.OBJ$ o,\r\n  7         sys.COL$ c\r\n  8  WHERE  o.name = 'T_EXCHANGE' AND\r\n  9         h.bo# = c.obj#(+) AND\r\n 10         h.intcol# = c.intcol#(+) AND\r\n 11         h.bo# = o.obj#\r\n 12  ;\r\n\r\nTABLE_NAME Colum Synopsis Creation T        BO#\r\n---------- ----- ------------------- ----------\r\nT_EXCHANGE PKEY  28.10:2018 08:37:39      74603\r\nT_EXCHANGE ID    28.10:2018 08:37:39      74603\r\nT_EXCHANGE S     28.10:2018 08:37:39      74603\r\n\r\n<\/pre>\n
Listing 4: Load data into exchange table and check the gathered statistics and synopsis<\/em><\/p>\n
Now we are ready to exchange the table with partition and see what will happen with the statistics. Right after exchange operation we can see partition-level statistics for the new partition: they were just copied from the exchange table. Re-gather table statistics and we see also global statistics being updated (20000 rows). This happens without full scan of the  table T_PART<\/strong>, just using the copied synopsis of the T_EXCHANGE<\/strong> (see highlighted lines 75-77)<\/p>\n
\r\n\r\nSQL> -- Exchange the table with partition\r\nSQL> \r\nSQL> ALTER TABLE T_PART EXCHANGE PARTITION P3 WITH TABLE t_exchange\r\n  2  INCLUDING INDEXES WITHOUT VALIDATION;\r\n\r\nTable T_PART altered.\r\n\r\nSQL> SELECT table_name, partition_name, global_stats, last_analyzed, num_rows\r\n  2  FROM   user_tab_statistics s\r\n  3  WHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           YES   28.10:2018 08:26:33      10000\r\nT_PART     P3    NO    28.10:2018 08:37:39      10000\r\nT_PART     P1    YES   28.10:2018 08:26:33          0\r\nT_PART     P2    NO    28.10:2018 08:26:33      10000\r\nT_PART     P0    YES   28.10:2018 08:26:33          0\r\n\r\nSQL> begin\r\n  4     dbms_stats.gather_table_stats (null,'T_PART');\r\n  5  end;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> SELECT table_name, partition_name, global_stats, last_analyzed, num_rows\r\n  2  FROM   user_tab_statistics s\r\n  3  WHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART     P2    NO    28.10:2018 08:26:33      10000\r\nT_PART     P0    YES   28.10:2018 08:26:33          0\r\nT_PART           YES   28.10:2018 08:41:21      20000\r\nT_PART     P3    NO    28.10:2018 08:37:39      10000\r\nT_PART     P1    YES   28.10:2018 08:26:33          0\r\n\r\nSQL> SELECT o.name         Table_Name,\r\n  2         p.subname      Partition_name,\r\n  3         c.name         "Column",\r\n  4         h.analyzetime  "Synopsis Creation Time"\r\n  5  FROM   sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,\r\n  6         sys.OBJ$ o,\r\n  7         sys.COL$ c,\r\n  8         ( ( SELECT TABPART$.bo#  BO#,\r\n  9                    TABPART$.obj# OBJ#\r\n 10             FROM   sys.TABPART$ tabpart$ )\r\n 11           UNION ALL\r\n 12           ( SELECT TABCOMPART$.bo#  BO#,\r\n 13                    TABCOMPART$.obj# OBJ#\r\n 14             FROM   sys.TABCOMPART$ tabcompart$ ) ) tp,\r\n 15         sys.OBJ$ p\r\n 16  WHERE  o.name = 'T_PART' AND\r\n 17         tp.obj# = p.obj# AND\r\n 18         h.bo# = tp.bo# AND\r\n 19         h.group# = tp.obj# * 2 AND\r\n 20         h.bo# = c.obj#(+) AND\r\n 21         h.intcol# = c.intcol#(+) AND\r\n 22         h.bo# = o.obj#\r\n 23  ORDER  BY 4,1,2,3\r\n 24  ;\r\n\r\nTABLE_NAME PARTI Colum Synopsis Creation T\r\n---------- ----- ----- -------------------\r\nT_PART     P0    ID    28.10:2018 08:26:33\r\nT_PART     P0    PKEY  28.10:2018 08:26:33\r\nT_PART     P0    S     28.10:2018 08:26:33\r\nT_PART     P1    ID    28.10:2018 08:26:33\r\nT_PART     P1    PKEY  28.10:2018 08:26:33\r\nT_PART     P1    S     28.10:2018 08:26:33\r\nT_PART     P2    ID    28.10:2018 08:26:33\r\nT_PART     P2    PKEY  28.10:2018 08:26:33\r\nT_PART     P2    S     28.10:2018 08:26:33\r\nT_PART     P3    ID    28.10:2018 08:37:39\r\nT_PART     P3    PKEY  28.10:2018 08:37:39\r\nT_PART     P3    S     28.10:2018 08:37:39\r\n\r\n12 rows selected. \r\n\r\n<\/pre>\n
Listing 5: After partition exchange: all statistics are now on partitioned table<\/em><\/p>\n
Incremental statistics on partitioned tables is an interesting topic on its own. Here are some useful links:<\/p>\n