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>
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]
*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>
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 [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Patrick Jolliffe [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 |
----------------------------------------------------------------------------------------
