[20211214]18c标量子查询unnest.txt
--//18c支持标量子查询unnest，也就是改写为连接模式。通过例子说明。
--//先测试11g下的情况:
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
SCOTT@book> @sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept;
DEPTNO DNAME N10
---------- -------------- ---------------------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
40 OPERATIONS
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54gxjxs8kx3w3, child number 1
-------------------------------------
select deptno,dname,(select max(sal) from emp where
emp.deptno=dept.deptno) N10 from dept
Plan hash value: 2018188441
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 | 0 |
| 1 | SORT AGGREGATE | | 4 | 1 | 7 | | | 4 |00:00:00.01 | 24 | 20 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 66M| 445M| 216M (1)|722:44:36 | 14 |00:00:00.01 | 24 | 20 |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | 0 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$2 / EMP@SEL$2
3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--//你可以发现id=2,循环4次（有4个部门），如果开发这样写，如果循环次数很多会很慢的,逻辑读很很高。
2.18c呢？
TTT@xxxxxx/orcl> @ sl all
alter session set statistics_level = all;
Session altered.
TTT@xxxxxx/orcl> select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept;
DEPTNO DNAME N10
---------- ---------------------------- ---------------------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
40 OPERATIONS
TTT@xxxxxx/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54gxjxs8kx3w3, child number 1
-------------------------------------
select deptno,dname,(select max(sal) from emp where
emp.deptno=dept.deptno) N10 from dept
Plan hash value: 2834279049
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 10 | | | |
| 1 | MERGE JOIN OUTER | | 1 | 4 | 156 | 7 (29)| 00:00:01 | 4 |00:00:00.01 | 10 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 52 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 3 | 78 | 5 (40)| 00:00:01 | 3 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 5 | VIEW | VW_SSQ_1 | 1 | 3 | 78 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | | | |
| 6 | HASH GROUP BY | | 1 | 3 | 21 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | 1186K| 1186K| 1294K (0)|
| 7 | TABLE ACCESS FULL | EMP | 1 | 14 | 98 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C772B8D1
2 - SEL$C772B8D1 / DEPT@SEL$1
3 - SEL$C772B8D1 / DEPT@SEL$1
5 - SEL$683B0107 / VW_SSQ_1@SEL$7511BFD2
6 - SEL$683B0107
7 - SEL$683B0107 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"="DEPT"."DEPTNO")
filter("ITEM_1"="DEPT"."DEPTNO")
--//你可以发现查询发生了改写，没有出现标量子查询。
--//select /*+ USE_HASH("VW_SSQ_1"@"SEL$7511BFD2") */deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept;
--//使用以上提示可以使用hash连接：
Plan hash value: 2653503239
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 12 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4 | 156 | 7 (15)| 00:00:01 | 4 |00:00:00.01 | 12 | 1695K| 1695K| 1087K (0)|
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | |
| 3 | VIEW | VW_SSQ_1 | 1 | 3 | 78 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | | | |
| 4 | HASH GROUP BY | | 1 | 3 | 21 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | 1186K| 1186K| 806K (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 98 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
TTT@xxxxxx/orcl> @expandz 54gxjxs8kx3w3 18
SELECT "A1"."DEPTNO" "DEPTNO","A1"."DNAME" "DNAME", (SELECT MAX("A2"."SAL") "MAX(SAL)" FROM "TTT"."EMP" "A2" WHERE "A2"."DEPTNO"="A1"."DEPTNO") "N10" FROM "TTT"."DEPT" "A1"
PL/SQL procedure successfully completed.
--//格式化如下：
SELECT "A1"."DEPTNO" "DEPTNO","A1"."DNAME" "DNAME", (
SELECT MAX("A2"."SAL") "MAX(SAL)"
FROM "TTT"."EMP" "A2"
WHERE "A2"."DEPTNO" = "A1"."DEPTNO"
) "N10"
FROM "TTT"."DEPT" "A1"
--//展开并不能发现改写。
TTT@xxxxxx/orcl> @10053x 54gxjxs8kx3w3 1
PL/SQL procedure successfully completed.
TTT@xxxxxx/orcl> @ ttt
tracefile_identifier = /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_7746_a54gxjxs8kx3w3.trc
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","VW_SSQ_1"."MAX(SAL)" "N10" FROM (SELECT MAX("EMP"."SAL") "MAX(SAL)","EMP"."DEPTNO" "ITEM_1" FROM "TTT"."EMP" "EMP" GROUP BY "EMP"."DEPTNO") "VW_SSQ_1","TTT"."DEPT" "DEPT" WHERE "VW_SSQ_1"."ITEM_1"(+)="DEPT"."DEPTNO"
kkoqbc: optimizing query block SEL$683B0107 (#2)
--//格式化如下：
SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","VW_SSQ_1"."MAX(SAL)" "N10"
FROM (
SELECT MAX("EMP"."SAL") "MAX(SAL)","EMP"."DEPTNO" "ITEM_1"
FROM "TTT"."EMP" "EMP"
GROUP BY "EMP"."DEPTNO") "VW_SSQ_1","TTT"."DEPT" "DEPT"
WHERE "VW_SSQ_1"."ITEM_1"(+) = "DEPT"."DEPTNO"
--//你可以发现改写如上。看看什么参数控制这样的行为：
SYS@xxxxxx/orclcdb> @ hide _optimizer_unnest_scalar_sq
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ---------------------- ---------- ------------------
_optimizer_unnest_scalar_sq enables unnesting of of scalar subquery TRUE TRUE TRUE TRUE IMMEDIATE
--//应该是_optimizer_unnest_scalar_sq隐含参数。
TTT@xxxxxx/orcl> select /*+ OPT_PARAM('_optimizer_unnest_scalar_sq', 'false') */ deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept;
DEPTNO DNAME N10
---------- ---------------------------- ---------------------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
40 OPERATIONS
TTT@xxxxxx/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c8tfsck6a46z1, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optimizer_unnest_scalar_sq', 'false') */
deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno)
N10 from dept
Plan hash value: 2018188441
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 4 |00:00:00.01 | 7 |
| 1 | SORT AGGREGATE | | 4 | 1 | 7 | | | 4 |00:00:00.01 | 24 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 35 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 24 |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$2 / EMP@SEL$2
3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--//还原原来11g的执行计划。
3.附上执行的脚本：
$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');
$ cat tpt/expandz.sql
set long 40000
set serveroutput on
column arg new_value arg
set term off
select decode(&2,11,'sql2','utility') arg from dual;
set term on
prompt
declare
l_sqltext clob := null;
l_result clob := null;
begin
select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';
-- dbms_output.put_line(l_sqltext);
-- dbms_sql2.expand_sql_text(l_sqltext,l_result);
dbms_&arg..expand_sql_text(l_sqltext,l_result);
dbms_output.put_line(l_result);
end;
/
set serveroutput off
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2847556/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
