Oracle 12CR2查詢轉換教程之cursor

142次閱讀

共計 3440 個字符，預計需要花費 9 分鐘才能閱讀完成。

行業資訊
數據庫
Oracle 12CR2 查詢轉換教程之 cursor-duration 臨時表的示例分析

丸趣 TV 小編給大家分享一下 Oracle 12CR2 查詢轉換教程之 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 行業資訊頻道！

正文完