{"id":782,"date":"2018-05-29T21:05:03","date_gmt":"2018-05-29T19:05:03","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=782"},"modified":"2018-11-04T20:00:15","modified_gmt":"2018-11-04T18:00:15","slug":"online-statistics-gathering-for-etl-part-2","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/online-statistics-gathering-for-etl-part-2\/","title":{"rendered":"Online Statistics Gathering for ETL – Part 2"},"content":{"rendered":"

In the first part<\/a> we looked at general preconditions for online statistics gathering to work and some restrictions. In this part we’ll take a look at what happens with direct path loads into partitioned tables. <\/p>\n

Partitioning<\/strong><\/p>\n

There are also some points to consider while working with partitioned tables. First, inserting into an empty partitioned table will gather only global table statistics<\/strong>. Listing 1 shows that. Also as expected, if we then issue subsequent INSERT statement, which bulk inserts into another (yet empty) partition, nothing will happen. <\/p>\n

\r\nSQL> DROP table t_part purge;\r\n\r\nTable T_PART dropped.\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\npartition by range (pkey)\r\n(partition P0 values less than (0),\r\npartition P1 values less than (1),\r\npartition P2 values less than (2),\r\npartition 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','FALSE')\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO   t_part\r\nSELECT -1 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           NO    21-05-2018 05:10:46      10000\r\nT_PART     P0    NO                                  \r\nT_PART     P1    NO                                  \r\nT_PART     P3    NO                                  \r\nT_PART     P2    NO                                  \r\n\r\nSQL> -- Now insert into another (empty) partition\r\nSQL> INSERT \/*+ append *\/ INTO   t_part\r\nSELECT 0 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT BY level <= 10000;\r\n\r\n10,000 rows inserted.\r\n\r\nSQL> COMMIT;\r\n\r\nCommit complete.\r\n\r\nSQL> -- No statistics have been gathered this time\r\nSQL> SELECT table_name, partition_name, global_stats, last_analyzed, num_rows\r\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           NO    21-05-2018 05:10:46      10000\r\nT_PART     P0    NO                                  \r\nT_PART     P1    NO                                  \r\nT_PART     P3    NO                                  \r\nT_PART     P2    NO                   \r\n<\/pre>\n
Listing 1 – inserting into an empty partitioned table gathers only global stats<\/strong><\/em><\/p>\n
But you can use an extended syntax and explicitly provide the partition to insert into (Listing 2). What happens then? If this partition was empty, then partition level<\/strong> statistics will be gathered online. No global statistics that time:<\/p>\n
\r\nSQL> DROP table t_part purge;\r\n\r\nTable T_PART dropped.\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\npartition by range (pkey)\r\n(partition P0 values less than (0),\r\npartition P1 values less than (1),\r\npartition P2 values less than (2),\r\npartition P3 values less than (3));\r\n\r\nTable T_PART created.\r\n\r\nSQL> INSERT \/*+ append *\/ INTO   t_part PARTITION (P2)\r\nSELECT 1 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           NO                                  \r\nT_PART     P1    NO                                  \r\nT_PART     P2    NO    21-05-2018 05:30:24      10000\r\nT_PART     P0    NO                                  \r\nT_PART     P3    NO                                  \r\n \r\n<\/pre>\n
Listing 2 – partition level stats if explicitly specifying partition <\/strong><\/em><\/p>\n
Even better, if we change the preference for gathering global table statistics to incremental, the synopsis for the changed partition will be created altogether. The prerequisite is using the extended syntax explicitly specifying the partition.<\/p>\n
\r\nSQL> DROP table t_part purge;\r\n\r\nTable T_PART dropped.\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\npartition by range (pkey)\r\n(partition P0 values less than (0),\r\npartition P1 values less than (1),\r\npartition P2 values less than (2),\r\npartition 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 (P3)\r\nSELECT 2 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           NO                                  \r\nT_PART     P0    NO                                  \r\nT_PART     P1    NO                                  \r\nT_PART     P2    NO                                  \r\nT_PART     P3    NO    21-05-2018 12:06:57      10000\r\n\r\nSQL> -- Check Synopsis\r\nSQL> SELECT o.name         Table_Name,\r\n       p.subname      Partition_name,\r\n       c.name         "Column",\r\n       h.analyzetime  "Synopsis Creation Time"\r\nFROM   sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,\r\n       sys.OBJ$ o,\r\n       sys.COL$ c,\r\n       ( ( SELECT TABPART$.bo#  BO#,\r\n                  TABPART$.obj# OBJ#\r\n           FROM   sys.TABPART$ tabpart$ )\r\n         UNION ALL\r\n         ( SELECT TABCOMPART$.bo#  BO#,\r\n                  TABCOMPART$.obj# OBJ#\r\n           FROM   sys.TABCOMPART$ tabcompart$ ) ) tp,\r\n       sys.OBJ$ p\r\nWHERE  o.name = 'T_PART' AND\r\n       tp.obj# = p.obj# AND\r\n       h.bo# = tp.bo# AND\r\n       h.group# = tp.obj# * 2 AND\r\n       h.bo# = c.obj#(+) AND\r\n       h.intcol# = c.intcol#(+) AND\r\n       h.bo# = o.obj#\r\nORDER  BY 4,1,2,3;\r\n\r\nTABLE_NAME PARTI Colum Synopsis Creation Time\r\n---------- ----- ----- ----------------------\r\nT_PART     P3    ID    21-05-2018 12:06:57   \r\nT_PART     P3    PKEY  21-05-2018 12:06:57   \r\nT_PART     P3    S     21-05-2018 12:06:57   \r\n \r\n<\/pre>\n
Listing 3 – partition level plus synopsis if explicitly specifying partition <\/strong><\/em><\/p>\n
But what happens if we don’t use the extended syntax leaving the incremental preference to be true? Nothing will happen, no online statistics at all!<\/p>\n
\r\nSQL> DROP table t_part purge;\r\n\r\nTable T_PART dropped.\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\npartition by range (pkey)\r\n(partition P0 values less than (0),\r\npartition P1 values less than (1),\r\npartition P2 values less than (2),\r\npartition 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> -- without partition-syntax\r\nSQL> INSERT \/*+ append *\/ INTO   t_part\r\nSELECT 0 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTI GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ----- ----- ------------------- ----------\r\nT_PART           NO                                  \r\nT_PART     P1    NO                                  \r\nT_PART     P2    NO                                  \r\nT_PART     P3    NO                                  \r\nT_PART     P0    NO                                  \r\n\r\nSQL> -- Check Synopsis\r\nSQL> SELECT o.name         Table_Name,\r\n       p.subname      Partition_name,\r\n       c.name         "Column",\r\n       h.analyzetime  "Synopsis Creation Time"\r\nFROM   sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,\r\n       sys.OBJ$ o,\r\n       sys.COL$ c,\r\n       ( ( SELECT TABPART$.bo#  BO#,\r\n                  TABPART$.obj# OBJ#\r\n           FROM   sys.TABPART$ tabpart$ )\r\n         UNION ALL\r\n         ( SELECT TABCOMPART$.bo#  BO#,\r\n                  TABCOMPART$.obj# OBJ#\r\n           FROM   sys.TABCOMPART$ tabcompart$ ) ) tp,\r\n       sys.OBJ$ p\r\nWHERE  o.name = 'T_PART' AND\r\n       tp.obj# = p.obj# AND\r\n       h.bo# = tp.bo# AND\r\n       h.group# = tp.obj# * 2 AND\r\n       h.bo# = c.obj#(+) AND\r\n       h.intcol# = c.intcol#(+) AND\r\n       h.bo# = o.obj#\r\nORDER  BY 4,1,2,3;\r\n\r\nno rows selected\r\n \r\n<\/pre>\n
Listing 4 – no stats with incremental = true and not explicitly specifying partition <\/strong><\/em><\/p>\n
We can show the different cases in the table:<\/p>\n\n\n\n\n\t\n\n\t\n\t
<\/td>Incremental Preference TRUE<\/th>Incremental Preference FALSE<\/th>\n<\/tr>\n<\/thead>\n
Partition name specified<\/td>partition-level stats + synopsis<\/td>partition-level stats<\/td>\n<\/tr>\n
No partition name specified<\/td>No stats at all!<\/td>global stats
\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
Well, I have one more question, which I think can simply be a bug. If we don’t reference partitions explicitly and thus get only table-level statistics, why is the column GLOBAL_STATS in USER_TAB_STATISTICS  shown as NO? The meaning of NO is that table-level statistics have been aggregated from partition-level statistics and YES means they have been explicitly gathered or set. Well, in our case they are definitely not aggregated from partition-level but are gathered on the fly <\/p>\n
Interval Partitioning<\/strong><\/em><\/p>\n
There are two reasons to explicitly mention interval partitioning. The first reason is that in 12.1 no online statistics gathering takes place with bulk INSERT in the case of interval partitioned tables. It is a known bug  (Doc ID 20562261.8) and it is fixed in 12.2. The second reason is the question that may arise with interval partitioning: how can we get partition-level statistics? Without specifying a partition we’ll get only global statistics. On the other hand, we don’t create empty partitions beforehand, instead they are automatically created by an INSERT.  But how can I specify a partition which doesn’t exist yet? Well, there is a way to do this: the extended partition syntax allows not only to specify the partition name, but also the value. The database automatically determines the right partition. This is shown in Listing 5. Interesting fact, if the table preference is to use incremental statistics, then a synopsis for affected partition is also created but we cannot see its creation time. <\/p>\n
\r\nSQL> DROP table t_part purge;\r\n\r\nTable T_PART dropped.\r\n\r\nSQL> CREATE TABLE t_part (pkey number, id number, s varchar2(20))\r\npartition by range (pkey) interval (1)\r\n(partition P0 values less than (0)\r\n);\r\n\r\nTable T_PART created.\r\n\r\nSQL> -- without partition-syntax\r\nSQL> INSERT \/*+ append *\/ INTO   t_part \r\nSELECT 0 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTITION_ GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ---------- ----- ------------------- ----------\r\nT_PART                NO    23-05-2018 03:45:10      10000\r\nT_PART     SYS_P961   NO                                  \r\nT_PART     P0         NO                                  \r\n\r\nSQL> -- With partition for\r\nSQL> INSERT \/*+ append *\/ INTO   t_part partition for (1) tp\r\nSELECT 1 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTITION_ GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ---------- ----- ------------------- ----------\r\nT_PART                NO    23-05-2018 03:45:10      10000\r\nT_PART     SYS_P961   NO                                  \r\nT_PART     P0         NO                                  \r\nT_PART     SYS_P962   NO    23-05-2018 03:45:10      10000\r\n\r\nSQL> -- With partition for + incremental\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 for (2) tp\r\nSELECT 2 pkey, level AS id , lpad (level, 20, '0') s\r\nFROM   dual\r\nCONNECT 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\nFROM   user_tab_statistics s\r\nWHERE  table_name IN ('T_PART');\r\n\r\nTABLE_NAME PARTITION_ GLOBA LAST_ANALYZED         NUM_ROWS\r\n---------- ---------- ----- ------------------- ----------\r\nT_PART                NO    23-05-2018 03:45:10      10000\r\nT_PART     SYS_P961   NO                                  \r\nT_PART     P0         NO                                  \r\nT_PART     SYS_P962   NO    23-05-2018 03:45:10      10000\r\nT_PART     SYS_P963   NO    23-05-2018 03:45:11      10000\r\n\r\nSQL> -- Check Synopsis\r\nSQL> SELECT o.name         Table_Name,\r\n       p.subname      Partition_name,\r\n       c.name         "Column",\r\n       h.analyzetime  "Synopsis Creation Time"\r\nFROM   sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,\r\n       sys.OBJ$ o,\r\n       sys.COL$ c,\r\n       ( ( SELECT TABPART$.bo#  BO#,\r\n                  TABPART$.obj# OBJ#\r\n           FROM   sys.TABPART$ tabpart$ )\r\n         UNION ALL\r\n         ( SELECT TABCOMPART$.bo#  BO#,\r\n                  TABCOMPART$.obj# OBJ#\r\n           FROM   sys.TABCOMPART$ tabcompart$ ) ) tp,\r\n       sys.OBJ$ p\r\nWHERE  o.name = 'T_PART' AND\r\n       tp.obj# = p.obj# AND\r\n       h.bo# = tp.bo# AND\r\n       h.group# = tp.obj# * 2 AND\r\n       h.bo# = c.obj#(+) AND\r\n       h.intcol# = c.intcol#(+) AND\r\n       h.bo# = o.obj#\r\nORDER  BY 4,1,2,3;\r\n\r\nTABLE_NAME PARTITION_ Colum Synopsis Creation Time\r\n---------- ---------- ----- ----------------------\r\nT_PART     SYS_P963   ID                          \r\nT_PART     SYS_P963   PKEY                        \r\nT_PART     SYS_P963   S                           \r\n \r\n<\/pre>\n
Listing 5 – explicitly specifying partition in case of interval partitioning <\/strong><\/em><\/p>\n
Conclusion<\/strong><\/p>\n
Having representative statistics  (which doesn’t automatically mean they always have to be fresh though) is essential for ETL Performance. Having looked at some use cases in part 1<\/a> and part 2<\/a>, we have seen that there are some specifics to consider.  If you plan to rely on online statistics gathering for some of your ETL steps, better double check that the feature is really working each time you run your ETL process.<\/p>\n
Related Posts<\/h3>