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來進行儲存。如果沒有可用記憶體,那麼資料庫將臨時資料寫入磁碟。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-2217914/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 12CR2查詢轉換之臨時錶轉換Oracle
- Oracle 12CR2查詢轉換之表擴充套件Oracle套件
- Oracle 12CR2查詢轉換之謂詞推送Oracle
- Oracle 12CR2查詢轉換之星型轉換Oracle
- Oracle 12CR2查詢轉換之檢視合併Oracle
- Oracle臨時表 優化查詢速度Oracle優化
- Oracle臨時表最佳化查詢速度Oracle
- Oracle臨時表 最佳化查詢速度Oracle
- Oracle下查詢臨時表空間佔用率Oracle
- 【SQL】Oracle查詢轉換之 OR用法SQLOracle
- oracle 表空間,臨時表空間使用率查詢Oracle
- Oracle 查詢轉換Oracle
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- 【SQL】Oracle查詢轉換之謂詞推送SQLOracle
- Oracle 查詢轉換初探Oracle
- Oracle中用子查詢建立臨時表 並賦值資料Oracle賦值
- ORACLE Temporary Tables臨時表更適合做插入和查詢操作Oracle
- oracle臨時表的用法(轉)Oracle
- Mysql 查詢時間轉換MySql
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- Oracle 查詢轉換-01 or expansionOracle
- Oracle查詢轉換(五)子查詢展開Oracle
- 巧用臨時表將大結果集轉換為小結果集驅動查詢薦
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- oracle 查詢什麼sql佔用臨時段OracleSQL
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- 臨時表空間被佔滿的原因查詢
- PHP專案中如何用PDO查詢臨時表?PHP
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- Oracle的臨時表Oracle
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查:Oracle
- oracle臨時表空間的增刪改查Oracle
- 查詢轉換
- MySQL之臨時表MySql
- Oracle 查詢轉換-04 Subquery UnnestingOracle