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来进行存储。如果没有可用内存，那么数据库将临时数据写入磁盘。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对小牛知识库的支持。