Cursor直译过来就是“游标”,它是Oracle数据库中SQL解析和执行的载体。Oracle数据库使用C语言写的,所以从本质上来说,可以将Cursor理解成C语言中的一种结构。
Oracle数据库中Cursor分为两种类型:一种是Shared Cursor;另一种是Sesssion Cursor
一、shared cursor
Oracle数据库中的Shared Cursor就是缓存在库缓存里的一种库缓存对象,说白了就是指缓存在库缓存里的SQL语句和匿名PL/SQL语句所对应的库缓存对象。Shared Cursor是Oracle缓存在Library Cache中的几十种库缓存对象之一,它所对应的库缓存对象句柄的Namespace属性的值是CRSR(Cursor的缩写)。
Oracle数据库的Shared Cursor又细分为Parent Cursor和Child Cursor这两种类型,我们可以通过分别查询视图 v$sqlarea 和 v$sql 来查看当前缓存在库缓存中Parent Curosr和Child Curosr,其中 v$sqlarea 用于查看Parent Cursor, v$sql 用于查看Child Cursor.
通过如下sql语句查找某个sql有多少个子游标:
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select * from pipi %';
SQL_TEXT SQL_ID VERSION_COUNT
---------------------------------------- ------------- -------------
select * from pipi where id=1 66fjb4p012mgu 1
其中version_count代表了子游标的个数
查找该sql_id对应的子游标:select plan_hash_value, child_number from v$sql where sql_id = '66fjb4p012mgu';
SQL> select plan_hash_value, child_number from v$sql where sql_id = '66fjb4p012mgu';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
3677126908 0
其中child_number为子游标编号,第一个子游标编号为0,第二个为1以此类推。
二、session cursor
Oracle数据库里第二种类型的Cursor就是Session Cursor,它是当前session解析和执行SQL的载体,换句话说,Session Cursor用于在当前Session中解析和执行SQL。和Shared Cursor一样,Session Cursor也是Oracle自定义的一种C语言复杂结构,他也是以哈希表的方式缓存起来的,只不过是缓存在PGA中
session cursor的相关参数解析
参数open_cursor用于设定单个session中同时能够以open状态并存的session cursor的总数
SQL> show parameter open_cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 2000
从上述显示的结果可以看出,open_cursors的值为2000,意味着在这个库里,单个session中同时能够以open状态并存的session cursor的总数不能超过2000,负责oracle会报错ORA-1000:maximum open cursors exceeded。
视图v$open_cursor可以用来查询数据库中状态为Open后者已经被缓存在PGA中的session cursor的数量和具体信息(比如session cursor所对应的SQL ID和SQL文本等)
select saddr,sid,user_name,sql_id,sql_text from v$open_cursor;
参数session_cached_cursors用于设定单个session中能够以soft closed状态并存的session cursor的总数,即用于设定单个session能够缓存在PGA中的session cursor的总数
SQL> show parameter session_cached_cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 200
oracle会用LRU算法来管理这些已缓存的session cursor,所以即便某个session以soft closed状态缓存在PGA中的session cursor的总数已经达到了session_cached_cursors所设置的上限也没有关系,LRU算法依然能够保证那些频繁反复执行的SQL所对应的session cursor的缓存命中率要高于那些不频繁反复执行的SQL。
一个session cursor能够被缓存在PGA中的必要条件是该session cursor所对应的SQL解析和执行的次数要超过3次。Oracle这么做的目的是为了避免将执行次数很少的SQL所对应的session cursor也被缓存在PGA里,这些SQL很可能只执行一次而且不会重复执行,所以把这些执行次数很少的SQL所对应的session cursor缓存在PGA中是没有太大意义的
实验验证:
SQL> select sid from v$mystat where rownum = 1;
SID
----------
1283
第一次执行语句:select * from pipi where id=1;
SQL> select * from pipi where id=1;
ID
----------
1
SQL> select sql_text, cursor_type from v$open_cursor where user_name = 'SYS' and sid = 1283 and sql_text like 'select * from pipi%';
SQL_TEXT CURSOR_TYP
---------------------------------------- ----------
select * from pipi where id=1 OPEN
第二次执行语句:select * from pipi where id=1;
SQL> select * from pipi where id=1;
ID
----------
1
SQL> select sql_text, cursor_type from v$open_cursor where user_name = 'SYS' and sid = 1283 and sql_text like 'select * from pipi%';
SQL_TEXT CURSOR_TYP
---------------------------------------- ----------
select * from pipi where id=1 OPEN
第三次执行语句:select * from pipi where id=1;
SQL> select * from pipi where id=1;
ID
----------
1
SQL> select sql_text, cursor_type from v$open_cursor where user_name = 'SYS' and sid = 1283 and sql_text like 'select * from pipi%';
SQL_TEXT CURSOR_TYP
---------------------------------------- ----------
select * from pipi where id=1 OPEN
第四次执行语句:select * from pipi where id=1;
SQL> select * from pipi where id=1;
ID
----------
1
SQL> select sql_text, cursor_type from v$open_cursor where user_name = 'SYS' and sid = 1283 and sql_text like 'select * from pipi%';
SQL_TEXT CURSOR_TYPE
----------------------------- --------------------------------
select * from pipi where id=1 SESSION CURSOR CACHED
可以发现第四次执行某个sql语句时候,该session cursor已经被数据库缓存至pga中成为SESSION CURSOR CACHED
三、两种 cursor的区别与联系
3.1. 两种 cursor的区别
- 缓存的位置不同,shared cursor在sga中,session cursor在pga中。
- session cursor和session是一一对应的,不同session和session cursor之间没法共享;shared cursor是可以共享的。
3.2. 两种 cursor的联系
- 可以通过session cursor找到shared cursor,在缓存session cursor的哈希表的对应Hash Bucket中,Oracle会存储目标SQL对应的Parent Cursor的库缓存对象句柄地址
- 两者的存储方式是一样的,都是通过hash bucket存储的
四、硬解析与软解析/软软解析
oracle在解析和执行目标SQL时,会先去当前session的PGA中找是否存在匹配的缓存session cursor。当Oracle第一次解析和执行目标SQL时(显然是硬解析),当前session的PGA中肯定不存在匹配的session cursor,这时Oracle会新生成一个session cursor和一对shared cursor(即Parent Cursor和Child Cursor),这其中的Shared Cursor会存储能被所有session共享、重用的内容(比如目标SQL的解析树、执行计划等)
无论是硬解析、软解析还是软软解析,oracle在解析和执行目标SQL时,始终会先去当前session的PGA中寻找是否存在匹配的缓存session cursor。
如果在当前session的PGA中找不到匹配的缓存session cursor,oracle就会去库缓存中找是否存在匹配的Parent cursor。如果找不到,Oracle就会新生成一个session cursor和一对shared cursor(即parent cursor和child cursor);如果找到了匹配的parent cursor,但找不到匹配的child cursor,Oracle就会新生成一个session cursor和一个child cursor(这个child cursor会被挂在之前找到的匹配parent cursor下)。无论哪一种情况,这两个过程对应的都是硬解析
- 如果在当前session的PGA中找不到匹配的缓存session cursor,但在库缓存中找到了匹配的parent cursor和child cursor,则oracle会新生成一个session cursor并重用刚刚找到的匹配parent cursor和child cursor,这个过程就是软解析
- 如果在当前session的PGA中找到了匹配的缓存session cursor,此时就不在需要新生成一个session cursor,并且也不再需要向软解析那样得去库缓存中查找匹配的parent cursor了,因为oracle此时可以重用找到的匹配session cursor,并且可以通过这个session cursor至二级访问到该SQL对应的parent cursor,这个过程就是软软解析
下图是对Oracle执行sql语句的流程,很好对应了上文所讲的: