Oracle 12CR2查詢轉(zhuǎn)換教程之cursor-duration臨時表詳解

更新時間：2018年11月01日 10:08:05 作者：jydba

這篇文章主要給大家介紹了關于Oracle 12CR2查詢轉(zhuǎn)換教程之cursor-duration臨時表的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

前言

在Oracle12C中為了物化查詢的中間結(jié)果，Oracle數(shù)據(jù)庫在查詢編譯時在內(nèi)存中可能會隱式的創(chuàng)建一個cursor_duration臨時表。

下面話不多說了，來一起看看詳細的介紹吧

Cursor-Duration臨時表的作用

復雜查詢有時會處理相同查詢塊多次，這將會增加不必要的性能開鎖。為了避免這種問題，Oracle數(shù)據(jù)庫可以在游標生命周期內(nèi)為查詢結(jié)果創(chuàng)建臨時表并存儲在內(nèi)存中。對于有with子句查詢，星型轉(zhuǎn)換與分組集合操作的復雜操作，這種優(yōu)化增強了使用物化中間結(jié)果來優(yōu)化子查詢。在這種方式下，cursor-duration臨時表提高了性能并且優(yōu)化了I/O。

Cursor-Duration臨時表工作原理

cursor-definition臨時表定義內(nèi)置在內(nèi)存中。表定義與游標相關，并且只對執(zhí)行游標的會話可見。當使用cursor-duration臨時表時，數(shù)據(jù)庫將執(zhí)行以下操作:

1.選擇使用cursor-duration臨時表的執(zhí)行計劃

2.創(chuàng)建臨時表時使用唯一名

3.重寫查詢引用臨時表

4.加載數(shù)據(jù)到內(nèi)存中直到?jīng)]有內(nèi)存可用，在這種情次品下將在磁盤上創(chuàng)建臨時段

5.執(zhí)行查詢，從臨時表中返回數(shù)據(jù)

6.truncate表，釋放內(nèi)存與任何磁盤上的臨時段

注意，cursor-duration臨時表的元數(shù)據(jù)只要cursor在內(nèi)存中就會一直存在于內(nèi)存中。元數(shù)據(jù)不會存儲在數(shù)據(jù)字典中這意味著通過數(shù)據(jù)字典視圖將不能查詢到，不能顯性地刪除元數(shù)據(jù)。上面的場景依賴于可用的內(nèi)存。對于特定查詢，臨時表使用PGA內(nèi)存。

cursor-duration臨時表的實現(xiàn)類似于排序。如果沒有可用內(nèi)存，那么數(shù)據(jù)庫將把數(shù)據(jù)寫入臨時段。對于cursor-duration臨時表，主要差異如下:

.在查詢結(jié)束時數(shù)據(jù)庫釋放內(nèi)存與臨時段而不是當row source不現(xiàn)活動時釋放。

.內(nèi)存中的數(shù)據(jù)仍然存儲在內(nèi)存中，不像排序數(shù)據(jù)可能在內(nèi)存與臨時段之間移動。

當數(shù)據(jù)庫使用cursor-duration臨時表時，關鍵字cursor duration memory會出現(xiàn)在執(zhí)行計劃中。

cursor-duration臨時表使用場景

一個with查詢重復相同子查詢多次可能有時使用cursor-duration臨時表性能更高，下面的查詢使用一個with子句來創(chuàng)建三個子查詢塊:

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.

下面是優(yōu)化轉(zhuǎn)換后的執(zhí)行計劃

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.

在上面的執(zhí)行計劃中，在步驟1中的TEMP TABLE TRANSFORMATION指示數(shù)據(jù)庫使用cursor-duration臨時表來執(zhí)行查詢。在步驟2中的CURSOR DURATION MEMORY指示數(shù)據(jù)庫使用內(nèi)存，如果有可用內(nèi)存，將結(jié)果作為臨時表SYS_TEMP_0FD9E08D2_620789C來進行存儲。如果沒有可用內(nèi)存，那么數(shù)據(jù)庫將臨時數(shù)據(jù)寫入磁盤。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章: