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

发布时间：2020-07-21 18:24:12 作者：lqding1980
来源：网络阅读：6252

游标是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。

如下图描述

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

还记得我们在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等。
v$sqlarea中的每一行代表了一个parent cursor， address字段表示其内存地址。
子游标
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，就表示是因为这个字段指示的内容不一致导致不能共享子游标。

游标的生命周期：

（1）打开游标（dbms_sql.open_cursor)

Open cursor: A memory structure for the cursor is allocated in the server-side private memory of the server process associated with the session, the user global area (UGA). Note that no SQL statement isassociated with the cursor yet.

系统会在UGA中分配相关的内存结构,就是获得游标句柄的过程，这时的游标还未和sql语句有关联；

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

Parse cursor: A SQL statement is associated with the cursor. Its parsed representation that includes the execution plan (which describes how the SQL engine will execute the SQL statement) is loaded in the shared pool, specifically, in the library cache. The structure in the UGA is updated to store a pointer to the location of the shareable cursor in the library cache. The next section will describe parsing in more detail.

有一条sql与游标相关联，并将执行解析过后的执行计划放在library cache（SGA的shared pool下）中，UGA中生成指向这个共享游标的指针；即session cursor 指向shared cursor。一个session cursor 只能指向一个shared cursor，而一个shared cursor 可以指向多个session cursor。
（3）定义输出变量（dbms_sql.define_column)

Define output variables: If the SQL statement returns data, the variables receiving it must be defined. This is necessary not only for queries but also for DELETE, INSERT, and UPDATE statements that use the RETURNING clause.

如果sql语句返回数据，必须定义接收数据的变量，对delete，update，insert来说是returning；

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

Bind input variables: If the SQL statement uses bind variables, their values must be provided. No check is performed during the binding. If invalid data is passed, a runtime error will be raised during the execution.

绑定过程是不做检查的；
（5）执行游标(dbms_sql.execute)

Execute cursor: The SQL statement is executed. But becareful, because the database engine doesn’t always do anything significant during this phase. In fact, for many types of queries, the real processing isusually delayed to the fetch phase.

这步数据库引擎其实不做什么重要事情，而对大多数sql语句来说，真正处理过程是到fetch获取数据阶段；
（6）获取游标(dbms_sql.fetch_rows)

Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries, this step is where most of theprocessing is performed. In the case of queries, rows might be partiallyfetched. In other words, the cursor might be closed before fetching all therows.

真正的处理过程，有返回数据的话，必须提供输出变量(dbms_sql.column_value)；
（7）关闭游标(dbms_sql.close_cursor)

Close cursor: The resources associated with the cursorin the UGA are freed and consequently made available for other cursors. The shareable cursor in the library cache is not removed. It remains there in the hope of being reused in the future.

释放UGA中相关资源，库缓存中共享游标不会被清除。

当游标被关闭后，还可以继续缓存在内存中，参数SESSION_CACHED_CURSORS定义当前Session已经关闭并被缓存的游标的最大数量,即单个session中同时能cache住的soft closed session cursor的最大数量。

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

相关阅读