RE: Cardinality estimate for hybrid histogram skips padding char fields
Date: Wed, 20 Apr 2016 06:53:26 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282CEB1F_at_EXMBX01.thus.corp>
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
_at_jloracle
From: Patrick Jolliffe [jolliffe_at_gmail.com] 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_at_jlcomp.demon.co.uk<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Patrick Jolliffe [jolliffe_at_gmail.com<mailto: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 ||* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 1447 | 14470 | 43 (38)| 00:00:01 |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 43 (38)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 10 | | |
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 ||* 2 | TABLE ACCESS STORAGE FULL| TEST_CARD | 175K| 1716K| 43 (38)| 00:00:01 |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 43 (38)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 10 | | |
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 20 2016 - 08:53:26 CEST