[20181108]with temp as 建立临时表吗.txt

news/2024/2/29 2:47:36

[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会出现在执行计划中。



转载于:https://www.cnblogs.com/lfree/p/9927341.html


http://www.ppmy.cn/news/858362.html

相关文章

join的优化

join的优化 multi_range read优化(mrr) 大多数的数据都是按照顺序来新增的,如果按照顺序对主键进行访问,接近于磁盘的顺序读,提高性能 根据索引定位到满足条件的记录,将id放入read_rnd_buffer将read_rnd_buffer的id进行递增排序排序后的id数组,依次到主键查记录,作为结果返回…

mysql主动自增可以_Mysql join联表及id自增实例解析

join的写法 如果用left join 左边的表一定是驱动表吗?两个表的join包含多个条件的等值匹配,都要写道on还是只把一个写到on,其余写道where部分? createtablea(f1int,f2int,index(f1))engineinnodb; createtableb(f1int,f2int)engin…

java创建map对象吗_[Java教程]JavaScript创建Map对象(转)

/* * MAP对象,实现MAP功能 * * 接口: * size() 获取MAP元素个数 * isEmpty() 判断MAP是否为空 * clear() 删除MAP所有元素 * put(key, value) 向MAP中增加元素(key, value) * remove(key) 删除指定KEY的元素,成功返回True&am…

Unidbg适合做算法还原吗?(一)

Unidbg是模拟执行的强大工具,这是毋庸置疑的,可是,它在算法还原上是否依然是得力的助手?或者说,当我们想要对一个样本进行算法还原而非模拟执行呢,需要关注Unidbg吗? 我们看一下AB两人的辩论&a…

php基本程序打印方法,PHP教程.程序控制-PHP教程,PHP基础

程序控制 本章深入php内部,讲述如何使用函数、表达式和语句以实现对程序的控制。 前面的章节初步介绍了怎样操作数据,如果我们将操作数和操作符看作是构筑元件的话,那么它们组合起来即可形成表达式。进一步讲,表达式可以构成语句&…

matlab保存bln文件,气象万千(冯锦明课题组)-软件程序

气象研究中几种流行的数据分析与绘图软件 GRADS - 简捷易用,但功能较弱,尤其是数据处理功能。 NCL - 针对气象,专业功能强大,实例丰富。函数名难记,部分功能灵活性偏弱。 IDL - 针对地学,功能强大。使用便捷…

global mapper 导入bln文件和tif文件

打开数据文件,注意在bln文件中,这个投影一定要选对。这样就可以将两个文件的信息叠加在一起了。

Golden Software BLN文件格式

Golden Software的BLN文件一种ASCII格式文件,用于存储包括多边形、多段线和点在内的地理信息。空间信息只关心空间中的对象的位置(即它们的坐标),而不是它们的属性(例如,行或填充样式,使用的标记符号,文本标签等等)。尽…

丰密面单打印 隐私面单 C#开发

目录 前言**图一****图二** 1.完成前期准备工作2.API接口3.请求参数(Headers)4.请求参数(Body)5.返回参数(Return)6.请求报文(示例)7.成功返回报文(示例)8.失…

CUDA编译问题-Dual BLN

CUDA编译问题-Dual BLN 问题: Fix “identifier “__builtin_is_constant_evaluated” is undefined” error on Arch Linux The Arch Linux has shipped gcc 9, but the newest CUDA V10.1.168 still only supports gcc 8. So I met following error after upgra…

Mysql学习之三. NLJ和BLN介绍

一.mysql中表关联有两种常见算法 1.1. N L J 嵌套循环算法 (Nested Loop Join) 1.2.B N L 块嵌套循环 (Block Nested Loop Join) 二. 简介 2.1.N L J 嵌套循环算法 (Nested Loop Join) 内连接inner join, 数据库会自主选择驱动表,(以小表驱…

BLN/BNA--surfer文件格式说明

BLN——白化文件 用来白化各种等值线图 格式: length,flag "Pname 1" x1,y1 x2,y2 ... xn,yn length: 即白化范围坐标点数,注意闭合(多边形起点坐标要在最后重复) flag :0 代表白化内部 1代表…

【科研杂记_1】ArcGIS制作.bln格式数据,并在Matlab中绘出

一、制作步骤: 1.基础数据研究区域的shp格式数据 2.工具箱–数据管理工具–要素–要素折点转折点 3.根据如上原理,选择输入的研究区域,此处为面输入,也可以尝试线输入,点类型选择ALL 4.打开输出数据的属性表&#xff0…

通过surfer提取边界bln文件的方法

打开Surfer 13软件,点击New Plot 新建基底图层 3.点击该图层,下方Image Coordinates中输入图片左下角和右上角的经纬度(该操作注意配准的是图片的左下角和右上角坐标,不是图片中图形的左下角和右上角坐标,所以有时需要…

制作bln边界文件

1.在ArcGIS中打开自己需要的区域的shp文件(未选择区域时需要进行裁剪) 2.选择Arctoolbox中的Data Management Tools(数据管理工具) 3.选择要素→要素转线 4.将转好的线文件,选择要素折点转点 5.转成点后,打…

Java文件与IO

文章目录 前言认识文件绝对路径与相对路径普通文件与二进制文件 Java中的操作文件File 类文件的读写 前言认识文件 狭义上的文件(file)。针对硬盘这种持久化存储的I/O设备,当我们想要进行数据保存时,往往不是保存成一个整体,而是独立成一个个…

用于FPGA远程更新的QuickBoot方法

用于FPGA远程更新的QuickBoot方法 用于FPGA远程更新的QuickBoot方法 用于FPGA远程更新的QuickBoot方法1. 远程更新简介2 QuickBoot方案2.1 QuickBoot配置方法2.2 QuickBoot Flash 编程方法 3.QuickBoot实现3.1 Critical Switch World (key point)3.2 QuickBoot存储映射3.3 Bits…

信息查询与信息资源

本书主要介绍在Internet上查询各种信息资源的方法,其中对经济类信息查询有专门的说明。不过,在正式介绍之前,我们需要首先了解一些比较基本的概念和原理,以方便后续书中内容的学习,同时也有助于那些对信息查询原理不是…

go 笔记 第七章 golang 的函数 func 方法

声明函数 func 函数名(入参1 类型, 入参2 类型,… )(出参1 类型, 出参2 类型…){ 函数体,写逻辑 出参一定要全部 return, return 出参 } 函数内部不可以声明带名字的函数,可以声明匿名函数和自执行函数 函数名大写可以被其他包调用&#x…

酒店宾馆wifi无线上网认证,手机认证方式详解

现在有很多酒店宾馆的WiFi网络管理都会遇到这样的问题,就是每次有访客想连接WiFi热点,都需要到前台问密码,管理很麻烦,如果不设置密码的话,又担心有人蹭网,浪费带宽资源,还会利用wifi网络进行非…
最新文章