I probably should have sent a message to say that I'd published the note:
http://jonathanlewis.wordpress.com/2016/04/20/column-groups-3/
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Jonathan Lewis
Sent: 20 April 2016 08:22
To: oracle-l
Subject: RE: Cardinality estimate for hybrid histogram skips padding char fields
Patrick,
I'm just writing up a quick blog note - with example - now.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Patrick Jolliffe [jolliffe@xxxxxxxxx]
Sent: 20 April 2016 08:00
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Cardinality estimate for hybrid histogram skips padding char fields
Ah OK, thanks. I guess we shouldn't expect a 'fix' for this anytime soon then
:)
On 20 April 2016 at 14:53, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
Patrick,
I ran your test on 12.1.0.2 last night to check that I could reproduce it, and
created a very small test for frequency histograms this morning which
demonstrated the same problem on 12.1.0.2 and 11.2.0.4.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Patrick Jolliffe [jolliffe@xxxxxxxxx<mailto:jolliffe@xxxxxxxxx>]
Sent: 20 April 2016 07:44
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Cardinality estimate for hybrid histogram skips padding char fields
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Patrick Jolliffe [jolliffe@xxxxxxxxx<mailto:jolliffe@xxxxxxxxx>]
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 |
----------------------------------------------------------------------------------------
