CURSOR_SHARING and ORDER BY

Posted by

AskTom regular Rajeshwaran Jeyabal posted an interesting comment a few days ago about the use of CURSOR_SHARING when it comes to the ORDER BY clause. He pointed out the following snippet from the Oracle documentation:

Note: If a statement uses an ORDER BY clause, then the database does not perform literal replacement in the clause because it is not semantically correct to consider the constant column number as a literal. The column number in the ORDER BY clause affects the query plan and execution, so the database cannot share two cursors having different column numbers.

That obviously makes a great deal of sense, because if I had two queries:

  • select * from MYTABLE order by 1
  • select * from MYTABLE order by 2

then replacing the literals “1” and “2” makes no sense since the execution and results from those queries are different by definition.

However, the documentation is not ideally worded when it says “the database does not perform literal replacement” because a simple demo reveals that when CURSOR_SHARING is set to force, then we do not discriminate. All literals go “under the knife” so to speak.


SQL> alter session set cursor_sharing = force;

Session altered.

SQL> set feedback on sql_id
SQL> select * from scott.emp where empno > 0 order by 2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

14 rows selected.

SQL_ID: 19m73brqx4wpw

SQL> select sql_text from v$sql
  2  where sql_text like 'select * from scott.emp where empno >%';

SQL_TEXT
--------------------------------------------------------------------
select * from scott.emp where empno > :"SYS_B_0" order by :"SYS_B_1"

Does this mean that there is a risk of two queries now sharing an execution plan when they should not? Or even worse, is it possible that  one of the queries will return result sorted incorrectly? No.

The intent of the documentation is better covered by the second part of the snippet, namely “the database cannot share two cursors having different column numbers”. We can prove this by running the same query again, this time with a different ORDER BY


SQL> select * from scott.emp where empno > 0 order by 1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL_ID: 19m73brqx4wpw

SQL> select child_number, sql_text from v$sql
  2  where sql_text like 'select * from scott.emp where empno >%';

CHILD_NUMBER SQL_TEXT
------------ --------------------------------------------------------------------
           0 select * from scott.emp where empno > :"SYS_B_0" order by :"SYS_B_1"
           1 select * from scott.emp where empno > :"SYS_B_0" order by :"SYS_B_1"

We get a child for each ORDER BY. Digging into V$SQL_SHARED_CURSOR, the column of interest here is HASH_MATCH_FAILED


SQL> select * from v$sql_shared_cursor
  2  where sql_id = '19m73brqx4wpw'
  3  @pr
