METHOD_OPT of NULL
This is the start of a series of MEEK (maybe everyone else knows) posts as I seem to keep running into things about Oracle that I feel I should have known.
Until very recently I did not know what using METHOD_OPT => NULL in a DBMS_STATS call does. If you do then stop reading here 
Creation of a test table…
MNASH@orcl> create table t as select * from dba_objects;
Table created.
MNASH@orcl> select count(*) from t;
COUNT(*)
----------
72563
MNASH@orcl> select table_name, num_rows from user_tables where table_name = 'T';
TABLE_NAME NUM_ROWS
---------- ----------
T
MNASH@orcl> select table_name, column_name, num_distinct from user_tab_columns where table_name = 'T';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
T OWNER
T OBJECT_NAME
T SUBOBJECT_NAME
T OBJECT_ID
T DATA_OBJECT_ID
T OBJECT_TYPE
T CREATED
T LAST_DDL_TIME
T TIMESTAMP
T STATUS
T TEMPORARY
T GENERATED
T SECONDARY
T NAMESPACE
T EDITION_NAME
15 rows selected.
MNASH@orcl>
Gathering statistics using DBMS_STATS.GATHER_TABLE_STATS with METHOD_OPT => NULL
MNASH@orcl> exec dbms_stats.gather_table_stats(null,'T',method_opt=>null) PL/SQL procedure successfully completed. MNASH@orcl> select table_name, num_rows from user_tables where table_name = 'T'; TABLE_NAME NUM_ROWS ---------- ---------- T 72563 MNASH@orcl> select table_name, column_name, num_distinct from user_tab_columns where table_name = 'T'; TABLE_NAME COLUMN_NAME NUM_DISTINCT ---------- ------------------------------ ------------ T OWNER T OBJECT_NAME T SUBOBJECT_NAME T OBJECT_ID T DATA_OBJECT_ID T OBJECT_TYPE T CREATED T LAST_DDL_TIME T TIMESTAMP T STATUS T TEMPORARY T GENERATED T SECONDARY T NAMESPACE T EDITION_NAME 15 rows selected. MNASH@orcl>
Statistics are gathered for the table, but not for the columns of the table.
I found out about this through a friend after puzzling over how some of the tables in a database had statistics at a table level, but none for the columns.
Just for completeness, the following shows what happens when METHOD_OPT is allowed to default:
MNASH@orcl> exec dbms_stats.gather_table_stats(null,'T') PL/SQL procedure successfully completed. MNASH@orcl> select table_name, num_rows from user_tables where table_name = 'T'; TABLE_NAME NUM_ROWS ---------- ---------- T 72563 MNASH@orcl> select table_name, column_name, num_distinct from user_tab_columns where table_name = 'T'; TABLE_NAME COLUMN_NAME NUM_DISTINCT ---------- ------------------------------ ------------ T OWNER 32 T OBJECT_NAME 43784 T SUBOBJECT_NAME 108 T OBJECT_ID 72563 T DATA_OBJECT_ID 7705 T OBJECT_TYPE 44 T CREATED 1174 T LAST_DDL_TIME 1272 T TIMESTAMP 1318 T STATUS 1 T TEMPORARY 2 T GENERATED 2 T SECONDARY 2 T NAMESPACE 21 T EDITION_NAME 0 15 rows selected. MNASH@orcl>
Whether or not you want to use the default, which is to allow Oracle to create histograms as it sees appropriate, is a separate point which has received plenty of attention elsewhere.
In the case I was looking into there was actually a bug in the code used as a wrapper to DBMS_STATS and it was not intended that no column statistics were gathered, but I started to wonder if there would be a situation where using this undocumented option would be attractive. I did some fairly simple testing to establish the overhead of collecting column statistics…
The test was:
1 – Increase the number of rows in table T to around 1 million
2 – Delete table statistics for table T
3 – Flush the buffer cache
4 – Gather table statistics using compute with method_opt => null
5 – Repeat (2) to (4) 5 times
6 – Delete table statistics for table T
7 – Flush the buffer cache
8 – Gather table statistics using compute with method_opt => ‘for all columns size 1′
9 – Repeat (6) to (8) 5 times
Looking at the elapsed time for the statistics gathering I got averages of:
null: 2.3 seconds
for all columns size 1: 14.5 seconds
That’s a pretty big overhead, but a lot of information you are robbing the CBO of if you don’t gather them!
I then ran the same test, but with AUTO_SAMPLE_SIZE as this is a more realistic case and got averages of:
null: 1.5 seconds
for all columns size 1: 3.4 seconds
Note: The tests were performed on a 11.2.0.1 database.
Where is METHOD_OPT SIZE REPEAT’s repeat value stored?
In my last post When does METHOD_OPT SIZE REPEAT not obey we saw how SIZE REPEAT sometimes appears to know best and creates a FREQUENCY histogram with a different number of buckets than previously specified.
The mechanics of this became clearer while pondering another question that I often ask myself.
How does METHOD_OPT SIZE REPEAT remember the value last specified in order to repeat it?
My primitive testing leads me to believe that the key value is stored in column “SYS.HIST_HEAD$.BUCKET_CNT”. The test that follows shows my working.
The table created below has two columns with HEIGHT BALANCED histograms, one with no histogram and one with a FREQUENCY BASED histogram. The column names should help in following the test.
drop table tab1 ; create table tab1 ( colskew1 number , colskew2 number , colnoskew number , colfreq number ); insert into tab1 select case when rownum <= 500 then rownum else 0 end , case when rownum <= 500 then rownum else 0 end , rownum , case when rownum <= 50 then rownum else 0 end from all_objects where rownum <= 4000; commit; exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colskew1 size 150'); exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colskew2 size 254'); exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colnoskew size 1'); exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colfreq size 254'); column column_name format a12 select c.column_name,t.num_rows, c.num_distinct,c.num_buckets,c.histogram from user_tab_columns c, user_tables t where t.table_name = 'TAB1' and c.table_name = t.table_name order by 1; COLUMN_NAME NUM_ROWS NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------ ---------- ------------ ----------- --------------- COLSKEW1 4000 501 150 HEIGHT BALANCED COLSKEW2 4000 501 254 HEIGHT BALANCED COLNOSKEW 4000 4000 1 NONE COLFREQ 4000 51 51 FREQUENCY
Now if we check the value stored in “SYS.HIST_HEAD$.BUCKET_CNT” we see the following.
select c.name,c.intcol#,h.bucket_cnt from sys.col$ c , sys.hist_head$ h where c.obj# = (select object_id from dba_objects where object_name ='TAB1') and h.obj# = c.obj# and h.intcol# = c.intcol#; NAME INTCOL# BUCKET_CNT ------------------------------ ---------- ---------- COLSKEW1 1 150 COLSKEW2 2 254 COLNOSKEW 3 1 COLFREQ 4 4000
So for HEIGHT BALANCED histograms (including an absence of a histogram) BUCKET_CNT holds the number of buckets specified when creating the histogram. The FREQUENCY BASED histogram however stores a value equal to the row count. This is different to the NUM_BUCKETS value displayed when querying USER_TAB_COLUMNS.
Now a statistics collection with a METHOD_OPT of SIZE REPEAT simply needs to use a value similar to “MIN(BUCKET_CNT,254)” as its target bucket count.
This was backed up (if not exactly confirmed) by then checking the definition of the SYS view USER_TAB_COLS. The extract below is for the NUM_BUCKETS column:
case when nvl(h.distcnt,0) = 0 then h.distcnt
when h.row_cnt = 0 then 1
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt
and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then h.row_cnt
else h.bucket_cnt
end
We can see that for a BUCKET_CNT > 255 the value used for NUM_BUCKETS is switched for HIST_HEAD$.ROW_CNT – this is the number of records in USER_HISTOGRAMS for the column, i.e. for FREQUENCY BASED histograms the number of buckets actually stored.
I’m not sure I’ve demonstrated this very well. In essence for SIZE REPEAT:
- FREQUENCY BASED histograms disregard any value previously specified and pick up a bucket count of the number of distinct rows in the row sample (if NUM_DISTINCT is <= 254 of course).
- HEIGHT BALANCED histograms can always reuse the value stored in HIST_HEAD$.BUCKET_CNT which will only be between 1 and 254.
When does METHOD_OPT size REPEAT not obey?
I have an example here of a case where a DBMS_STATS METHOD_OPT parameter setting of REPEAT does not do as expected (by me at least).
We have a table with 3 columns. The table is loaded with 1000 rows, all columns in all rows are set to 1.
drop table tab1 ; create table tab1 ( colsz1 number , colsz3 number , colsz254 number ); insert into tab1 select 1,1,1 from all_objects where rownum <= 1000; commit;
We now gather statistics on each column with varying histogram sizes and check the resulting histogram settings for each one. The column names will help keep track of which is which.
exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colsz1 size 1'); exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colsz3 size 3'); exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colsz254 size 254'); column column_name format a12 select column_name,num_distinct,num_buckets,histogram from dba_tab_columns where table_name = 'TAB1' order by 1;
Below we can observe that “SIZE 1″ did not create a histogram and “SIZE 3″ & “SIZE 254″ created frequency histograms, each with 1 bucket. All as expected.
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------ ------------ ----------- --------------- COLSZ1 1 1 NONE COLSZ254 1 1 FREQUENCY COLSZ3 1 1 FREQUENCY
Now we add 3 new values to each column in the table giving us 4 distinct values in total.
insert into tab1 values (2,2,2); insert into tab1 values (3,3,3); insert into tab1 values (4,4,4); commit;
If we gather statistics for all of the columns using METHOD_OPT size REPEAT what do you expect to happen? My expectation is that:
- COLSZ1 will have 1 bucket
- COLSZ3 will have 3 buckets and become a HEIGHT BALANCED histogram because we have more values that the number of buckets
- COLSZ254 will have 4 buckets and remain a FREQUENCY histogram
Let’s see…
exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for all columns size repeat'); COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------ ------------ ----------- --------------- COLSZ1 4 1 NONE COLSZ254 4 4 FREQUENCY COLSZ3 4 4 FREQUENCY
I was wrong! Size REPEAT ignored me (or perhaps corrected me) and created a fourth bucket. What if I don’t use size REPEAT and force a size of 3…
exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for columns colsz3 size 3'); COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------ ------------ ----------- --------------- COLSZ1 4 1 NONE COLSZ254 4 4 FREQUENCY COLSZ3 4 3 HEIGHT BALANCED
Now that’s what I expected. So now the histogram is HEIGHT BALANCED let’s try size REPEAT again.
exec dbms_stats.gather_table_stats(null,'tab1',estimate_percent=>100,method_opt=>'for all columns size repeat'); COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------ ------------ ----------- --------------- COLSZ1 4 1 NONE COLSZ254 4 4 FREQUENCY COLSZ3 4 3 HEIGHT BALANCED
Interesting. So this time size REPEAT did not introduce a fourth bucket.
At first this seemed to be significant and I was searching for a case where I could catch it out. However it looks to me that the DBMS_STATS code is behaving sensibly:
- If we have a frequency histogram and a new value appears then DBMS_STATS will add an extra bucket if possible. In the example above I specifically asked for three buckets when there was only one value, when a fourth distinct value was added DBMS_STATS was smart enough to add another bucket.
- If we have a HEIGHT BALANCED histogram and another distinct value is added to the column then DBMS_STATS assumes there was a good reason for specifying fewer buckets than values and leaves it well alone.
The title of this post was “When does METHOD_OPT size REPEAT not obey?”. The answer of course is “When it knows better!”
The implementation of this turns out to be quite simple and will be demonstrated in my next post.
Martin Nash
Neil Johnson
Pawel Krol
Ben Thompson
Eter Pani
leave a comment