Projection Pushdown bug in 11.2.0.3

There’s a bug in 11.2.0.3 related to projection pushdown that seems to be influenced by the setting of STATISTICS_LEVEL.

Having taken the time to distill a test case from a real world issue (commenting out columns and logic and bringing view definitions inline etc – original query had no reference to DUAL), turns out it’s very simple to reproduce:

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

SQL> SELECT 
  2         1     col1,
  3         'X'   col2
  4  FROM   DUAL  trad
  5  UNION
  6  SELECT (SELECT SUM(1)
  7          FROM   DUAL) col1,
  8         TO_CHAR (NULL) col2
  9  FROM   DUAL ;
FROM   DUAL  trad
       *
ERROR at line 4:
ORA-00600: internal error code, arguments: [qkeIsExprReferenced1], [], [], [],
[], [], [], [], [], [], [], []


SQL> 

Maybe you’re thinking that you probably don’t tend to run with STATISTICS_LEVEL set to ALL though?

But perhaps you’re used to doing your SQL tuning with the GATHER_PLAN_STATISTICS hint?

SQL> SELECT /*+ gather_plan_statistics */
  2         1     col1,
  3         'X'   col2
  4  FROM   DUAL  trad
  5  UNION
  6  SELECT (SELECT SUM(1)
  7          FROM   DUAL) col1,
  8         TO_CHAR (NULL) col2
  9  FROM   DUAL ;
FROM   DUAL  trad
       *
ERROR at line 4:
ORA-00600: internal error code, arguments: [qkeIsExprReferenced1], [], [], [],
[], [], [], [], [], [], [], []


SQL> 

You can address the bug easily enough by altering _projection_pushdown to false like so:

SQL> ALTER SESSION SET "_projection_pushdown" = false;

Session altered.

SQL> SELECT /*+ gather_plan_statistics */
  2         1     col1,
  3         'X'   col2
  4  FROM   DUAL  trad
  5  UNION
  6  SELECT (SELECT SUM(1)
  7          FROM   DUAL) col1,
  8         TO_CHAR (NULL) col2
  9  FROM   DUAL ;

      COL1 C
---------- -
         1 X
         1

SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

SQL> SELECT 1     col1,
  2         'X'   col2
  3  FROM   DUAL  trad
  4  UNION
  5  SELECT (SELECT SUM(1)
  6          FROM   DUAL) col1,
  7         TO_CHAR (NULL) col2
  8  FROM   DUAL ;

      COL1 C
---------- -
         1 X
         1

SQL> 

Unfortunately, if you’re turning projection pushdown off, then that must critically change the plan of the statement you’re looking at.

Any thoughts that you might use the column projection information of DBMS_XPLAN to help diagnose the bug are scuppered by the fact that this is obviously raised at parse time.

What is particularly disappointing is that we found this on Day 1 of testing an application against 11.2.0.3, an application which we’ve previously tested extensively on 11.2.0.2 and were considering 11.2.0.3 rather than request individual backports for a couple of specific, unrelated bugs that we’ve yet to address on 11.2.0.2.

About these ads

One Response to Projection Pushdown bug in 11.2.0.3

  1. Dom Brooks says:

    The support engineer is telling me that this is bug 7041059, the description of which says:

    A-600 [qkeIsExprReferenced1] can occur from projection pushdown if the query involves user defined operators.
    

    And this despite the fact that bug 7041059 is not believed to affect 11.2 plus the fact that this was not an issue in 11.2.0.2 and it is an issue in 11.2.0.3 and despite this not involving user-defined operators.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: