Oracle 12CR2中cursor

135次阅读

共计 3393 个字符，预计需要花费 9 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 Oracle 12CR2 中 cursor-duration 临时表怎么用，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

在 Oracle12C 中为了物化查询的中间结果，Oracle 数据库在查询编译时在内存中可能会隐式的创建一个 cursor_duration 临时表。

Cursor-Duration 临时表的作用
复杂查询有时会处理相同查询块多次，这将会增加不必要的性能开锁。为了避免这种问题，Oracle 数据库可以在游标生命周期内为查询结果创建临时表并存储在内存中。对于有 with 子句查询，星型转换与分组集合操作的复杂操作，这种优化增强了使用物化中间结果来优化子查询。在这种方式下，cursor-duration 临时表提高了性能并且优化了 I /O。

Cursor-Duration 临时表工作原理
cursor-definition 临时表定义内置在内存中。表定义与游标相关，并且只对执行游标的会话可见。当使用 cursor-duration 临时表时，数据库将执行以下操作:
1. 选择使用 cursor-duration 临时表的执行计划
2. 创建临时表时使用唯一名
3. 重写查询引用临时表
4. 加载数据到内存中直到没有内存可用，在这种情次品下将在磁盘上创建临时段
5. 执行查询，从临时表中返回数据
6.truncate 表，释放内存与任何磁盘上的临时段

注意，cursor-duration 临时表的元数据只要 cursor 在内存中就会一直存在于内存中。元数据不会存储在数据字典中这意味着通过数据字典视图将不能查询到，不能显性地删除元数据。上面的场景依赖于可用的内存。对于特定查询，临时表使用 PGA 内存。

cursor-duration 临时表的实现类似于排序。如果没有可用内存，那么数据库将把数据写入临时段。对于 cursor-duration 临时表，主要差异如下:
. 在查询结束时数据库释放内存与临时段而不是当 row source 不现活动时释放。

. 内存中的数据仍然存储在内存中，不像排序数据可能在内存与临时段之间移动。
当数据库使用 cursor-duration 临时表时，关键字 cursor duration memory 会出现在执行计划中。

cursor-duration 临时表使用场景
一个 with 查询重复相同子查询多次可能有时使用 cursor-duration 临时表性能更高，下面的查询使用一个 with 子句来创建三个子查询块:

SQL  set long 99999
SQL  set linesize 300
SQL  with
 2 q1 as (select department_id, sum(salary) sum_sal from hr.employees group by
 3 department_id),
 4 q2 as (select * from q1),
 5 q3 as (select department_id, sum_sal from q1)
 6 select * from q1
 7 union all
 8 select * from q2
 9 union all
 10 select * from q3;
DEPARTMENT_ID SUM_SAL
------------- ----------
 100 51608
 30 24900
 7000
 90 58000
 20 19000
 70 10000
 110 20308
 50 156400
 80 304500
 40 6500
 60 28800
 10 4400
 100 51608
 30 24900
 7000
 90 58000
 20 19000
 70 10000
 110 20308
 50 156400
 80 304500
 40 6500
 60 28800
 10 4400
 100 51608
 30 24900
 7000
 90 58000
 20 19000
 70 10000
 110 20308
 50 156400
 80 304500
 40 6500
 60 28800
 10 4400
36 rows selected.

下面是优化转换后的执行计划

SQL  select * from table(dbms_xplan.display_cursor(format= basic +rows +cost 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
with q1 as (select department_id, sum(salary) sum_sal from hr.employees
group by department_id), q2 as (select * from q1), q3 as (select
department_id, sum_sal from q1) select * from q1 union all select *
from q2 union all select * from q3
Plan hash value: 4087957524
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)|
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9E08D2_620789C | | |
| 3 | HASH GROUP BY | | 11 | 276 (2)|
| 4 | TABLE ACCESS FULL | EMPLOYEES | 100K| 273 (1)|
| 5 | UNION-ALL | | | |
| 6 | VIEW | | 11 | 2 (0)|
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | 2 (0)|
| 8 | VIEW | | 11 | 2 (0)|
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | 2 (0)|
| 10 | VIEW | | 11 | 2 (0)|
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9E08D2_620789C | 11 | 2 (0)|
----------------------------------------------------------------------------------------------------
26 rows selected.

在上面的执行计划中，在步骤 1 中的 TEMP TABLE TRANSFORMATION 指示数据库使用 cursor-duration 临时表来执行查询。在步骤 2 中的 CURSOR DURATION MEMORY 指示数据库使用内存，如果有可用内存，将结果作为临时表 SYS_TEMP_0FD9E08D2_620789C 来进行存储。如果没有可用内存，那么数据库将临时数据写入磁盘。

看完了这篇文章，相信你对“Oracle 12CR2 中 cursor-duration 临时表怎么用”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

正文完