Library Cache优化与SQL游标

冷菠

冷菠,网名悠然(个人主页http://www.orasky.net),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队管理以及培训经验。曾担任美资企业Senior DBA职务、支付公司数据库团队负责人,现为培训机构重庆优唯佳科技公司技术合伙人。

擅长数据库备份恢复、数据库性能诊断优化以及数据库自动化运维等,对主机存储、网络、系统业务架构设计优化、大数据等领域有较为深入的研究。目前致力于大数据、智能一体化、开源云计算等领域的佳实践探索。

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

1

SQL语句与父游标及子游标

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

SQL语句与游标关系如下:

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

图2-2 SQL语句与游标

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

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

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

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

小提示

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

2

父游标

1父游标特点

父游标的主要特点如下:

q 父游标是由SQL语句决定;

q 父游标使用SQL语句的SQL_ID唯一标识;

q 父游标包含一到多个子游标;

q 父游标与参数cursor_sharing紧密相关。

2父游标组成结构

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

表2-2 父游标组成结构

组成结构单元

功能描述

KGLHD

KGL Handle 结构体

KGLOB

KGL Object 结构体,通过x$kglob查询

KGLNA

KGL Name结构体,通过x$kglna查询

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

图2-3 父游标组成结构

3父游标相关查询

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

V$SQLAREA主要特点有:

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

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

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

4父游标相关参数

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

cursor_sharing的3种模式:

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

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

  • cursor_sharing= EXACT

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

  • cursor_sharing = FORCE

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

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

小提示

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

  • cursor_sharing = SIMILAR

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

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

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

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

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

  • 示例2:父子游标。

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

图2-4 父子游标与cursor_sharing

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

3

子游标

1子游标特点

子游标的主要特点有:

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

2子游标组成结构

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

表2-3 子游标组成结构

组成结构单元

功能描述

KGLHD

KGL Handle 结构体

KGLOB

KGL Object 结构体,通过x$kglob查询

KGLNA

KGL Name结构体,通过x$kglna查询

Environment

环境信息

Statistics

统计信息

Execution Plan

执行计划

Bind Variable

绑定变量

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

图2-5 子游标组成结构

3子体游标相关查询

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

V$SQL主要特点有:

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

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

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

4子游标相关参数

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

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

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

图2-6 父子游标与optimizer_mode

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-08-30

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏潘昌伟的专栏

用好 mysql 分区表

大数据时代,数据量趋于海量,mysql单表很难满足大数据场景的一些统计需求,用好分区表可以很好的解决很大一部分的问题。

8260
来自专栏杨建荣的学习笔记

执行计划变化导致CPU负载高的问题分析 (r8笔记第20天)

前几天碰到一个CPU负载较高的问题。从系统层面来看,情况不是很严重,但是从应用的角度来说,已经感觉到很慢了。因为前端的调用频率还是比较高。所以会把这个问题放大。...

2497
来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

803
来自专栏杨建荣的学习笔记

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

2926
来自专栏杨建荣的学习笔记

Oracle中的段(r10笔记第81天)

Oracle的体系结构中,关于存储结构大家应该都很熟悉了。 估计下面这张图大家都看得熟悉的不能再熟悉了。 ? 简单来说,里面的一个重要概念就是段,如果是开发...

3388
来自专栏帘卷西风的专栏

创建角色随机名字(mysql抽取随机记录)和mysql游标的使用

1、现在创建游戏角色的时候,基本上都是支持角色名字随机的,以前此功能在客户端用代码实现,然后向服务器请求并验证,后来发现有时候连续几次都失败,所以改成在服务器...

542
来自专栏邵梦超的专栏

Mysql 并发引起的死锁问题

平台的某个数据库上面有近千个连接,每个连接对应一个爬虫,爬虫将爬来的数据放到cdb里供后期分析查询使用。前段时间经常出现cdb查询缓慢,cpu占有率高的现象。难...

1.1K0
来自专栏杨建荣的学习笔记

完美的执行计划导致的性能问题(r4笔记第17天)

今天现场的开发同事反馈有一个job处理数据的速度很慢,从半夜2点开始运行,结果到了早上8点还没有运行完,最后无奈kill掉了进程。等我刚到公司,他们想让我查查倒...

2847
来自专栏杨建荣的学习笔记

一条SQL语句的执行计划变化探究(r10笔记第3天)

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落...

3156
来自专栏程序猿

MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些? 本文记录MySQL优化方案 ,梗概如下: 优化SQL 优化索引 (一)优化SQL 1、通过MySQL自有的优化语句 优化SQL语...

3807

扫描关注云+社区