ITPub博客

首页 > 数据库 > Oracle > [20210627]cursor_sharing=force与orade by.txt

[20210627]cursor_sharing=force与orade by.txt

原创 Oracle 作者:lfree 时间:2021-07-06 21:42:03 0 删除 编辑

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

--//一样有效,自不过产生许多子光标罢了.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2780014/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3005
  • 访问量
    6760184