In my presentation to the UKOUG SIG yesterday “Struggling with Statistics – part 2” I described a problem that I wrote about a few months ago: when you join a fact table with a massively skewed distribution on one of the surrogate key columns to a dimension holding the unique list of keys and descriptions a query against a description “loses” the skew. Here’s an demo of the problem that’s a little simpler than the one in the previous article.
rem rem Script: bitmap_join_histogram.sql rem Author: Jonathan Lewis rem Dated: June 2016 rem Updated: Sep 2018 rem execute dbms_random.seed(0) create table facts nologging as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 --> comment to avoid wordpress format issue ) select rownum id, trunc(3 * abs(dbms_random.normal)) id_status, lpad(rownum,10,'0') v1, lpad('x',100,'x') padding from generator v1, generator v2 where rownum <= 1e5 --> comment to avoid wordpress format issue ; alter table facts add constraint fct_pk primary key(id); alter table facts modify id_status not null; create table statuses as select id, chr(65 + id) status_code, rpad('x',100,'x') description from ( select distinct(id_status) id from facts ) ; alter table statuses modify status_code not null; alter table statuses add constraint sta_pk primary key (id); alter table facts add constraint fct_fk_sta foreign key (id_status) references statuses(id); create bitmap index fct_b1 on facts(id_status); begin dbms_stats.gather_table_stats( ownname => user, tabname =>'facts', method_opt => 'for all columns size skewonly' ); dbms_stats.gather_table_stats( ownname => user, tabname =>'statuses', method_opt => 'for all columns size 254' ); end; /
The definition of the facts.id_status column means I get a nice skewing effect on the data and this is what my data looks like:
select id_status, count(*) from facts group by id_status order by id_status; ID_STATUS COUNT(*) ---------- ---------- 0 26050 1 23595 2 18995 3 13415 4 8382 5 4960 6 2643 7 1202 8 490 9 194 10 55 11 17 12 2 13 rows selected.
The statuses table translates the numbers 0 – 12 into the letters ‘A’ – ‘M’.
A quick check will show you that there are 55 rows for id_status = 10, which means 55 rows for status_code = ‘K’. So what happens when we write the two queries that should show us these results. I don’t really care what the execution plans are at this point, I’m interested only in the optimizer’s estimate of cardinality – so here are two queries, each followed by its execution plan:
select sum(fct.id) from facts fct where fct.id_status = 10 ; ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| FACTS | 55 | 440 | 12 (0)| 00:00:01 | | 3 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 4 | BITMAP INDEX SINGLE VALUE | FCT_B1 | | | | | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("FCT"."ID_STATUS"=10) select sum(fct.id) from facts fct, statuses sta where fct.id_status = sta.id and sta.status_code = 'K' ; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 233 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | HASH JOIN | | 7692 | 99996 | 233 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| STATUSES | 1 | 5 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| FACTS | 100K| 781K| 229 (3)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FCT"."ID_STATUS"="STA"."ID") 3 - filter("STA"."STATUS_CODE"='K')
The estimated cardinality for the query against the base column reflects the value 55 from the histogram, but the estimated cardinality of the join is 7,692 – which is num_rows(facts) / num_distinct(id_status). Oracle has lost information about the skew. There is a way to get Oracle to produce a correct estimate (shown in the previous article) by rewriting the join as an IN subquery with the (undocumented) “precompute_subquery” hint, but there is an alternative which David Kurtz hypothesized in a conversation after the presentation was over (in fact someone else had described their use of exactly his suggested approach in a comment on a much older blog note about this problem): take the histogram from the id_status column on the facts table and “apply it” to the status_code column on the statuses table. In discussion with David I expressed the opinion that this probably shouldn’t work, and it wasn’t really a bit of fakery I’d want to apply to a production system – but we both tried it when we got home … with differing degrees of success.
Here’s a piece of code that I inserted into my script immediately after gathering stats on the statuses table. I’ll explain the details below as it makes a couple of assumptions that need to be pointed out:
declare srec dbms_stats.statrec; m_distcnt number; m_density number; m_nullcnt number; m_avgclen number; c_array dbms_stats.chararray; begin dbms_stats.get_column_stats( ownname => 'test_user', tabname => 'facts', colname => 'id_status', distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen ); srec.bkvals := dbms_stats.numarray(); c_array := dbms_stats.chararray(); for r in ( select stt.status_code, count(*) ct from facts fct, statuses stt where stt.id = fct.id_status group by stt.status_code order by stt.status_code ) loop c_array.extend; c_array(c_array.count) := r.status_code; srec.bkvals.extend; srec.bkvals(srec.bkvals.count) := r.ct; end loop; dbms_stats.prepare_column_values(srec, c_array); dbms_stats.set_column_stats( ownname => 'test_user', tabname => 'statuses', colname => 'status_code', distcnt => m_distcnt, density => m_density, nullcnt => m_nullcnt, srec => srec, avgclen => m_avgclen ); end; / alter system flush shared_pool;
The code isn’t intended to be efficient, and I’ve been a bit lazy in setting up the content.
The first step gets the column stats from facts.id_status – and I know that I’ve got a frequency histogram that covers exactly the right number of distinct values on that column so almost everything is set up correctly to copy the stats across to statuses.status_code, except one column is numeric and the other is character and (although I know it’s true because of the way I defined the status_code values) I need to ensure that the bucket values I write to the status_code need to be arranged in alphabetic order of status_code.
So my second step is to run a query against the facts table to get the counts of status_code in alphabetical order and copy the results in order into a pair of arrays – one being a standalone array of the type defined in the dbms_stats package as an array of character types, the other being the array of bucket values that already exists in the stats record for the facts.id_status column that I’ve pulled into memory. (The bucket values array is stored as cumulative frequency values, so I do have to overwrite it with the simple frequency values at this point).
Finally I “prepare column values” and “set column stats” into the correct column, and the job is done. The flush of the shared pool is there to avoid any accidents of cursors surviving previous tests and causing confusion.
So what happens when I run a couple of queries with these faked stats in place ?
set autotrace traceonly explain select sum(fct.id) from facts fct, statuses sta where fct.id_status = sta.id and sta.status_code = 'K' ; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 233 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | HASH JOIN | | 55 | 770 | 233 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| STATUSES | 1 | 6 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| FACTS | 100K| 781K| 229 (3)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FCT"."ID_STATUS"="STA"."ID") 3 - filter("STA"."STATUS_CODE"='K') select sum(fct.id) from facts fct, statuses sta where fct.id_status = sta.id and sta.status_code = 'D' ; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 233 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | HASH JOIN | | 13415 | 183K| 233 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| STATUSES | 2 | 12 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| FACTS | 100K| 781K| 229 (3)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FCT"."ID_STATUS"="STA"."ID") 3 - filter("STA"."STATUS_CODE"='D')
Querying for ‘K’ the prediction is 55 rows, querying for ‘D’ the prediction is for 13,415 rows – both estimates are exactly right. Wow !!!
Problem – that’s not what David Kurtz saw. In an email to me he said: “To my surprise, if I fake a histogram on the dimension table using the skew on the join column from the fact table I do get the correct number of rows calculated in the execution plan (provided it is less than the value if the histogram was not present)”. To make that concrete – when he queried for ‘K’ he got the correct prediction, when he queried for ‘D’ he was back to a prediction of 7,692. Looking at the report of the actual data, he’d get the right prediction for codes ‘F’ to ‘M’ and the wrong prediction for codes ‘A’ to ‘E’.
So what went wrong (and with whom) ?
When I run up new tests I tend to test Oracle versions in the order 12.1.0.2, then 11.2.0.4, then 12.2.0.1, then 18.3.0.0 – it’s the order of popularity that I currently see. So I was running my test on 12.1.0.2; David was running his test on 18.3.0.0. So I jumped a step and ran my test on 12.2.0.1: here are my results when querying for status_code = ‘D’:
-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 233 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | HASH JOIN | | 7692 | 105K| 233 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| STATUSES | 1 | 6 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| FACTS | 100K| 781K| 229 (3)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FCT"."ID_STATUS"="STA"."ID") 3 - filter("STA"."STATUS_CODE"='D')
As David has seen with 18.3, Oracle used the num_distinct to estimate the cardinality for ‘D’. (It still used the value indicated by the histogram for ‘K’.) When I set the optimizer_features_enable parameter back to 12.1.0.2 the cardinality estimate for ‘D’ wentback to 13,415 – so it looks as if this is a deliberate piece of coding. 172 fix controls and 31 optimizer state parameters changed, but none of the more likely looking candidates had any effect when I tried testing them separately; possibly there’s a new sanity check when the number of rows recorded for the table is a long way off the total histogram bucket count.
I took a quick look at the 10053 trace in 12.2, with and without the change to optimizer_features_enable. The key difference was in the single table access path analysis – which didn’t give me any further clues.
With optimizer_features_enable = 12.1.0.2 ========================================= Access path analysis for STATUSES *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for STATUSES[STA] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE kkecdn: Single Table Predicate:"STA"."STATUS_CODE"='K' Estimated selectivity: 5.5000e-04 , endpoint value predicate, col: #2 Access path analysis for STATUSES *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for STATUSES[STA] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE kkecdn: Single Table Predicate:"STA"."STATUS_CODE"='D' Estimated selectivity: 0.134150 , endpoint value predicate, col: #2 With optimizer_features_enable defaulting to 12.2.0.1 ===================================================== Access path analysis for STATUSES *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for STATUSES[STA] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE kkecdn: Single Table Predicate:"STA"."STATUS_CODE"='K' Estimated selectivity: 5.5000e-04 , endpoint value predicate, col: #2 Access path analysis for STATUSES *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for STATUSES[STA] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE kkecdn: Single Table Predicate:"STA"."STATUS_CODE"='D' Estimated selectivity: 0.076923 , endpoint value predicate, col: #2
Bottom line on this – there’s at least one person who already uses this method to work around the optimizer limitation, they need to be careful when they upgrade to 12.2 (or above) as the method no longer works in all cases.
[…] have written about the problem in my previous post but re-raising the mentioned problem by Jonathan Lewis enforced me to think about other possible solution of it. A suggested solution in Jonathan`s blog […]
Pingback by Skew Handling Subject to Filter Conditions | Chinar Aliyev`s blog — October 2, 2018 @ 2:52 pm BST Oct 2,2018 |
[…] up my “Hacking for Skew” article from a couple of days ago, Chinar Aliyev has written an article about a method for […]
Pingback by Join Cardinality | Oracle Scratchpad — October 3, 2018 @ 12:01 pm BST Oct 3,2018 |
First of all thanks for the post!
You have define FK on fact and since you are referencing only column from fact table, just wondering why join elimination doesn’t showed up in the plan? just thinking, if it works optimizer may take benefit of histogram on fact table?
thanks
Henish
Comment by Henish — October 4, 2018 @ 2:57 am BST Oct 4,2018 |
Henish,
Good thought – but although the select list references only the fact table, I have a reference to a non-key column from the statuses table in the WHERE clause. That’s why table elimination doesn’t apply.
Comment by Jonathan Lewis — October 4, 2018 @ 3:15 pm BST Oct 4,2018 |