oracle 优化学习笔记_cursor

1.shared cursor

1.1 library cache

库缓存为SGA中，shared_pool中的一片区域，主要缓存刚刚执行过的sql语句和pl/sql语句所对应的执行计划、解析树、pcode、mcode等对象。
缓存在库缓存中的对象为库缓存对象，以库缓存对象句柄的结构存储。
句柄是以哈希表的方式存储在库缓存中。

1.2 parent cursor和child cursor

Oracle数据库中的Shared Cursor又细分为Parent Cursor（父游标）和Child Cursor（子游标）这两种类型，我们可以通过分别查询视图V$SQLAREA和V$SQL来查看当前缓存在库缓存中的Parent Cursor和Child Cursor，其中V$SQLAREA用于查看Parent Cursor，V$SQL用于查看Child Cursor。
在Oracle数据库里，任意一个目标SQL一定会同时对应两个Shared Cursor，其中一个是Parent Cursor，另外一个则是Child Cursor，Parent Cursor会存储该SQL 的SQL文本，而该SQL真正的可以被重用的解析树和执行计划则存储在Child Cursor中。

TEST@regan> select count(*) from tab_a;
TEST01@regan> select count(*) from tab_a;
--两个用户执行相同的sql语句。

SYS@regan> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select count(*) from tab_a%';
SQL_TEXT                                 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select count(*) from tab_a               f7db5h6h5404m             2
--这里可以查询到有一个parent cursor

SYS@regan> select plan_hash_value,child_number from v$sql where sql_id='f7db5h6h5404m';

PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     1005348827            0
     1005348827            1
--对应生成了2个child cursor     

1.3 匹配cursor顺序

1. 根据目标SQL文本的hash值去库缓存中找匹配的hash bucket
2. 在匹配的hash bucket的库缓存对象链表中查找匹配的parent cursor
3. 若步骤2找到了parent cursor，则遍历从属的child cursor
4. 若步骤2没找到parent cursor，则从头开始解析，并生成parent和child cursor，并挂到对应的hash bucket上
5. 若步骤3找到了child cursor，则可以把child cursor中的解析树和执行计划直接拿来用
6. 若步骤3没找到child cursor，则从头开始解析，新生成一个child cursor，并挂在对应的parent cursor下

2.session cursor

2.1 概念

session cursor 是当前session解析和执行sql的载体，缓存在PGA中。
注意：

1. session cursor与session是一一对应的，不同session之间不共享。
2. session cursor是有生命周期的。根据SESSION_CACHED_CURSORS的不同，不一定会被缓存。
3. session cursor也是以hash表的方式缓存，可以认为是根据目标sql的sql文本的hash值去PGA中的hash bucket中匹配找到session cursor。

一个session cursor只能对应一个shared cursor，而一个shared cursor可以同时对应多个session cursor。
当目标sql以硬解析的方式解析和执行完毕后，这个sql所对应的shared cursor就已经被缓存在库缓存中，它所对应的session cursor也已使用完毕。这个时候会存在以下两种情况：

1. SESSION_CACHED_CURSORS=0 那么session cursor就会close，下次再执行的时候，会是软解析并再次open一个session cursor
2. SESSION_CACHED_CURSORS>0 则被标记为soft closed，同时将其缓存在当前session的PGA中，下次再执行的时候则直接匹配到这个session cursor直接用

2.2 相关参数

参数	作用
open_cursor	设置单个session 中能够以open状态并存的 session cursor的总数
session_cached_cursors	设置单个session中能够以soft closed状态并存的session cursor的总数。11gR2种，一个session cursor能被缓存在pga中的条件是该sql解析和执行的次数要超过3次。
cursor_space_for_time	11107后已过时。10g中用于减少库缓存相关的latch争用，但同时对shared pool的空间增加压力。

SYS@regan> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_cursor_bind_capture_area_size       integer     400
cursor_bind_capture_destination      string      memory+disk
cursor_invalidation                  string      IMMEDIATE
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

SYS@regan> select sid from v$mystat where rownum=1;

       SID
----------
        51

SYS@regan> select count(*) from v$open_cursor where  sid=51;

  COUNT(*)
----------
        22

2.3 session cursor的种类

1. 隐式游标
2. 显示游标
3. 参考游标

3.硬解析、软解析与软软解析

session cursor和shared cursor的关联关系，以及各种解析之间的关系：

• 无论是硬解析，软解析，还是软软解析。oracle在解析和执行sql时，始终会去当前session的pga中寻找是否存在匹配的session cursor。
• 若pga中不存在，则会去库缓存中找是否存在匹配的parent cursor。如果找不到，oracle就会新生成一个session cursor和一对session cursor；如果找到了parent cursor，但是找不到匹配的child cursor，oracle就会新生成一个session cursor和一个child cursor。这两种过程都是硬解析
• 若pga中不存在，但是在库缓存中找到了匹配的parent cursor和child cursor，则会生成一个session cursor并重用刚才匹配到的parent和child cursor。这个过程就是软解析
• 若在pga中找到了匹配的session cursor，则可以通过这个session cursor直接访问到该sql对应的parent cursor并重用。这个过程就是软软解析