[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;

--//一樣有效,自不過產生許多子游標罷了.