Oracle 学习之性能优化（二）游标

  游标是cursor的中文翻译，那么到底什么是cursor呢？Oracle Concept中是这样描述的：

  When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates acursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA.

如果上述很多的专业术语把你搞晕了，那你可以简单的理解成，一个sql语句就会对应到一个游标。

游标分类：

• session cursor 其实就是指跟这个session相对应的server process的PGA里（准确的说是UGA）的一块内存区域（或者说内存结构），它的目的是为了处理且一次只处理一条sql语句。

• shared cursor 缓存在librarycache（SGA下的Shared Pool）里的一种library cache object，说白了就是指缓存在library cache里的sql和匿名pl/sql。

如下图描述

还记得我们在http://lqding.blog.51cto.com/9123978/1685341这篇文章中描述的硬解析、软解析吗？

硬解析，上图中的第4种情况，需要重新构造一个游标。

软解析，上图中的第3种情况，可以在共享池中查询到可以被重用的游标信息。

软软解析：如上图，不单Shared pool中有cursor的详细信息，UGA中也会记录cursor的状态。当一个sql   发出后，如果能在uga中找到已经打开的相同游标，那么直接共用该游标。无需再进行Shared pool检   查。如果uga中的cursor已经关闭，那么直接打开游标即可。也可以直接共用游标。这两种情况都无   需进行Shared pool的检查，这种解析称之为软软解析。

Session Cursor

  使用v$open_cursor视图查询，一个会话最多可以打开的游标数由参数OPEN_CURSORS定义。

  session cursor又分为三种：分别是implicit cursor，explicit cursor和ref cursor。 

共享游标分类：

• 父游标

  文本相同的子游标的代表。所有文本相同的SQL都共享父游标。

  父游标没有执行计划，只有一些管理性的信息，包含了SQL TEXT和相关的hash value等。

• 子游标

  SQL文本相同，但是因执行环境等不同，会生成多个执行计划。

  包含了SQL的metadata，即使得这个SQL可以执行的所有相关信息，如OBJECT和权限，优化器设置，执行计划等。v$sql中的每一行表示了一个child cursor，根据hash value和address与parent cursor 关联。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。

案例：

1. 创建一个用户

SQL> conn / as sysdba
Connected.
SQL> create user ding identified by ding;

User created.

SQL> grant resource,connect to ding;

Grant succeeded.

2. 生成数据

SQL> create table ding.emp as select * from scott.emp;

Table created.

将数据库重启

3. 分别登录scott和ding用户，执行如下查询

SQL> select * from emp;

4. 查看父游标

SQL> COL SQL_TEXT FOR A30
SQL> COL SQL_ID FOR A20
SQL> SET LINESIZE 200
SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE '%emp';
SQL_TEXT		       SQL_ID		    EXECUTIONS	    LOADS VERSION_COUNT
------------------------------ -------------------- ---------- ---------- -------------
select * from emp	       a2dk8bdn0ujx7		     2		2	      2

两次查询共享了一个父游标，该父游标被load 2次，表示被硬解析了两次。VERSION_COUNT表示子游标的个数。

5. 查看子游标

SQL>  SELECT sql_id,sql_text,loads,child_number,parse_calls
  FROM v$sql
 WHERE sql_text LIKE '%emp';

SQL_ID		     SQL_TEXT				 LOADS CHILD_NUMBER PARSE_CALLS
-------------------- ------------------------------ ---------- ------------ -----------
a2dk8bdn0ujx7	     select * from emp			     1		  0	      1
a2dk8bdn0ujx7	     select * from emp			     1		  1	      1

6. scott用户下的语句再执行一遍，再次查看父子游标

SQL> SELECT sql_text,
       sql_id,
       EXECUTIONS,
       LOADS,
       VERSION_COUNT
  FROM v$sqlarea
 WHERE sql_text LIKE '%emp';  2    3    4    5    6    7  

SQL_TEXT		       SQL_ID		    EXECUTIONS	    LOADS VERSION_COUNT
------------------------------ -------------------- ---------- ---------- -------------
select * from emp	       a2dk8bdn0ujx7		     3		2	      2

EXECUTIONS变成了3. 

SQL> SELECT sql_id,
       sql_text,
       loads,
       child_number,
       parse_calls,
       parsing_schema_name
  FROM v$sql
 WHERE sql_text LIKE '%emp'  2    3    4    5    6    7    8  ;

SQL_ID		     SQL_TEXT				 LOADS CHILD_NUMBER PARSE_CALLS PARSING_SC
-------------------- ------------------------------ ---------- ------------ ----------- ----------
a2dk8bdn0ujx7	     select * from emp			     1		  0	      1 DING
a2dk8bdn0ujx7	     select * from emp			     1		  1	      2 SCOTT

只有文本完全相同，才能共享父游标。哪怕是语句的语义、环境等有完全相同，sql文本稍微不同都不行。例如如下sql

select * from emp;
select  * from emp;
select * from Emp;

那么当父游标相同，有多个子游标时，我们如何知道是何原因导致不共享子游标呢？

SELECT *
  FROM v$sql_shared_cursor
 WHERE sql_id = 'a2dk8bdn0ujx7'

  这个表中会有什么*MISMATCH的字段，如果该值为Y，就表示是因为这个字段指示的内容不一致导致不能共享子游标。

游标的生命周期：

（2）解析游标(dbms_sql.parse)

（4）绑定输入变量（dbms_sql.bind_variable/bind_array)

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>