大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说[20211215]提示precompute_subquery补充.txt「建议收藏」,希望您对编程的造诣更进一步.
![[20211215]提示precompute_subquery补充.txt](https://www.yht7.com/upload/image/images/imgsql/16.jpg "[20211215]提示precompute_subquery补充.txt「建议收藏」插图")
![[20211215]提示precompute_subquery补充.txt](/wp-content/themes/justnews/themer/assets/images/lazy.png "[20211215]提示precompute_subquery补充.txt「建议收藏」插图1")
[20211215]提示precompute_subquery补充.txt
–//前几天测试precompute_subquery,我仔细想一下好像以前看书或者别人的blog见到使用过.
–//我翻看以前的工作日志,发现blog如下:
D: otes>dir /s/b *precompute_subquery*
D: otes2014201408[20140829]PRECOMPUTE_SUBQUERY hint.txt
D: otes2015201503[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>1×4</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>1×4</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>1×4</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>1×4</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>1×4</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>1×4</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://daima100.com/5625.html
