[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.使用范围很窄，一旦内层使用绑定变量，提示失效。
