Re: Cardinality estimate for hybrid histogram skips padding char fields

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Wed, 20 Apr 2016 14:44:49 +0800
Message-ID: <CABx0cSV8UAO_gdga4jb=ZTGBbuDSpKTrR1tmXu31FVyJkPzhqA_at_mail.gmail.com>

Thanks for the feedback Jonathan, I did try to reproduce previously, but if I recall correctly could only get it to reproduce if the number of distinct values went over 255 and hybrid histogram was generated. (Also think test case doesn't reproduce on 11g, so that's why I made the association with this new feature). I will try to dig into that more tomorrow. Our application (JDEdwards) uses CHAR semantics throughout, I suspect vast majority of Oracle databases use VARCHAR2, so wouldn't run into this problem.
Trouble is, we have loads of views/PL/SQL code on top of this application, where we don't pad to correct field length. Also interestingly this was not histogram on a column group we specifically defined, but the new 12c feature which automatically created the column group and histograms for us

On 20 April 2016 at 14:29, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>
> Patrick,
>
> I think it's not just hybrid histograms. I think it's a generic problem
> that could show up in any type of histogram on a column group. (It's also
> possible that it could appear with any type of virtual column based on a
> user-defined function with CHAR inputs - but I haven't tested that idea.)
>
> I think the issue is that Oracle doesn't consider the possible need for
> blank-padding semantics when passing parameter to the
> sys_op_combined_hash() function that it uses to generate the virtual column
> and the histogram entries - and that could lead to all sorts of odd
> effects, even with (badly defined) joins.
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Patrick Jolliffe [jolliffe_at_gmail.com]
> *Sent:* 19 April 2016 05:27
> *To:* oracle-l
> *Subject:* Cardinality estimate for hybrid histogram skips padding char
> fields
>
> Thought I would share the test case in case it helps anyone. Have raised
> SR.
>
>
> DROP TABLE TEST_CARD;
> CREATE TABLE TEST_CARD (COL1 CHAR(4), COL2 CHAR(2));
> INSERT INTO TEST_CARD SELECT 'XX', 'XX' FROM DUAL CONNECT BY LEVEL <=
> 171000;
> INSERT INTO TEST_CARD SELECT MOD(ROWNUM, 16), MOD(ROWNUM, 43) FROM DUAL
> CONNECT BY LEVEL <= 1000000;
> COMMIT;
>
> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(NULL, 'TEST_CARD', '(COL1,COL2)')
> FROM DUAL;
> SYS_STUFLHATC5RBD6JHJZWT$X2AAH
> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'TEST_CARD', METHOD_OPT=>'FOR
> COLUMNS SIZE 255 SYS_STUFLHATC5RBD6JHJZWT$X2AAH');
> SELECT HISTOGRAM FROM user_tab_col_statistics WHERE COLUMN_NAME =
> 'SYS_STUFLHATC5RBD6JHJZWT$X2AAH' and table_name = 'TEST_CARD'
> HYBRID
>
> EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_CARD WHERE COL1 = 'XX' AND
> COL2= 'XX';
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 10 | 43
> (38)| 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 10
> | | |
> |* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 1447 | 14470 | 43
> (38)| 00:00:01 |
>
> ----------------------------------------------------------------------------------------
> EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_CARD WHERE COL1 = 'XX ' AND
> COL2= 'XX';
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 10 | 43
> (38)| 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 10
> | | |
> |* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 175K| 1716K| 43
> (38)| 00:00:01 |
>
> ----------------------------------------------------------------------------------------
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 20 2016 - 08:44:49 CEST