父游标、子游标及共享游标

        游标是数据库领域较为复杂的一个概念,因为游标包含了shared cursor和session cursor。两者有其不同的概念,也有不同的表现形式。共享游标的概念易于与SQL语句中定义的游标相混淆。本文主要描述解析过程中的父游标,子游标以及共享游标,即shared cursor,同时给出了游标(session cursor)的生命周期以及游标的解析过程的描述。   

            有关游标的定义,声明,与使用请参考:PL/SQL 游标             有关硬解析与软解析请参考:Oracle 硬解析与软解析

一、相关定义         shared cursor                也即是共享游标,是SQL语句在游标解析阶段生成获得的,是位于library cache中的sql或匿名的pl/sql等。其元数据被在视图V$sqlarea与v$sql中具体化。如果library cache中的父游标与子游标能够被共享,此时则为共享游标。父游标能够共享即为共享的父游标,子游标能够共享极为共享的子游标。         session cursor                即通过系统为用户分配缓冲区用于存放SQL语句的执行结果。用户可以通过这个中间缓冲区逐条取出游标中的记录并对其处理,直到所有的游标记录被逐一处理完毕。session cursor指的跟这个session相对应的server process的PGA里(准确的说是UGA)的一块内存区域(或者说内存结构)即其主要特性表现在记录的逐条定位,逐条处理。session cursor的元数据通过v$open_cursor视图来具体化。每一个打开或解析的SQL都将位于该视图。

二、游标的生命周期(session cursor)        session cursor需要从UGA中分配内存,因此有其生命周期。其生命周期主要包括:            打开游标(根据游标声明的名称在UGA中分配内存区域)            解析游标(将SQL语句与游标关联,并将其执行计划加载到Library Cache)            定义输出变量(仅当游标返回数据时)            绑定输入变量(如果与游标关联的SQL语句使用了绑定变量)            执行游标(即执行SQL语句)            获取游标(即获取SQL语句记录结果,根据需要对记录作相应操作。游标将逐条取出查询的记录,直到取完所有记录)            关闭游标(释放UGA中该游标占有的相关资源,但Library Cache中的游标的执行计划按LRU原则清除,为其游标共享提供可能性)

        对于session cursor而言,可以将游标理解为任意的DML,DQL语句(个人理解,有待核实)。即一条SQL语句实际上就是一个游标,只不过session cursor分为显示游标和隐式游标,以及游标指针。由上面游标的生命周期可知,任何的游标(SQL语句)都必须经历内存分配,解析,执行与关闭的过程。故对隐式游标而言,生命周期的所有过程由系统来自动完成。对所有的DML和单行查询(select ... into ...)而言,系统自动使用隐式游标。多行结果集的DQL则通常使用显示游标。

二、游标的解析过程(产生shared cursor)         解析过程:

        A、包含vpd的约束条件:                SQL语句如果使用的表使用了行级安全控制,安全策略生成的约束条件添加到where子句中         B、语法、语义、访问权限检查:                检查SQL语句书写的正确性,对象存在性,用户的访问权限         C、父游标缓存:                将该游标(SQL语句)的文本进行哈希得到哈希值并在library cache寻找相同的哈希值,如不存在则生存父游标且保存在library cache中,按顺序完成D-F步骤。如果此时存在父游标,则进一步判断是否存在子游标。若存在相同的子游标,则直接调用其子游标的执行计划执行该SQL语句,否则转到步骤D进行逻辑优化            D、逻辑优化:                使用不同的转换技巧,生成语义上等同的新的SQL语句(SQL语句的改写),一旦该操作完成,则执行计划数量、搜索空间将会相应增长。其主要目的未进行转换的情况下是寻找无法被考虑到的执行计划         E、物理优化:                为逻辑优化阶段的SQL语句产生执行计划,读取数据字典中的统计信息以及动态采样的统计信息,计算开销,开销最低的执行计划将被选中。         F、子游标缓存:                分配内存,生成子游标(即最佳执行计划),与父游标关联。可以在v$sqlarea, v$sql得到具体游标信息,父子游标通过sql_id关联对于仅仅完成步骤A与B的SQL语句即为软解析,否则即为硬解析

三、shared cursor与session cursor的关系以及软软解析         关系:                       一个session cursor只能对应一个shared cursor,而一个shared cursor却可能同时对应多个session cursor

四、父游标与子游标、共享游标         由游标的解析过程可知,父游标,子游标同属于共享游标的范畴。         父游标              是在进行硬解析时产生的,父游标里主要包含两种信息:SQL文本以及优化目标(optimizer goal),首次打开父游标被锁定,直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被LRU算法置换出library cache,只有在解锁以后才能置换出library cache,此时该父游标对应的所有子游标也同样被置换出library cache。v$sqlarea中的每一行代表了一个parent cursor,address表示其内存地址。        子游标           当发生硬解析时,在产生父游标的同时,则跟随父游标会产生相应的子游标,此时V$SQL.CHILD_NUMBER的值为0。           如果存在父游标,由于不同的运行环境,此时同样会产生新的子游标,新子游标的CHILD_NUMBER在已有子游标基础上以1为单位累计。           子游标包括游标所有相关信息,如具体的执行计划、绑定变量,OBJECT和权限,优化器设置等。子游标随时可以被LRU算法置换出library cache,当子游标被置换出library cache时,oracle可以利用父游标的信息重新构建出一个子游标来,这个过程叫reload。           v$sql中中 的每一行表示了一个child cursor,根据hash value和address与parent cursor 关联。child cursor有自己的address,即v$sql.child_address。

        确定一个游标的三个主要字段:address,hash_value,child_number,

