开发者社区> 技术小能手> 正文

Library Cache优化与SQL游标

简介:
+关注继续查看

Dear
跟着小编一起读好书运动开始啦!咱们作为DBA不仅要外部打扮自己,更要从内部武装。近期,小编将分享冷菠老师的《Oracle高性能自动化运维》一部分精选章节分享给大家。如果你对内容很感兴趣,还是要去买一本比较好哦。

image
冷菠

冷菠,网名悠然(个人主页http://www.orasky.net ),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队管理以及培训经验。曾担任美资企业Senior DBA职务、支付公司数据库团队负责人,现为培训机构重庆优唯佳科技公司技术合伙人。
擅长数据库备份恢复、数据库性能诊断优化以及数据库自动化运维等,对主机存储、网络、系统业务架构设计优化、大数据等领域有较为深入的研究。目前致力于大数据、智能一体化、开源云计算等领域的佳实践探索。

Library Cache主要用于存放SQL游标,而SQL游标最大化共享是Library Cache优化的重要途径,可以使SQL运行开销最低、性能最优。


SQL语句与父游标及子游标

在PL/SQL中,游标(Cursor)是数据集遍历的内存集合。而从广义上讲, 游标是SQL语句在Library Cache中的内存载体。

SQL语句与游标关系如下:

1.一条SQL语句包含一个父游标(Parent Cursor)和一到多个子游标(Child Cursors),如图2-2所示。

image
图2-2 SQL语句与游标

2.SQL语句通过SQL_ID唯一标识父游标,如下所示:


image

从上述示例可以看出,SQL语句使用SQL_ID唯一标识父游标(V$SQLAREA),同时该SQL语句仅包含一父游标和一个子游标。

3.不同的SQL语句的父游标也不同,如下所示:


image

可以看出,2个不同SQL语句对应的SQL_ID也不相同,产生了不同的父游标。

小提示

当SQL语句父游标不相同,其对应的子游标也肯定不同。


父游标

1.父游标特点

父游标的主要特点如下:

q 父游标是由SQL语句决定;
q 父游标使用SQL语句的SQL_ID唯一标识;
q 父游标包含一到多个子游标;
q 父游标与参数cursor_sharing紧密相关。

2.父游标组成结构

父游标的主要组成结构如表2-2所示:


image
表2-2 父游标组成结构

父游标组成结构单元之间的关系,如图2-3所示:


image
图2-3 父游标组成结构

3.父游标相关查询

父游标信息可以通过V$SQLAREA视图进行查询。

V$SQLAREA主要特点有:

  • V$SQLAREA中一条记录表示一个父游标,如下所示:


image


可以看出在V$SQLAREA视图中,SQL_ID是唯一的,从侧面也可以说V$SQLAREA中一条记录代表一个父游标。

  • V$SQLAREA只包含父游标的相关信息。

4.父游标相关参数

参数cursor_sharing决定父游标被共享的模式,用于减少解析带来的开销,提升SQL执行效率。

cursor_sharing的3种模式:

  • EXACT (默认模式),如下所示:


image

  • FORCE
  • SIMILAR

接下来对3种模式进行详细介绍。

  • cursor_sharing= EXACT

默认模式。只有SQL语句内容完全一样,才会共享父游标(SQL语句之间才会共享)。也就是说,当用户端发起的SQL语句只要有一点不相同,就会产生不同的父游标,从而不会共享SQL父游标。如下所示:


image

  • cursor_sharing = FORCE

当模式设置为FOCE时,将会强制优化器共享父游标,而不管执行计划是否最优。当条件允许时,可以采用这种方式来减少解析开销。如下所示:


image


可以看出,在FORCE模式下,将2条内容不同的SQL强制共享父游标(使用系统绑定变量)。

小提示

FORCE模式建议不要过度使用,虽然这种模式会强制SQL共享父游标,但是这样可能会忽略CBO优化器最优的执行计划,使得SQL执行不是最优化的。

  • cursor_sharing = SIMILAR

模式SIMILAR表示优化器在一定条件下会自动选择共享游标:

  • 当SQL语句几乎完全相同时;
  • 当执行计划相同或者执行计划更优时;
  • 当忽略SQL语句文字内容差异共享游标

可以通过以下示例进行验证:

  • 示例1:参数变化导致游标共享差异。


image

可以看出,当模式设置为SIMILAR时,只要SQL语句相似就可以共享游标 。

  • 示例2:父子游标。


image


示例2可以概括为图2-4:


image
图2-4 父子游标与cursor_sharing

通过图2-4可以看到,一个父游标可以包含多个子游标,验证了图2-2的正确性。


子游标

1.子游标特点

子游标的主要特点有:

  • V$SQL中一条记录对应一个子游标
  • 子游标与绑定变量(Bind Variable)、NLS参设置等相关
  • 子游标与参数optimizer_mode紧密相关

2.子游标组成结构

子游标的主要组成结构如表2-3所示:


image
表2-3 子游标组成结构

子游标组成结构单元之间的关系,如图2-5所示:


image
图2-5 子游标组成结构

3.子体游标相关查询

子游标信息可以通以V$SQL(X$KGLCURSOR_CHILD视图进行查询。

V$SQL主要特点有:

  • V$SQL中一条记录代表一个子游标。如下所示:


image

可以看到,一个SQL_ID(父游标)包含了多条记录,每条记录代表一个子游标。

V$SQL包含了父游标和子游标信息。

4.子游标相关参数

参数optimizer_mode用于设置子游标的CBO优化器模式。

可以通过查询V$SQL_SHARED_CURSOR. OPTIMIZER_MISMATCH验证子游标不匹配(missmatch)原因:是否由参数optimizer_mode导致的。如下所示:

image

可以将上面内容可以概括如图2-6所示:

image
图2-6 父子游标与optimizer_mode

来源:数据和云
原文链接

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
java202303java学习笔记第四十四天函数-使用规则-进阶sql优化1
java202303java学习笔记第四十四天函数-使用规则-进阶sql优化1
13 0
重现一条简单SQL的优化过程
重现一条简单SQL的优化过程
10778 0
vMySQL的explain解释SQL执行计划,优化SQL执行和创建索引
这里举例说明如何查看MySQL的SQL执行计划,并根据执行计划创建索引。
71 0
【PostgreSQL】PostgreSQL扩展:pg_stat_statements 优化SQL
【PostgreSQL】PostgreSQL扩展:pg_stat_statements 优化SQL
57 0
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(上)
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(上)
86 0
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(下)
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB MySQL版解析与实践(下)——三、SQL优化与慢查询解决(下)
93 0
【JavaP6大纲】MySQL篇:如何优化SQL?
【JavaP6大纲】MySQL篇:如何优化SQL?
71 0
大数据开发基础的数据库基础的SQL语句优化
在处理大量数据时,SQL语句优化是非常重要的。大量的数据会导致查询和操作的效率降低,而SQL语句优化可以提高数据库的性能,从而实现更快速、更高效的数据处理。以下是一些SQL语句优化的技巧。
68 0
码农必备SQL高性能优化指南!35+条优化建议立马get
由于工作需要,最近做了很多 BI 取数的工作,需要用到一些比较高级的 SQL 技巧,总结了一下工作中用到的一些比较骚的进阶技巧,特此记录一下,以方便自己查阅,主要目录如下:
192 0
mysql千万级分页查询SQL优化
mysql千万级分页查询SQL优化
118 0
+关注
技术小能手
云栖运营小编~
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
时序数据库TSDB的SQL与流计算
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关实验场景
更多