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