==============================
SQL_ID                        : 19m73brqx4wpw
ADDRESS                       : 00007FFF10DACF78
CHILD_ADDRESS                 : 00007FFF179D71C8
CHILD_NUMBER                  : 0
UNBOUND_CURSOR                : N
SQL_TYPE_MISMATCH             : N
OPTIMIZER_MISMATCH            : N
OUTLINE_MISMATCH              : N
STATS_ROW_MISMATCH            : N
LITERAL_MISMATCH              : N
FORCE_HARD_PARSE              : N
EXPLAIN_PLAN_CURSOR           : N
BUFFERED_DML_MISMATCH         : N
PDML_ENV_MISMATCH             : N
INST_DRTLD_MISMATCH           : N
SLAVE_QC_MISMATCH             : N
TYPECHECK_MISMATCH            : N
AUTH_CHECK_MISMATCH           : N
BIND_MISMATCH                 : N
DESCRIBE_MISMATCH             : N
LANGUAGE_MISMATCH             : N
TRANSLATION_MISMATCH          : N
BIND_EQUIV_FAILURE            : N
INSUFF_PRIVS                  : N
INSUFF_PRIVS_REM              : N
REMOTE_TRANS_MISMATCH         : N
LOGMINER_SESSION_MISMATCH     : N
INCOMP_LTRL_MISMATCH          : N
OVERLAP_TIME_MISMATCH         : N
EDITION_MISMATCH              : N
MV_QUERY_GEN_MISMATCH         : N
USER_BIND_PEEK_MISMATCH       : N
TYPCHK_DEP_MISMATCH           : N
NO_TRIGGER_MISMATCH           : N
FLASHBACK_CURSOR              : N
ANYDATA_TRANSFORMATION        : N
PDDL_ENV_MISMATCH             : N
TOP_LEVEL_RPI_CURSOR          : N
DIFFERENT_LONG_LENGTH         : N
LOGICAL_STANDBY_APPLY         : N
DIFF_CALL_DURN                : N
BIND_UACS_DIFF                : N
PLSQL_CMP_SWITCHS_DIFF        : N
CURSOR_PARTS_MISMATCH         : N
STB_OBJECT_MISMATCH           : N
CROSSEDITION_TRIGGER_MISMATCH : N
PQ_SLAVE_MISMATCH             : N
TOP_LEVEL_DDL_MISMATCH        : N
MULTI_PX_MISMATCH             : N
BIND_PEEKED_PQ_MISMATCH       : N
MV_REWRITE_MISMATCH           : N
ROLL_INVALID_MISMATCH         : N
OPTIMIZER_MODE_MISMATCH       : N
PX_MISMATCH                   : N
MV_STALEOBJ_MISMATCH          : N
FLASHBACK_TABLE_MISMATCH      : N
LITREP_COMP_MISMATCH          : N
PLSQL_DEBUG                   : N
LOAD_OPTIMIZER_STATS          : N
ACL_MISMATCH                  : N
FLASHBACK_ARCHIVE_MISMATCH    : N
LOCK_USER_SCHEMA_FAILED       : N
REMOTE_MAPPING_MISMATCH       : N
LOAD_RUNTIME_HEAP_FAILED      : N
HASH_MATCH_FAILED             : N
PURGED_CURSOR                 : N
BIND_LENGTH_UPGRADEABLE       : N
USE_FEEDBACK_STATS            : N
REASON                        :
CON_ID                        : 3
==============================
SQL_ID                        : 19m73brqx4wpw
ADDRESS                       : 00007FFF10DACF78
CHILD_ADDRESS                 : 00007FFF1A238078
CHILD_NUMBER                  : 1
UNBOUND_CURSOR                : N
SQL_TYPE_MISMATCH             : N
OPTIMIZER_MISMATCH            : N
OUTLINE_MISMATCH              : N
STATS_ROW_MISMATCH            : N
LITERAL_MISMATCH              : N
FORCE_HARD_PARSE              : N
EXPLAIN_PLAN_CURSOR           : N
BUFFERED_DML_MISMATCH         : N
PDML_ENV_MISMATCH             : N
INST_DRTLD_MISMATCH           : N
SLAVE_QC_MISMATCH             : N
TYPECHECK_MISMATCH            : N
AUTH_CHECK_MISMATCH           : N
BIND_MISMATCH                 : N
DESCRIBE_MISMATCH             : N
LANGUAGE_MISMATCH             : N
TRANSLATION_MISMATCH          : N
BIND_EQUIV_FAILURE            : N
INSUFF_PRIVS                  : N
INSUFF_PRIVS_REM              : N
REMOTE_TRANS_MISMATCH         : N
LOGMINER_SESSION_MISMATCH     : N
INCOMP_LTRL_MISMATCH          : N
OVERLAP_TIME_MISMATCH         : N
EDITION_MISMATCH              : N
MV_QUERY_GEN_MISMATCH         : N
USER_BIND_PEEK_MISMATCH       : N
TYPCHK_DEP_MISMATCH           : N
NO_TRIGGER_MISMATCH           : N
FLASHBACK_CURSOR              : N
ANYDATA_TRANSFORMATION        : N
PDDL_ENV_MISMATCH             : N
TOP_LEVEL_RPI_CURSOR          : N
DIFFERENT_LONG_LENGTH         : N
LOGICAL_STANDBY_APPLY         : N
DIFF_CALL_DURN                : N
BIND_UACS_DIFF                : N
PLSQL_CMP_SWITCHS_DIFF        : N
CURSOR_PARTS_MISMATCH         : N
STB_OBJECT_MISMATCH           : N
CROSSEDITION_TRIGGER_MISMATCH : N
PQ_SLAVE_MISMATCH             : N
TOP_LEVEL_DDL_MISMATCH        : N
MULTI_PX_MISMATCH             : N
BIND_PEEKED_PQ_MISMATCH       : N
MV_REWRITE_MISMATCH           : N
ROLL_INVALID_MISMATCH         : N
OPTIMIZER_MODE_MISMATCH       : N
PX_MISMATCH                   : N
MV_STALEOBJ_MISMATCH          : N
FLASHBACK_TABLE_MISMATCH      : N
LITREP_COMP_MISMATCH          : N
PLSQL_DEBUG                   : N
LOAD_OPTIMIZER_STATS          : N
ACL_MISMATCH                  : N
FLASHBACK_ARCHIVE_MISMATCH    : N
LOCK_USER_SCHEMA_FAILED       : N
REMOTE_MAPPING_MISMATCH       : N
LOAD_RUNTIME_HEAP_FAILED      : N
HASH_MATCH_FAILED             : Y
PURGED_CURSOR                 : N
BIND_LENGTH_UPGRADEABLE       : N
USE_FEEDBACK_STATS            : N
REASON                        :
CON_ID                        : 3

PL/SQL procedure successfully completed.

Note: This is not to be confused with the literal replacement that SQLcl might be doing under the covers for you, which has its own behaviours you should be aware of.

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.