问题描述
嗨
我刚刚遇到了关于内存光标持续时间临时表的有趣文章:
https://oracle-big-data.blogspot.fi/2017/03/this-post-covers-one-of-new-sql.html
有人提到此功能可用于 “串行查询” 的12.2。
我们正在为DW环境使用自动并行性，该环境运行良好。
这些并行化的查询是否会从该功能中受益？
此功能是否更改并行查询以串行运行？
在Oracle 18c中是否对此进行了任何更改？
我还应该问什么 (关于这个)？
左侧
我刚刚遇到了关于内存光标持续时间临时表的有趣文章:
https://oracle-big-data.blogspot.fi/2017/03/this-post-covers-one-of-new-sql.html
有人提到此功能可用于 “串行查询” 的12.2。
我们正在为DW环境使用自动并行性，该环境运行良好。
这些并行化的查询是否会从该功能中受益？
此功能是否更改并行查询以串行运行？
在Oracle 18c中是否对此进行了任何更改？
我还应该问什么 (关于这个)？
左侧
专家解答
目前它们是串行的，因为否则我们必须有一种方法来 * 共享 * 临时数据，以便所有奴隶都可以看到它。这意味着共享内存区域或临时段。我们目前选择后者 (用于并行)。
Will these parallelized queries benefit from this feature?
不完全是。虽然如果你检查我在这里最后的演示，你可以看到我们在推导执行计划方面使用相同的 “逻辑”-更多的是，我们需要将临时数据存储在每个人都可以访问的地方。
Does this feature change parallel queries to run serially ?
没有。
Will these parallelized queries benefit from this feature?
不完全是。虽然如果你检查我在这里最后的演示，你可以看到我们在推导执行计划方面使用相同的 “逻辑”-更多的是，我们需要将临时数据存储在每个人都可以访问的地方。
Does this feature change parallel queries to run serially ?
没有。
SQL> create table t nologging tablespace largets 2 as select d.* from dba_Objects d, 3 ( select 1 from dual connect by level <= 100 ); Table created. SQL> SQL> SQL> select /*+ gather_plan_statistics */ owner, object_type, trunc(created,'YYYY') yr, count(*) 2 from t 3 group by grouping sets 4 ( 5 (owner), 6 (owner,object_type), 7 (object_type,trunc(created,'YYYY')), 8 (trunc(created,'YYYY')), 9 (object_type) 10 ); OWNER OBJECT_TYPE YR COUNT(*) ------------------------------ ----------------------- --------- ---------- HR TYPE 100 HR VIEW 100 ... 490 rows selected. PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SQL_ID 0mcu7kuqur48p, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ owner, object_type, trunc(created,'YYYY') yr, count(*) from t group by grouping sets ( (owner), (owner,object_type), (object_type,trunc(created,'YYYY')), (trunc(created,'YYYY')), (object_type) ) Plan hash value: 3954148600 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 490 |00:00:08.69 | 196K| 181K| 14372 | | | | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | 490 |00:00:08.69 | 196K| 181K| 14372 | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D660D_960F92A1 | 1 | | 0 |00:00:05.06 | 167K| 153K| 14372 | 2068K| 2068K| | | 3 | TABLE ACCESS FULL | T | 1 | 7905K| 7905K|00:00:00.87 | 153K| 153K| 0 | | | | | 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D660E_960F92A1 | 1 | | 0 |00:00:01.82 | 14392 | 14373 | 0 | 1024 | 1024 | | | 5 | SORT GROUP BY ROLLUP | | 1 | 1282 | 371 |00:00:01.82 | 14392 | 14373 | 0 | 27648 | 27648 |24576 (0)| | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_960F92A1 | 1 | 7905K| 7905K|00:00:00.48 | 14392 | 14373 | 0 | | | | | 7 | MULTI-TABLE INSERT | | 1 | | 0 |00:00:01.80 | 14428 | 14372 | 0 | | | | | 8 | SORT GROUP BY ROLLUP | | 1 | 35 | 117 |00:00:01.80 | 14428 | 14372 | 0 | 6144 | 6144 | 6144 (0)| | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_960F92A1 | 1 | 7905K| 7905K|00:00:00.50 | 14428 | 14372 | 0 | | | | | 10 | DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660F_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 11 | DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660E_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 12 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D660E_960F92A1 | 1 | | 0 |00:00:00.01 | 0 | 0 | 0 | 1024 | 1024 | | | 13 | HASH GROUP BY | | 1 | 1 | 2 |00:00:00.01 | 0 | 0 | 0 | 1149K| 1149K| 491K (0)| | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_960F92A1 | 1 | 35 | 68 |00:00:00.01 | 0 | 0 | 0 | | | | | 15 | VIEW | | 1 | 1317 | 490 |00:00:00.01 | 0 | 0 | 0 | | | | | 16 | VIEW | | 1 | 1317 | 490 |00:00:00.01 | 0 | 0 | 0 | | | | | 17 | UNION-ALL | | 1 | | 490 |00:00:00.01 | 0 | 0 | 0 | | | | | 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_960F92A1 | 1 | 1282 | 422 |00:00:00.01 | 0 | 0 | 0 | | | | | 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_960F92A1 | 1 | 35 | 68 |00:00:00.01 | 0 | 0 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 34 rows selected. SQL> select /*+ gather_plan_statistics parallel */ owner, object_type, trunc(created,'YYYY') yr, count(*) 2 from t 3 group by grouping sets 4 ( 5 (owner), 6 (owner,object_type), 7 (object_type,trunc(created,'YYYY')), 8 (trunc(created,'YYYY')), 9 (object_type) 10 ); OWNER OBJECT_TYPE YR COUNT(*) ------------------------------ ----------------------- --------- ---------- HR TYPE 100 HR VIEW 100 ... 490 rows selected. SQL> SQL> SQL> @exp PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SQL_ID 1pmucckvxxywq, child number 1 ------------------------------------- select /*+ gather_plan_statistics parallel */ owner, object_type, trunc(created,'YYYY') yr, count(*) from t group by grouping sets ( (owner), (owner,object_type), (object_type,trunc(created,'YYYY')), (trunc(created,'YYYY')), (object_type) ) Plan hash value: 4262997987 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 490 |00:00:04.32 | 1011 | | | | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | 490 |00:00:04.32 | 1011 | | | | | 2 | PX COORDINATOR | | 1 | | 4 |00:00:02.23 | 261 | 73728 | 73728 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | | 4 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6613_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | 2068K| 2068K| 2068K (0)| | 5 | PX BLOCK ITERATOR | | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | |* 6 | TABLE ACCESS FULL | T | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | | 7 | PX COORDINATOR | | 1 | | 4 |00:00:01.09 | 27 | 73728 | 73728 | | | 8 | PX SEND QC (RANDOM) | :TQ20001 | 0 | 1282 | 0 |00:00:00.01 | 0 | | | | | 9 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6614_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | 1040K| 1040K| 1040K (0)| | 10 | VIEW | | 0 | | 0 |00:00:00.01 | 0 | | | | | 11 | SORT GROUP BY ROLLUP COLLECTOR | | 0 | 1282 | 0 |00:00:00.01 | 0 | 27648 | 27648 |12288 (0)| | 12 | PX RECEIVE | | 0 | 1282 | 0 |00:00:00.01 | 0 | | | | | 13 | PX SEND HYBRID HASH | :TQ20000 | 0 | 1282 | 0 |00:00:00.01 | 0 | | | | | 14 | SORT GROUP BY ROLLUP DISTRIBUTOR | | 0 | 1282 | 0 |00:00:00.01 | 0 | 4096 | 4096 | 2048 (0)| | 15 | HASH GROUP BY | | 0 | 1282 | 0 |00:00:00.01 | 0 | 1022K| 1022K| 1326K (0)| | 16 | PX BLOCK ITERATOR | | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | |* 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_960F92A1 | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | | 18 | PX COORDINATOR | | 1 | | 6 |00:00:00.92 | 29 | 73728 | 73728 | | | 19 | PX SEND QC (RANDOM) | :TQ30002 | 0 | | 0 |00:00:00.01 | 0 | | | | | 20 | MULTI-TABLE INSERT | | 0 | | 0 |00:00:00.01 | 0 | | | | | 21 | PX RECEIVE | | 0 | | 0 |00:00:00.01 | 0 | | | | | 22 | PX SEND ROUND-ROBIN | :TQ30001 | 0 | | 0 |00:00:00.01 | 0 | | | | | 23 | VIEW | | 0 | | 0 |00:00:00.01 | 0 | | | | | 24 | SORT GROUP BY ROLLUP COLLECTOR | | 0 | 35 | 0 |00:00:00.01 | 0 | 4096 | 4096 | 4096 (0)| | 25 | PX RECEIVE | | 0 | 35 | 0 |00:00:00.01 | 0 | | | | | 26 | PX SEND HYBRID HASH | :TQ30000 | 0 | 35 | 0 |00:00:00.01 | 0 | | | | | 27 | SORT GROUP BY ROLLUP DISTRIBUTOR| | 0 | 35 | 0 |00:00:00.01 | 0 | 4096 | 4096 | 2048 (0)| | 28 | HASH GROUP BY | | 0 | 35 | 0 |00:00:00.01 | 0 | 1079K| 1079K| 37M (0)| | 29 | PX BLOCK ITERATOR | | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | |* 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_960F92A1 | 0 | 7905K| 0 |00:00:00.01 | 0 | | | | | 31 | DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6615_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | | | | | 32 | DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6614_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | | | | | 33 | PX COORDINATOR | | 1 | | 4 |00:00:00.02 | 26 | 73728 | 73728 | | | 34 | PX SEND QC (RANDOM) | :TQ40001 | 0 | 1 | 0 |00:00:00.01 | 0 | | | | | 35 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6614_960F92A1 | 0 | | 0 |00:00:00.01 | 0 | 1040K| 1040K| 1040K (0)| | 36 | HASH GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 1149K| 1149K| 468K (0)| | 37 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | | | 38 | PX SEND HASH | :TQ40000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | | | 39 | HASH GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 1149K| 1149K| 469K (0)| | 40 | PX BLOCK ITERATOR | | 0 | 35 | 0 |00:00:00.01 | 0 | | | | |* 41 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6615_960F92A1 | 0 | 35 | 0 |00:00:00.01 | 0 | | | | | 42 | PX COORDINATOR | | 1 | | 490 |00:00:00.02 | 40 | 73728 | 73728 | | | 43 | PX SEND QC (RANDOM) | :TQ50000 | 0 | 1317 | 0 |00:00:00.01 | 0 | | | | | 44 | VIEW | | 0 | 1317 | 0 |00:00:00.01 | 0 | | | | | 45 | VIEW | | 0 | 1317 | 0 |00:00:00.01 | 0 | | | | | 46 | UNION-ALL | | 0 | | 0 |00:00:00.01 | 0 | | | | | 47 | PX BLOCK ITERATOR | | 0 | 1282 | 0 |00:00:00.01 | 0 | | | | |* 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_960F92A1 | 0 | 1282 | 0 |00:00:00.01 | 0 | | | | | 49 | PX BLOCK ITERATOR | | 0 | 35 | 0 |00:00:00.01 | 0 | | | | |* 50 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6615_960F92A1 | 0 | 35 | 0 |00:00:00.01 | 0 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z) 17 - access(:Z>=:Z AND :Z<=:Z) 30 - access(:Z>=:Z AND :Z<=:Z) 41 - access(:Z>=:Z AND :Z<=:Z) 48 - access(:Z>=:Z AND :Z<=:Z) 50 - access(:Z>=:Z AND :Z<=:Z) Note ----- - automatic DOP: Computed Degree of Parallelism is 2 79 rows selected. SQL>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