五、演示父游标、子游标  

/************************************ 首先创建表 t  **************************************/                              
	SQL> create table t as select empno,ename,sal from emp where deptno=10;                                                
	                                                                                                                       
	Table created.                                                                                                         
*********************************** 对表进行查询 *****************************************/		                           
	SQL> select * from t where empno=7782;                                                                                 
	                                                                                                                       
	     EMPNO ENAME             SAL                                                                                       
	---------- ---------- ----------                                                                                       
	      7782 CLARK            2450                                                                                       
	                                                                                                                       
	SQL> SELECT * from t where empno=7782;                                                                                 
	                                                                                                                       
	     EMPNO ENAME             SAL                                                                                       
	---------- ---------- ----------                                                                                       
	      7782 CLARK            2450                                                                                       
	                                                                                                                       
	SQL> SELECT * FROM t WHERE empno=7782;                                                                                 
	                                                                                                                       
	     EMPNO ENAME             SAL                                                                                       
	---------- ---------- ----------                                                                                       
	      7782 CLARK            2450                                                                                       
	                                                                                                                       
	SQL> select * from t where empno=7782;                                                                                 
	                                                                                                                       
	     EMPNO ENAME             SAL                                                                                       
	---------- ---------- ----------                                                                                       
	      7782 CLARK            2450                                                                                       
	                                                                                                                       
	/*********************由下面的查询(v$sqlarea)可知产生了3个父游标,其中一个父游标(2r6rbdp92kyh9)执行了2次 ************/ 
	/**************************************************/                                                                   
	/* Author: Robinson Cheng                         */                                                                   
	/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
	/* MSN:    robinson_0612@hotmail.com              */                                                                   
	/* QQ:     645746311                              */                                                                   
	/**************************************************/                                                                   
                                                                                                                         
	SQL> col sql_text format a40                                                                                           
	SQL> select sql_id,sql_text,executions from v$sqlarea                                                                  
	  2  where sql_text like '%empno=7782%' and sql_text not like '%from v$sqlarea%';                                      
	                                                                                                                       
	SQL_ID        SQL_TEXT                                 EXECUTIONS                                                      
	------------- ---------------------------------------- ----------                                                      
	4rs2136z084y1 SELECT * from t where empno=7782                  1                                                      
	84w067b4n91h5 SELECT * FROM t WHERE empno=7782                  1                                                      
	2r6rbdp92kyh9 select * from t where empno=7782                  2                                                      
	                                                                                                                       
	/************上面3个父游标对应的子游标可以在v$sql中获得 *******************/                                           
	SQL> select sql_id,hash_value,child_number,plan_hash_value,sql_text,executions from v$sql                              
	  2  where sql_text like '%empno=7782%' and sql_text not like '%from v$sql%';                                          
	                                                                                                                       
	SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT                                 EXECUTIONS              
	------------- ---------- ------------ --------------- ---------------------------------------- ----------              
	4rs2136z084y1 3187938241            0      1601196873 SELECT * from t where empno=7782                  1              
	84w067b4n91h5 3376711173            0      1601196873 SELECT * FROM t WHERE empno=7782                  1              
	2r6rbdp92kyh9 1378449929            0      1601196873 select * from t where empno=7782                  2              
	                                                                                                                       
	/******************调整optimizer_index_caching 参数并执行聚合查询 ************************/                            
	SQL> alter session set optimizer_index_caching=40;                                                                     
	                                                                                                                       
	Session altered.                                                                                                       
	                                                                                                                       
	SQL> select sum(sal) from t;                                                                                           
	                                                                                                                       
	  SUM(SAL)                                                                                                             
	----------                                                                                                             
	      8750                                                                                                             
	                                                                                                                       
	SQL> alter session set optimizer_index_caching=100;                                                                    
	                                                                                                                       
	Session altered.                                                                                                       
	                                                                                                                       
	SQL> select sum(sal) from t;                                                                                           
	                                                                                                                       
	  SUM(SAL)                                                                                                             
	----------                                                                                                             
	      8750                                                                                                             
	                                                                                                                       
	/***************相同的查询由于不同的运行环境导致产生了不同的子游标,optimizer_env_hash_value值不同 **************/     
	/***************不同的子游标有不同的child_address 值         ****************************/                             
	SQL> select sql_id, child_number, sql_text,optimizer_env_hash_value oehv,child_address                                 
	  2  from v$sql where sql_text like '%sum(sal)%' and sql_text not like '%from v$sql%';                                 
	                                                                                                                       
	SQL_ID        CHILD_NUMBER SQL_TEXT                                       OEHV CHILD_ADDRESS                           
	------------- ------------ ---------------------------------------- ---------- ----------------                        
	gu68ka2qzx3hh            0 select sum(sal) from t                   3620536549 0000000093696D00                        
	gu68ka2qzx3hh            1 select sum(sal) from t                   2687219005 0000000093767F58                        
	                                                                                                                       
	/********** 查询v$sql_shared_cursor可以跟踪是那些变化导致了子游标不能共享,此例为optimizer_mismatch *****************/  
	SQL> SELECT child_number, optimizer_mismatch                                                                           
	  2  FROM v$sql_shared_cursor                                                                                          
	  3  WHERE sql_id = '&sql_id';                                                                                         
	Enter value for sql_id: gu68ka2qzx3hh                                                                                  
	old   3: WHERE sql_id = '&sql_id'                                                                                      
	new   3: WHERE sql_id = 'gu68ka2qzx3hh'                                                                                
	                                                                                                                       
	CHILD_NUMBER O                                                                                                         
	------------ -                                                                                                         
	           0 N                                                                                                         
	           1 Y                                                                                                         
	/***********************观察父游标address,hash_value,sql_id ******************/                                        
	/***********************观察子游标address,hash_value,child_number,sql_id,child_address ******************/             
	/************************从Oracle 10g 之后,sql_id既可以唯一确定一个父游标,sql_id,child_number唯一确定一个子游标*****/
	SQL> SELECT address,hash_value,sql_id FROM v$sqlarea  WHERE sql_id='gu68ka2qzx3hh';                                    
	                                                                                                                       
	ADDRESS          HASH_VALUE SQL_ID                                                                                     
	---------------- ---------- -------------                                                                              
	000000009F8CBB58 2919140880 gu68ka2qzx3hh                                                                              
	                                                                                                                       
	SQL> SELECT address,hash_value,child_number, sql_id,child_address                                                      
	  2  FROM v$sql WHERE sql_id='gu68ka2qzx3hh';                                                                          
	                                                                                                                       
	ADDRESS          HASH_VALUE CHILD_NUMBER SQL_ID        CHILD_ADDRESS                                                   
	---------------- ---------- ------------ ------------- ----------------                                                
	000000009F8CBB58 2919140880            0 gu68ka2qzx3hh 0000000093696D00                                                
	000000009F8CBB58 2919140880            1 gu68ka2qzx3hh 0000000093767F58                                                

