CURSOR_SHARING and ORDER 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.