[20210627]cursor_sharing=force与orade by.txt

[20210627]cursor_sharing=force与orade by.txt --//今天看了链接https://connor-mcdonald.com/2021/07/05/cursor_sharing-and-order-by/ --//实际上不知道是否巧合,前几天我也看到这句话. 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. --//我看到的这个内容来之sql-tuning-guide.pdf. 21c F31828-03 December 2020.我当时的测试就是不想上面说的情况. --//以前是我的测试: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> alter session set cursor_sharing=force ; Session altered. SCOTT@test01p> select * from dept where deptno=10 order by 1;     DEPTNO DNAME                LOC ---------- -------------------- -------------         10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  f2jf1h54abkzu, child number 0 ------------------------------------- select * from dept where deptno=:"SYS_B_0" order by :"SYS_B_1" Plan hash value: 2852011669 ---------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9FB2EC53 / DEPT@SEL$1    2 - SEL$9FB2EC53 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("DEPTNO"=:SYS_B_0) --//替换发生,并没有链接介绍的情况. --//执行如下也是一样 select * from dept where deptno=10 order by 2; --//换一种方式: SCOTT@test01p> select * from dept where dname='ACCOUNTING' order by 2;     DEPTNO DNAME                LOC ---------- -------------------- -------------         10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  087vcgdqz87g9, child number 0 ------------------------------------- select * from dept where dname=:"SYS_B_0" order by :"SYS_B_1" Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- SCOTT@test01p> select * from dept where dname='ACCOUNTING' order by 1;     DEPTNO DNAME                LOC ---------- -------------------- -------------         10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  087vcgdqz87g9, child number 1 ------------------------------------- select * from dept where dname=:"SYS_B_0" order by :"SYS_B_1" Plan hash value: 3103054919 ---------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |        |       |     2 (100)|          | |*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     2   (0)| 00:00:01 | |   2 |   INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1    2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DNAME"=:SYS_B_0) --//还是有效. select * from dept where dname='ACCOUNTING' order by 1,2; select * from dept where dname='ACCOUNTING' order by 2,1; select * from dept where dname='ACCOUNTING' order by 2,3; select * from dept where dname='ACCOUNTING' order by 3,2; --//一样有效,自不过产生许多子光标罢了.