<< 返回文章列表

Library Cache优化与SQL游标

2017年12月20日
冷菠
1864

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


1、SQL语句与父游标及子游标


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


SQL语句与游标关系如下:


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


    image.png


    图2-2  SQL语句与游标

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


    image.png



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


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

    image.png


    可以看出,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所示:

image.png


图2-3  父游标组成结构


3父游标相关查询


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


V$SQLAREA主要特点有:


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

    image.png

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


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

4父游标相关参数


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


cursor_sharing的3种模式:


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

    image.png

  • FORCE

  • SIMILAR

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


  • cursor_sharing= EXACT

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


image.png


  • cursor_sharing = FORCE

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

image.png


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

小提示

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


  • cursor_sharing = SIMILAR

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

  • 当SQL语句几乎完全相同时; 

  • 当执行计划相同或者执行计划更优时;

  • 当忽略SQL语句文字内容差异共享游标


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


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


image.png


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


  • 示例2:父子游标。


image.png


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


image.png


 图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所示:

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


3子体游标相关查询



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


V$SQL主要特点有:


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


image.png


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


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


4子游标相关参数


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


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


image.png


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


image.png


图2-6  父子游标与optimizer_mode