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临时表，主要差异如下:
.在查询结束时数据库释放内存与临时段而不是当rowsource不现活动时释放。
.内存中的数据仍然存储在内存中，不像排序数据可能在内存与临时段之间移动。
当数据库使用cursor-duration临时表时，关键字cursordurationmemory会出现在执行计划中。
cursor-duration临时表使用场景
一个with查询重复相同子查询多次可能有时使用cursor-duration临时表性能更高，下面的查询使用一个with子句来创建三个子查询块:
SQL>setlong99999 SQL>setlinesize300 SQL>with 2q1as(selectdepartment_id,sum(salary)sum_salfromhr.employeesgroupby 3department_id), 4q2as(select*fromq1), 5q3as(selectdepartment_id,sum_salfromq1) 6select*fromq1 7unionall 8select*fromq2 9unionall 10select*fromq3; DEPARTMENT_IDSUM_SAL ----------------------- 10051608 3024900 7000 9058000 2019000 7010000 11020308 50156400 80304500 406500 6028800 104400 10051608 3024900 7000 9058000 2019000 7010000 11020308 50156400 80304500 406500 6028800 104400 10051608 3024900 7000 9058000 2019000 7010000 11020308 50156400 80304500 406500 6028800 104400 36rowsselected.
下面是优化转换后的执行计划
SQL>select*fromtable(dbms_xplan.display_cursor(format=>'basic+rows+cost')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- EXPLAINEDSQLSTATEMENT: ------------------------ withq1as(selectdepartment_id,sum(salary)sum_salfromhr.employees groupbydepartment_id),q2as(select*fromq1),q3as(select department_id,sum_salfromq1)select*fromq1unionallselect* fromq2unionallselect*fromq3 Planhashvalue:4087957524 ---------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Cost(%CPU)| PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT|||6(100)| |1|TEMPTABLETRANSFORMATION|||| |2|LOADASSELECT(CURSORDURATIONMEMORY)|SYS_TEMP_0FD9E08D2_620789C||| |3|HASHGROUPBY||11|276(2)| |4|TABLEACCESSFULL|EMPLOYEES|100K|273(1)| |5|UNION-ALL|||| |6|VIEW||11|2(0)| |7|TABLEACCESSFULL|SYS_TEMP_0FD9E08D2_620789C|11|2(0)| |8|VIEW||11|2(0)| |9|TABLEACCESSFULL|SYS_TEMP_0FD9E08D2_620789C|11|2(0)| |10|VIEW||11|2(0)| |11|TABLEACCESSFULL|SYS_TEMP_0FD9E08D2_620789C|11|2(0)| ---------------------------------------------------------------------------------------------------- 26rowsselected.
在上面的执行计划中，在步骤1中的TEMPTABLETRANSFORMATION指示数据库使用cursor-duration临时表来执行查询。在步骤2中的CURSORDURATIONMEMORY指示数据库使用内存，如果有可用内存，将结果作为临时表SYS_TEMP_0FD9E08D2_620789C来进行存储。如果没有可用内存，那么数据库将临时数据写入磁盘。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。