[20211215]提示precompute_subquery补充.txt

[20211215]提示precompute_subquery补充.txt

--//前几天测试precompute_subquery,我仔细想一下好像以前看书或者别人的blog见到使用过.
--//我翻看以前的工作日志,发现blog如下:

D:\notes>dir /s/b *precompute_subquery*
D:\notes\2014\201408\[20140829]PRECOMPUTE_SUBQUERY hint.txt
D:\notes\2015\201503\[20150316]PRECOMPUTE_SUBQUERY.txt

--//我自己都写过两篇文章,时间太久远了,有点记不住了,加上很少使用这个提示.仔细看了原来的文章,发现当时自己的功力实在太差了.
--//不过里面提到的几个细节我给重复测试看看.

1.环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试1:
SCOTT@book> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);
D
-
X
---//多执行几次.
SCOTT@book> @hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
2725646910 c437vsqj7c4jy            3       4670       272002086 2021-12-15 09:34:22    16777219

SCOTT@book> select sql_id,child_number from v$sql where sql_id='c437vsqj7c4jy';
SQL_ID        CHILD_NUMBER
------------- ------------
c437vsqj7c4jy            0
c437vsqj7c4jy            1
c437vsqj7c4jy            2
c437vsqj7c4jy            3


SCOTT@book> @nonshared c437vsqj7c4jy
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
SQL_ID                        : c437vsqj7c4jy
ADDRESS                       : 000000007D702AC8
CHILD_ADDRESS                 : 000000007C330B70
CHILD_NUMBER                  : 0
REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : c437vsqj7c4jy
ADDRESS                       : 000000007D702AC8
CHILD_ADDRESS                 : 000000007D10AAD8
CHILD_NUMBER                  : 1
CURSOR_PARTS_MISMATCH         : Y
REASON                        : <ChildNode><ChildNumber>1</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : c437vsqj7c4jy
ADDRESS                       : 000000007D702AC8
CHILD_ADDRESS                 : 000000007BFE2E18
CHILD_NUMBER                  : 2
CURSOR_PARTS_MISMATCH         : Y
REASON                        : <ChildNode><ChildNumber>2</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : c437vsqj7c4jy
ADDRESS                       : 000000007D702AC8
CHILD_ADDRESS                 : 000000007C121608
CHILD_NUMBER                  : 3
CURSOR_PARTS_MISMATCH         : Y
REASON                        :
-----------------
PL/SQL procedure successfully completed.

--//换一个语句尝试：
SCOTT@book> select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp);
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
---//多执行几次.

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
1533076182 b58wqt9dq1sqq            3      58070      3383998547 2021-12-15 09:36:34    16777219

SCOTT@book> select sql_id,child_number from v$sql where sql_id='b58wqt9dq1sqq';
SQL_ID        CHILD_NUMBER
------------- ------------
b58wqt9dq1sqq            0
b58wqt9dq1sqq            1
b58wqt9dq1sqq            2
b58wqt9dq1sqq            3
--//问题依旧。

SCOTT@book> @nonshared b58wqt9dq1sqq
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

SQL_ID                        : b58wqt9dq1sqq
ADDRESS                       : 000000007CD50B68
CHILD_ADDRESS                 : 000000007D8DE4E8
CHILD_NUMBER                  : 0
REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : b58wqt9dq1sqq
ADDRESS                       : 000000007CD50B68
CHILD_ADDRESS                 : 000000007C932BA8
CHILD_NUMBER                  : 1
CURSOR_PARTS_MISMATCH         : Y
REASON                        : <ChildNode><ChildNumber>1</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : b58wqt9dq1sqq
ADDRESS                       : 000000007CD50B68
CHILD_ADDRESS                 : 000000007D276458
CHILD_NUMBER                  : 2
CURSOR_PARTS_MISMATCH         : Y
REASON                        : <ChildNode><ChildNumber>2</ChildNumber><ID>9</ID><reason>PQ Slave mismatch(1)</reason><size>1x4</size><ctxxyfl_cursor>67108864</ctxxyfl_cursor></ChildNode>
-----------------
SQL_ID                        : b58wqt9dq1sqq
ADDRESS                       : 000000007CD50B68
CHILD_ADDRESS                 : 000000007BC6F920
CHILD_NUMBER                  : 3
CURSOR_PARTS_MISMATCH         : Y
REASON                        :
-----------------
PL/SQL procedure successfully completed.

3.测试2:
--//内层使用绑定变量呢？
variable v_sal number;
exec :v_sal := 1000;

SCOTT@book> @ sl all
alter session set statistics_level = all;

Session altered.

SCOTT@book> select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp where sal > :v_sal);
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SCOTT@book> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  260bdkx0wpcrt, child number 1
-------------------------------------
select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY
*/ deptno from emp where sal > :v_sal)
Plan hash value: 2100826622
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   216M(100)|          |      1 |00:00:00.01 |      12 |      5 |       |       |          |
|*  1 |  HASH JOIN ANTI NA |      |      1 |      1 |    27 |   216M  (1)|722:44:41 |      1 |00:00:00.01 |      12 |      5 |  1321K|  1321K| 1074K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |      0 |       |       |          |
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |    166M|  1112M|   216M  (1)|722:44:35 |     12 |00:00:00.01 |       6 |      5 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / DEPT@SEL$1
   3 - SEL$5DA710D3 / EMP@SEL$2
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 1000
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")
   3 - filter("SAL">:V_SAL)
--//如果使用绑定变量，提示失效。

SCOTT@book> select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp where sal > 1000);
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SCOTT@book> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cabmj7fg33ty7, child number 0
-------------------------------------
select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY
*/ deptno from emp where sal > 1000)
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       6 |
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      2 |    40 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>30))

SCOTT@book> select sql_id,child_number from v$sql where sql_id='cabmj7fg33ty7';
SQL_ID        CHILD_NUMBER
------------- ------------
cabmj7fg33ty7            0
cabmj7fg33ty7            1

4.总结：
--//1.怪不得这个提示很少人提及，每次都产生1个新的子光标，每次都是一次"硬分析"。
--//2.使用范围很窄，一旦内层使用绑定变量，提示失效。