六、总结         1、硬解析通常是由于不可共享的父游标造成的,如经常变动的SQL语句,或动态SQL或未使用绑定变量等         2、解决硬解析的办法则通常是使用绑定变量来解决         3、与父游标SQL文本完全一致的情形下,多个相同的SQL语句可以共享一个父游标         4、SQL文本、执行环境完全一致的情形下,子游标能够被共享,否则如果执行环境不一致则生成新的子游标

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

MySQL8.0之数据字典

MySQL 8.0 将数据库元信息都存放于InnoDB存储引擎表中,在之前版本的MySQL中,数据字典不仅仅存放于特定的存储引擎表中,还存放于元数据文件、非事务...

58990
来自专栏木头编程 - moTzxx

后台 配置页面功能设计

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u011415782/article/de...

68540
来自专栏程序猿

SQL注入分析服务器类型

分析数据库服务器类型 一般来说,ACCESS与SQL-SERVER是最常用的数据库服务器,尽管它们都支持T-SQL标准,但还有不同之处,而且不同的数据库有不同的...

32860
来自专栏沃趣科技

配置详解 | performance_schema全方位介绍

在上一篇 《初相识 | performance_schema全方位介绍》 中粗略介绍了如何配置与使用performance_schema,相信大家对perfor...

1.9K70
来自专栏测试驿栈

SQL注入详解

SQL注入是一种将SQL代码插入或添加到应用(用户)的输入参数中的攻击,之后再将这些参数传递给后台的SQL服务器加以解析并执行。

1K30
来自专栏数据和云

【动手实践】Oracle 12.2 新特性:自动的列表分区创建

2017年来了,我们要启动新的学习征程了。在过去我们一直思考,什么样的内容能够更帮助大家了解和学习到有用的知识? 这个『动手实践』栏目就是这样一个改进和尝试吧,...

30160
来自专栏开发与安全

Mysql数据库学习(一):数据库基本概念、关系型数据库、Mysql数据库安装配置、DDL/DCL/DML语句

一、数据库基本概念 数据库(DB) 按照数据结构来组织、存储数据的仓库 数据库管理系统(DBMS) 数据库管理系统(Database Manag...

22700
来自专栏散尽浮华

Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlo...

40380
来自专栏Java架构师历程

MYSQL 浅谈MyISAM 存储引擎

       mysql中用的最多存储引擎就是innodb和myisam。做为Mysql的默认存储引擎,myisam值得我们学习一下,以下是我对《高性能MYSQ...

90420
来自专栏小怪聊职场

MySQL(一)|性能分析方法、SQL性能优化和MySQL内部配置优化

777100

扫码关注云+社区

领取腾讯云代金券