[转帖]Oracle SQL调优系列之cursor学习笔记

https://cloud.tencent.com/developer/article/1995387

文章目录

- [一、oracle库缓存](https://cloud.tencent.com/developer)
    - [1.1、库缓存简介](https://cloud.tencent.com/developer)
    - [1.2、相关概念](https://cloud.tencent.com/developer)
    - [1.3、库缓存结构](https://cloud.tencent.com/developer)
    - [1.4、sql执行过程简介](https://cloud.tencent.com/developer)
- [二、oracle cursor](https://cloud.tencent.com/developer)
    - [2.1、cursor分类](https://cloud.tencent.com/developer)
    - [2.2、shared cursor](https://cloud.tencent.com/developer)
    - [2.3、session cursor](https://cloud.tencent.com/developer)
    - [2.4、sql执行过程](https://cloud.tencent.com/developer)

最近在看《基于oracle的sql优化》一书，并做了笔记

一、oracle库缓存

1.1、库缓存简介

介绍oracle cursor(游标)之前先，介绍一下oracle的库缓存，Oracle库缓存(Library Cache)是SGA中的一块内存区域，它的主要作用是缓存刚刚执行过的SQL语句或者PL/SQL语句(比如存储过程、函数、触发器、包)所对应的执行计划、解析树、Pcode，Mcode等对象，SGA相关知识可以参考我之前笔记：https://cloud.tencent.com/developer/article/1426640

1.2、相关概念

• 库缓存对象：缓存在库缓存中的对象称之为库缓存对象(Library Cache Object)，库缓存对象都是以库缓存对象句柄(Library Cache Object Handle)的结构存储在库缓存中的
• 库缓存对象句柄：所谓的库缓存对象句柄其实就是一种C语言定义的复杂结构，库缓存对象句柄以哈希表(Hash table)的方式存储在库缓存中

1.3、库缓存结构

Oracle库缓存的结构，图来自《基于Oracle的SQL优化》一书：

库缓存是由一组Hash Buckets组成的，Hash Buckets里面存储的hash值相同的库缓存对象句柄，而Hash Buckets中不同的库缓存对象句柄是以指针的方式连接起来的，从而组成一条库缓存对象句柄链表

1.4、sql执行过程简介

所以执行sql的过程，会将sql的文本进行hash运算，得到对象的hash值，然后拿hash值，去Hash Buckets里遍历缓存对象句柄链表，找到对应的缓存对象句柄，然后就可以得到缓存对象句柄里对应sql执行计划、解析树等对象，所以执行相同的sql第二次执行时是会比较快的，因为不需要解析获取执行计划，解析树等对象，如果找不到库缓存对象句柄，就需要重新解析，这个过程解析过多，容易造成硬解析问题

硬解析：是指Oracle在执行目标SQL时，在库缓存中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标SQL并生成相应的Parent Cursor和Child Cursor的过程。 软解析：是指Oracle在执行目标SQL时，在Library Cache中找到了匹配的Parent Cursor和Child Cursor，并将存储在Child Cursor中的解析树和执行计划直接拿过来重用，无须从头开始解析的过程。

二、oracle cursor

2.1、cursor分类

oracle的cursor其实就是库缓存对象，Oracle中的cursor分为两种，一种是shared cursor,另外一种是session cursor

2.2、shared cursor

shared cursor存储目标SQL的sql文本、解析树、该sql使用的绑定变量类型和长度、以及该SQL的执行计划等信息

oracle中的shared cursor又细分为parent cursor和child cursor，我们可以通过分别查询视图vSQLAREA和v SQL来获取存储在parent cursor和child cursor中的缓存信息

其实parent cursor和child cursor结构是类似的，解析sql时候，sql文本是存储在parent cursor的缓存对象句柄的属性name中，而执行计划和解析树是存储在child cursor的属性heap6中的，然后parent cursor和child cursor是怎么关联的？在parent cursor的属性heap0中存储的是child cursor缓存对象句柄的地址，然后就可以知道属于这个parent cursor的所有child cursor

2.3、session cursor

session cursor是用于在当前session中解析和执行SQL，session cursor也是C语言定义的一种结构，也是以哈希表的结构存储的，不过是存储在PGA中的

• session是和session cursor对应的，不同session的session cursor是无法共享的
• session cursor是有生命周期的，每个session cursor在使用过程都至少会经历一次Bind、Execute、Fetch和Close中的一个或多个过程

oracle第一次解析和执行sql时，会新生成一个session cursor和一对shared cursor(parent cursor和child cursor)，而其中的shared cursor会存储能被所有session共享、重用的内容(比如执行计划，解析树等)，而session cursor会经历一次open、parse、bind、execute、fetch和close的一个或多次阶段

2.4、sql执行过程

综上所述，oracle执行sql过程都会先去session cursor里面找，能找到就能通过关联找到parent cursor，假如找不到，就要重新生成session cursor和一对shared cursor(parent cursor和child cursor)，假如child cursor找不到，也会重新生成session cursor和child cursor