[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.使用範圍很窄,一旦內層使用繫結變數,提示失效。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2847784/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20211221]提示precompute_subquery補充2.txt
- [20180928]ora-01426(補充).txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20220603]測試quiz night(補充).txtUI
- [20211116]plsql_code_type=native補充.txtSQL
- [20221014]TNS-12543 TNSdestination host unreachable(補充).txt
- [20210803]使用那個shared pool latch(補充).txt
- [20211013]測試遠端監聽補充.txt
- [20210929]sql打補丁使用rule提示問題.txtSQL
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20211025]12c sequence nocache測試補充.txt
- [20220120]超長sql語句補充4.txtSQL
- [20220119]超長sql語句補充3.txtSQL
- [20180129]簡單探究cluster table(補充)4.txt
- [20181229]簡單探究cluster table(補充)3.txt
- [20181227]簡單探究cluster table(補充)2.txt
- [20211021]windows新建文字檔案帶日期(補充).txtWindows
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20200414]Linux下快速刪除大量檔案(補充).txtLinux
- [20201208]為什麼返回2行記錄補充.txt
- [20190214]11g Query Result Cache RC Latches補充.txt
- [20190211]簡單測試埠是否開啟(補充).txt
- [20211009]8K資料庫最大行號補充.txt資料庫
- [20201116]測試CURSOR_SPACE_FOR_TIME(10g)(補充).txt
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20210708]find -mtime +0 0 -0時間問題補充.txt
- [20210626]find -mtime +N N -N時間問題補充.txt
- [20210625]find -mtime +N N -N時間問題補充.txt
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20190312]關於增量檢查點的疑問(補充).txt
- [20230227]探究v$session.SQL_EXEC_ID在共享池(補充).txtSessionSQL
- [20181030]避免表示式在sql語句中(10g)(補充).txtSQL
- [20230303]生成相關備庫的awr報表(補充說明).txt
- [20211011]計算sql_id.sh指令碼的一些補充.txtSQL指令碼
- linux命令補充Linux
- 聯通性補充