.
\nIn short, when have statistics gathered in 11.2.0.3 but the query is now running with the 11.2.0.4 optimizer, you may have wrong cardinality estimation on histograms, leading to sub-optimal plans.
\n
\nI had a table with a flag that has two values ‘Y’ and ‘N’ with even distribution between them. It’s a good case for frequency histograms. I had frequency histograms and expected exact cardinality estimation for a WHERE FLAG=’Y’ predicate. But that was not the case: very low estimation leading to very bad execution plan. Because the cardinality estimation was far from what we have in histograms and far from what we would have without histograms, I checked a 10053 trace and this is what I’ve find:<\/p>\n
Using prorated density: 0.004557 of col #97 as selectivity of out-of-range\/non-existent value pred<\/code><\/pre>\nThis is linear decay because of out-of-range predicate (I’ll show that next Wednesday at UKOUG TECH16) but I don’t expect an out-of-range condition when I provide one of the two values that are in the frequency histogram.<\/p>\n
Here is my testcase<\/h3>\n\nSQL> create table DEMO ( flag char);\nTable created.\n \nSQL> select count(*) from DEMO where flag='Y';\n \n COUNT(*)\n----------\n 0\n \nSQL> insert into DEMO select 'Y' from xmltable('1 to 100000');\n100000 rows created.\n \nSQL> insert into DEMO select 'N' from xmltable('1 to 1000');\n1000 rows created.\n \nSQL> select flag,to_char(ascii(flag),'XX'),count(*) from DEMO group by flag;\n \nFLAG TO_CHAR(ASCII(FLAG),'XX') COUNT(*)\n---- ------------------------- --------\nY 59 100000\nN 4E 1000\n<\/code><\/pre>\n100000 rows with ‘Y’ and 1000 rows with ‘N’.<\/p>\n
11.2.0.3<\/h3>\n
I gather statistics in 11.2.0.3<\/p>\n
\nSQL> alter session set optimizer_features_enable='11.2.0.3';\nSession altered.\n \nSQL> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false);\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nAnd run a query looking for rows where flag is ‘Y’<\/p>\n
\nSQL> explain plan for select count(*) from DEMO where flag='Y';\nExplained.\n \nSQL> select * from table(dbms_xplan.display(format=>'basic +rows'));\nPLAN_TABLE_OUTPUT\nPlan hash value: 2180342005\n \n-------------------------------------------\n| Id | Operation | Name | Rows |\n-------------------------------------------\n| 0 | SELECT STATEMENT | | 1 |\n| 1 | SORT AGGREGATE | | 1 |\n| 2 | TABLE ACCESS FULL| DEMO | 100K|\n-------------------------------------------\n<\/code><\/pre>\nThe estimate is perfect thanks to the histograms.<\/p>\n
11.2.0.4<\/h3>\n
Now the CBO will running in 11.2.0.4 but statistics have not been gathered since 11.2.0.3<\/p>\n
\nSQL> alter session set optimizer_features_enable='11.2.0.4';\nSession altered.\n \nSQL> explain plan for select count(*) from DEMO where flag='Y';\nExplained.\n \nSQL> select * from table(dbms_xplan.display(format=>'basic +rows'));\nPLAN_TABLE_OUTPUT\nPlan hash value: 2180342005\n \n-------------------------------------------\n| Id | Operation | Name | Rows |\n-------------------------------------------\n| 0 | SELECT STATEMENT | | 1 |\n| 1 | SORT AGGREGATE | | 1 |\n| 2 | TABLE ACCESS FULL| DEMO | 500 |\n-------------------------------------------\n<\/code><\/pre>\nBad estimation here. Were those 500 come from?
\nIt’s not from the histogram that knows that 100K rows have value ‘Y’
\nWithout histograms the estimation would be based on 2 distinct values among 101000 so that would be 50500.
\nCBO trace says:<\/p>\n
Access path analysis for DEMO\n***************************************\nSINGLE TABLE ACCESS PATH\n Single Table Cardinality Estimation for DEMO[DEMO]\n SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE\n \n kkecdn: Single Table Predicate:\"DEMO\".\"FLAG\"='Y'\n Column (#1):\n NewDensity:0.004950, OldDensity:0.000005 BktCnt:101000.000000, PopBktCnt:101000.000000, PopValCnt:2, NDV:2\n Column (#1): FLAG(CHARACTER)\n AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.004950\n Histogram: Freq #Bkts: 2 UncompBkts: 101000 EndPtVals: 2 ActualVal: no\n Using density: 0.004950 of col #1 as selectivity of pred having unreasonably low value\n Table: DEMO Alias: DEMO\n Card: Original: 101000.000000 Rounded: 500 Computed: 500.000000 Non Adjusted: 500.000000<\/code><\/pre>\nPredicate having unreasonably low value…<\/p>\n
Time to look at the histograms.<\/p>\n
ENDPOINT_VALUE<\/h3>\n
You can get the histogram entries. For CHAR datatype (my case here) the endpoint value is hashed through their ASCII representation put in decimal. Or at least with first characters. <\/p>\n
SQL> select endpoint_number,endpoint_value\n 2 ,to_char(endpoint_value,rpad('FM',65,'X'))\n 3 ,utl_raw.cast_to_varchar2(hextoraw(to_char(endpoint_value,rpad('FM',65,'X'))))\n 4 from user_histograms h where table_name='DEMO';\nENDPOINT_NUMBER ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X')) UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X'))))\n--------------- ------------------------------------ ----------------------------------------- ------------------------------------------------------------------------------\n1000 405650737316592000000000000000000000 4E20202020203A7BB119D5F6000000 N :{\ufffd\ufffd\n101000 462766002760475000000000000000000000 59202020202034D998FF0B5AE00000 Y 4\u0658\ufffd\n Z\ufffd\n<\/code><\/pre>\nLooks good. I can see the ‘N’ and ‘Y’ values here. But obviously the CBO sees that as different than ‘Y’.<\/p>\n
Let’s gather statistics again (I’m now with 11.2.0.4 CBO):<\/p>\n
SQL> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false);\nPL\/SQL procedure successfully completed.<\/code><\/pre>\nAnd look at the difference:<\/p>\n
SQL> select endpoint_number,endpoint_value\n 2 ,to_char(endpoint_value,rpad('FM',65,'X'))\n 3 ,utl_raw.cast_to_varchar2(hextoraw(to_char(endpoint_value,rpad('FM',65,'X'))))\n 4 from user_histograms h where table_name='DEMO';\nENDPOINT_NUMBER ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X')) UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X'))))\n--------------- ------------------------------------ ----------------------------------------- ------------------------------------------------------------------------------\n1000 404999154965717000000000000000000000 4E0000000000181F436C7BBB200000 NCl{\ufffd\n101000 462114420409600000000000000000000000 590000000000127D2B51B120000000 Y}+Q\ufffd<\/code><\/pre>\nIn 11.2.0.3 the characters (‘Y’ is ASCII 0x59) were padded with spaces (ASCII 0x20). But In 11.2.0.4 they are padded with nulls (ASCII 0x00).
\nThis is the reason why it was considered different. The ENDPOINT_VALUE for ‘Y’ calculated by the 11.2.0.4 version of the CDB is different from the one calculated by the 11.2.0.3 dbms_stats. <\/p>\n
Now, the estimation is good again:<\/p>\n
\nSQL> explain plan for select count(*) from DEMO where flag='Y';\nExplained.\n \nSQL> select * from table(dbms_xplan.display(format=>'basic +rows'));\nPLAN_TABLE_OUTPUT\nPlan hash value: 2180342005\n \n-------------------------------------------\n| Id | Operation | Name | Rows |\n-------------------------------------------\n| 0 | SELECT STATEMENT | | 1 |\n| 1 | SORT AGGREGATE | | 1 |\n| 2 | TABLE ACCESS FULL| DEMO | 100K|\n-------------------------------------------\n<\/code><\/pre>\nchar_value<\/h3>\n
The ENDPOINT_VALUE function has been described by Jonathan Lewis in Cost-Based Oracle Fundamentals<\/a> and the script to encode a character string is downloadable rom http:\/\/www.jlcomp.demon.co.uk\/cbo_book\/book_cbo_scripts.zip<\/a>. I use it (as an inline function because I’m running that in 12c<\/em>) to show the values calculated from ‘Y’ and ‘N’ as well as the values calculated from same strings right-padded with spaces:<\/p>\n\nSQL> with function char_value(i_vc varchar2) return number\n -- function coming from Jonathan Lewis char_value.sql http:\/\/www.jlcomp.demon.co.uk\/cbo_book\/book_cbo_scripts.zip\n is\n m_vc varchar2(15) := substr(rpad(i_vc,15,chr(0)),1,15);\n m_n number := 0;\n begin\n for i in 1..15 loop\n m_n := m_n + power(256,15-i) * ascii(substr(m_vc,i,1));\n end loop;\n m_n := round(m_n, -21);\n return m_n;\n end;\n select flag,char_value(flag),cast(flag as char(32)),char_value(cast(flag as char(32))),count(*) from DEMO.DEMO group by flag\n\/\n \nFLAG CHAR_VALUE(FLAG) CAST(FLAGASCHAR(32)) CHAR_VALUE(CAST(FLAGASCHAR(32))) COUNT(*)\n---- ------------------------------------ -------------------------------- ------------------------------------ --------\nY 462114420409600000000000000000000000 Y 462766002760475000000000000000000000 100000\nN 404999154965717000000000000000000000 N 405650737316592000000000000000000000 1000\n<\/code><\/pre>\nComparing the calculated values with the ENDPOINT_VALUE I had above, we can see that the function has not changed but the input string was padded with spaces before which is not the case anymore.<\/p>\n
So what?<\/h3>\n