[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
=====================
--//从语法看建立的不上临时表,或者仅仅是一种特殊的临时表.我查询一下,发现如下链接:
http://www.jydba.net/oracle-12cr2%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会出现在执行计划中。