[20181108]with temp as 建立臨時表嗎.txt
[20181108]with temp as 建立臨時表嗎.txt
--//連結:http://www.itpub.net/thread-2106304-1-1.html
--//作者提到在dg上使用with查詢的sql語句報錯.出現如下錯誤:
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access
--//我在11.2.0.3以及11.2.0.4上使用dg都無法再現作者遇到的問題,不過裡面提到要建立一個臨時表.我跟蹤看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試:
SCOTT@book> @ 10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@book> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
SCOTT@book> @ 10046off
Session altered.
SCOTT@book> @ pp
TRACEFILE
--------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16666.trc
SCOTT@book> @ dpc b52t0afdmxyxf ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b52t0afdmxyxf, child number 0
-------------------------------------
with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by
deptno) select * from x1
Plan hash value: 2072880957
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 3 |00:00:00.01 | 22 | 7 | 1 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 3 |00:00:00.01 | 22 | 7 | 1 | | | |
| 2 | LOAD AS SELECT | | 1 | | | | | 0 |00:00:00.01 | 10 | 6 | 1 | 270K| 270K| 270K (0)|
| 3 | HASH GROUP BY | | 1 | 3 | 21 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | 6 | 0 | 1214K| 1214K| 1259K (0)|
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 | 98 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | 6 | 0 | | | |
| 5 | VIEW | | 1 | 3 | 78 | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 6 | 1 | 0 | | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6600_176524F2 | 1 | 3 | 21 | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 6 | 1 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$1
4 - SEL$1 / EMP@SEL$1
5 - SEL$D67CB2D2 / X1@SEL$2
6 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
--//可以發現執行計劃中出現一個表SYS_TEMP_0FD9D6600_176524F2.
3.檢查跟蹤檔案發現如下:
=====================
PARSING IN CURSOR #140363556544784 len=177 dep=1 uid=0 oct=1 lid=0 tim=1541638752385719 hv=3738467917 ad='7be14610' sqlid='5j608hvgd8zkd'
CREATE GLOBAL TEMPORARY T
~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140363556544784:c=1000,e=693,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1541638752385717
BINDS #140363556569464:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=7fa8efe44fc0 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
kxsbbbfp=7fa8efe44fd8 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D6600_176524F2"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=7fa8efe44ff8 bln=22 avl=02 flg=01
value=1
EXEC #140363556569464:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1541638752386116
FETCH #140363556569464:c=0,e=19,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1541638752386157
CLOSE #140363556569464:c=0,e=2,dep=2,type=3,tim=1541638752386194
=====================
--//注意看下劃下顯示資訊不全,11.2.0.4都無法完整顯示建表語句.這樣看上去建立一個臨時表的資訊.
SCOTT@book> @ /desc sys.SYS_TEMP_0FD9D6600_176524F2
Name Null? Type
------------------------------- -------- ----------------------------
C0 NUMBER(2)
C1 NUMBER
--//查詢可以發現該表名,實際上如果你重新整理共享池後下次執行,可以發現這個臨時表名會發生變化,我不做這測試,大家可以自行驗證.
4.進一步驗證:
--//連結提到:http://www.itpub.net/thread-2106304-1-1.html
*** 2018-11-06 17:45:04.545
KQRCMT: Write failed with error=604 po=00000006E229E0C8 cid=8
diagnostics : cid=8 hash=3374ad43 flag=2a
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6837_3AF8797C" ("C0" CHARACTER(36),"C1" NUMBER,"C2" DATE,"C3"
NUMBER,"C4" DATE ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254951479 ) NOPARALLEL
----- Current SQL Statement for this session (sql_id=avk2qrjwhx4zb) -----
--//可以看到類似建立臨時表的語句.注意後面的引數IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT ,如果那這個語句在sql無法正常執
--//行的.
5.如果類似語句在12c下執行呢?
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b52t0afdmxyxf, child number 0
-------------------------------------
with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by
deptno) select * from x1
Plan hash value: 2072880957
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660A_174F61 | | | | | 1024 | 1024 | |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 | 1214K| 1214K| 1255K (0)|
| 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 | | | |
| 5 | VIEW | | 3 | 78 | 2 (0)| 00:00:01 | | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_174F61 | 3 | 21 | 2 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$1
4 - SEL$1 / EMP@SEL$1
5 - SEL$D67CB2D2 / X1@SEL$2
6 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
--//注意看id=2.Operation=LOAD AS SELECT (CURSOR DURATION MEMORY),id=1有1個操作TEMP TABLE TRANSFORMATION,我的理解轉換為臨
--//時表.也就是12c表示更加明確一些.重新整理共享池後,使用10046 跟蹤看看.
SCOTT@test01p> alter system flush shared_pool ;
System altered.
SCOTT@test01p> @ 10046on 12
Session altered.
SCOTT@test01p> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
SCOTT@test01p> @ 10046off
Session altered.
--//檢查跟蹤檔案,可以發現如下:
=====================
PARSING IN CURSOR #698582680 len=189 dep=1 uid=0 oct=1 lid=0 tim=2928951715 hv=3568969204 ad='7ff0a173d50' sqlid='6d7zw7rabn9gn'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660B_174F61" SHARING=NONE ("C0" NUMBER(2),"C1" NUMBER )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950923 ) NOPARALLEL
--//12c能完整顯示建表語句.
END OF STMT
PARSE #698582680:c=0,e=4795,p=0,cr=48,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=2928951714
BINDS #698686824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=2a461ab8 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(25) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
kxsbbbfp=2a461ad0 bln=32 avl=25 flg=01
value="SYS_TEMP_0FD9D660B_174F61"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=2a461af0 bln=22 avl=02 flg=01
value=1
EXEC #698686824:c=15600,e=249,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=2928952150
FETCH #698686824:c=0,e=11,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=2928952186
CLOSE #698686824:c=0,e=1,dep=2,type=3,tim=2928952220
BINDS #698742424:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a461af0 bln=22 avl=06 flg=05
value=4254950923
EXEC #698742424:c=0,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=2928952385
FETCH #698742424:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=2928952419
CLOSE #698742424:c=0,e=1,dep=2,type=3,tim=2928952454
=====================
--//從語法看建立的不上臨時表,或者僅僅是一種特殊的臨時表.我查詢一下,發現如下連結:
%e6%9f%a5%e8%af%a2%e8%bd%ac%e6%8d%a2%e4%b9%8bcursor-duration%e4%b8%b4%e6%97%b6%e8%a1%a8/
Oracle 12CR2查詢轉換之cursor-duration臨時表
在Oracle12C中為了物化查詢的中間結果，Oracle資料庫在查詢編譯時在記憶體中可能會隱式的建立一個cursor_duration臨時表。
--//這種臨時表叫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會出現在執行計劃中。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2219162/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Temp 臨時表空間Oracle
- Oracle TEMP臨時表空間概念Oracle
- Oracle Temp臨時表空間處理Oracle
- Oracle基礎 02 臨時表空間 tempOracle
- mysql 建立臨時表MySql
- SQL建立臨時表SQL
- Oracle 12c 新特性 - 臨時表undo(TEMP UNDO)Oracle
- 【TEMP】臨時表空間的工作原理及維護方法
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 臨時表空間的建立、刪除，設定預設臨時表空間
- SQLServer資料庫中建立臨時表SQLServer資料庫
- [20190410]dg建立臨時表檔案資料檔案.txt
- 12C關於CDB、PDB 臨時temp表空間的總結
- Oracle 11g中Temp臨時表空間、檔案的新特性Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 重構之臨時變數(Replace Temp with Query)變數
- [20180104]oracle臨時表.txtOracle
- oracle外部表建立以及收集統計資訊以及臨時表Oracle
- 怎麼清理temp資料夾的臨時檔案？Win7系統電腦temp臨時檔案的清理方法Win7
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- 建立基於事務和基於會話的臨時表及臨時表建索引的實驗會話索引
- MySQL臨時表MySql
- PostgreSQL：臨時表SQL
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- [20180105]oracle臨時表補充.txtOracle
- SQL Server通過建立臨時表遍歷更新資料SQLServer
- MySQL之臨時表MySql
- Oracle的臨時表Oracle
- 臨時表的操作
- 全域性臨時表
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Oracle 12c 新特性之臨時Undo--temp_undo_enabledOracle
- 你好奇過 MySQL 內部臨時表存了什麼嗎？MySql
- Oracle中用子查詢建立臨時表 並賦值資料Oracle賦值
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- TEMP表空間不足解決 - temp group
- oracle 臨時表的使用Oracle