A question has just appeared on OTN describing a problem where code that works in 11g doesn’t work in 12c (exact versions not specified). The code in question is a C-based wrapper for some SQL, and the problem is a buffer overflow problem. The query supplied is as follows:
select T1.C1 from T1, T2 where T1.C1 = T2.D1;
The problem is that this works in 11g where the receiving (C) variable is declared as
char myBuffer [31];
but it doesn’t work in 12c unless the receiving variable is declared as:
char myBuffer [51];
There’s an important bit of background information that might be giving us a clue about what’s happened (although what I’m about to describe isn’t actually the problem unless the SQL provided is a simplified version of the problem SQL that is expected to display the problem). Column C1 is defined as char(30) and column D1 is defined as char(50). Here’s some sample code showing why you might need a buffer of 50+1 bytes to hold something that ought to be 30+1 bytes long. (This may be nothing to do with the anomaly described in the original posting – it’s just something I thought of when I first saw the question.)
rem Script: join_elimination_oddity.sql rem Author: Jonathan Lewis rem Dated: Mar 2017 create table t1( c30 char(30) primary key ); create table t2( d50 char(50) references t1 ); begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); dbms_stats.gather_table_stats( ownname => user, tabname =>'T2', method_opt => 'for all columns size 1' ); end; / explain plan for select t1.c30 from t1, t2 where t1.c30 = t2.d50 ; select * from table(dbms_xplan.display(null,null,'projection'));
So we’re selecting c30 – the 30 byte character column – from t1; what do we actually get ? Here’s the plan with the projection:
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 1 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T2 | 1 | 52 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T2"."D50" IS NOT NULL) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T2"."D50"[CHARACTER,50]
Table t1 has been eliminated and the projected column is the “equivalent” column from t2 – which is too long for the expected output. To work around this problem you can disable join elimination either by parameter (_optimizer_join_elimination_enabled=false) or by hinting /*+ no_eliminate_join(t1) */ in which case the plan (with my data) became a nested loop join from t2 to t1 with column c1 projected as expected.
Two things to note about my demonstration
A follow-up to the original posting has identified source of the problem. Rather than a side effect of join elimination the anomaly has appeared because of the “partition join evaluation” mechanism introduced in 12c. There is a hint to disable the mechanism /*+ no_partial_join(alias) */ or – if you can’t get hints into place for all the necessary code – it would be possible to disable the feature completely through the use of a hidden parameter “_optimizer_partial_join_eval”=false. The usual rule of checking with Oracle support before setting hidden parameter applies, of course.
Recent comments
1 year 4 weeks ago
1 year 16 weeks ago
1 year 20 weeks ago
1 year 21 weeks ago
1 year 26 weeks ago
1 year 47 weeks ago
2 years 15 weeks ago
2 years 45 weeks ago
3 years 29 weeks ago
3 years 29 weeks ago