[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.使用范圍很窄，一旦內層使用系結變數，提示失效，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/381881.html
標籤：Oracle