cursor工作原理一点点说明

原文地址：http://blog.itpub.net/742571/viewspace-874801/

有网友说：

db instance: share modle.
open cursor时, 想问一下:
1.如果没设large pool, cursor用的是UGA, 如果UGA用完了接着用哪个空间?还是会报错？
2. 如果设定了large pool, cursor用的是large pool, large pool也用完了，接着oracle会怎么处理？

为了这个问题，我查了好几天的资料，以下描述，仅查询oracle 9i资料。

先从cursor的定义来看：
Cursors

A cursor is a handle or name for a private SQL area--an area in memory in which a parsed statement and other information for processing the statement are kept.

Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.

Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initialization parameter.

Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can run several recursive calls. These recursive cursors also use shared SQL areas.

-----引自《Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01》的第14章SQL, PL/SQL, and Java。
总的来说，定义的cursor的意思大体是：
A cursor is a handle or name for a private SQL area.In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors.

这么说来，cursor是一条SQL语句的句柄，或private SQL area的名字，或对应用程序来说是一个命名资源。
那么，说起cursor，就应该从一条SQL语句的执行过程说起和private SQL area是怎么分配，在那里分配的了?

cursor分显式和隐式，比如我们在pl/sql里自己定义的游标，然后用明确的open语句打开了，那么就是显式的；如果用for x in cur_data的方法打开游标，那么就是隐式的了。不管是那一种，他都是一个游标。

关于SQL语句的执行过程，可以参考：《Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01》的第14章“SQL, PL/SQL, and Java”的“SQL Processing”这一节。
从这一节我们可以看到，SQL语句执行的第一步就是创建游标：
Stage 1: Create a Cursor
A program interface call creates a cursor. The cursor is created independent of any SQL statement: it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.

然后，就是把SQL语句交给Oracle来解析，此时如果在shared pool的library cache发现一个和该SQL语句一样的shared SQL area，那么，直接拷贝一份作为private SQL area来执行，此叫做发生一次soft parse；如果没有找到，那么就要解析该SQL，也就是要得到该SQL的parse tree和execution plan，分配内存，存储这些信息为一个shared SQL area，然后拷贝一份做为private SQL area然后再执行，此为发生一次hard parse。

对于private SQL area，参考《Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01》的第7章“Memory Architecture”，我们可以得到private SQL area的信息如下：
Private SQL Area

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.

The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:

* The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed.
* The run-time area, which is freed when the execution is terminated.

Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been run. For queries, Oracle frees the runtime area only after all rows are fetched or the query is canceled.

The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process's PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

Cursors and SQL Areas

The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas.

The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.

A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.

上面的E文描述了，在private SQL area创建过程中，会有两个内存区域，第一个是The persistent area，只有在cursor close才会释放，而另外一个The run-time area，update,insert,delete语句一执行结束，或SELECT语句的所有行返回后，就会释放。

那么，private SQL area在那里分配的呢？shared SQL area 是在shared pool的library cache里分配的，而private SQL area，如果是shared server模式，那么是在shared pool的library cache里分配，如果是dedicated server，那么是在PGA里分配。那么这里的PGA说的是什么呢？
这里的PGA，说的是通过参数PGA_AGGREGATE_TARGET参数指定的PGA大小分配的区域。即这部分内容，已经不在SGA里分配了。

此时，如果是SQL语句里包含了sort,hash-join,bitmap create,bitmap merge，那么，还会在单独的分配SQL work area来处理“sort,hash-join,bitmap create,bitmap merge”操作，这里的SQL work area，如果WORKARE_SIZE_POLICY设置为auto，那么也是在GA_AGGREGATE_TARGET的范围里分配的，如果设置为 manual，那么是由*_AREA_SIZE（例如sort_area_size,hash_area_size, create_bitmap_area_size）来分别定义大小的（如果分配的大小不够，此时temp表空间就排上用场了），分配的范围还是属于PGA 的。

到此，关于SQL语句的执行，主要是关于cursor的定义，我觉得应该是相对比较清楚了。
但是，想深一层，cursor的取值过程，比如1G的SGA，1G的PGA，取10G的大表全表数据，如果在pl/sql里打开游标处理，那么这些数据的处理过程是怎么处理的，还是没有描述出来，这里只表述了关于CURSOR这个SQL语句的分析处理过程。

这里，cursor有关的两个参数，open_cursor定义一个session最大能打开的cursor数，指的是打开后不关闭的情况，也就是可以创 建多少个private SQL area，CURSOR_SHARING指的是符合那些标准的SQL语句可以认为是一类，是同一个SQL语句来被shared，即创建的shared SQL area可以被后续的那些SQL语句共享执行。SESSION_CACHED_CURSORS说的是一个session可以缓存多少个cursor，让后 续相同的SQL语句不再OPEN。


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
最后，针对楼主的问题：
db instance: share modle.
open cursor时, 想问一下:
1.如果没设large pool, cursor用的是UGA, 如果UGA用完了接着用哪个空间?还是会报错？
2. 如果设定了large pool, cursor用的是large pool, large pool也用完了，接着oracle会怎么处理？

cursor用完了，1是cursor个数用完了，受到open_cursors限制，应该会报错。2是cursor消耗内存消耗完了，这里cursor 对应的private SQL area的PGA大小，应该是受到PGA_AGGREGATE_TARGET限制，如果超过了，那么就错了，而且，private SQL area只是存储parsing tree和execute plan等信息，size是很小的，默认的open_cursors=50，在加上process参数指定的会话数，应该是不会超过 PGA_AGGREGATE_TARGET，与此相关的shared SQL area，由于是在shared pool，如果SQL语句太多，那么是受LRU list管理的，如果全部有用，而又不能淘汰，应该是会报告错误的。

至于large pool，如果配置了，应该也是和cursor没关系的。
按照《Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01》的第7章“Memory Architecture”定义：
Large Pool

The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

* Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
* I/O server processes
* Oracle backup and restore operations
* Parallel execution message buffers, if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to true (otherwise, these buffers are allocated to the shared pool)

large pool工作的4大块内容，只有session memory貌似和cursor有点关系，可是参考下面对session memory的定义：
Session Memory

Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

session memory与cursor是没有关系的，那么，large pool，与cursor是没有关系的。

[@more@]＝＝＝＝＝＝＝＝＝＝＝＝
最后，关于cursor的内容，我自己有很多细节部分也没想清楚，也没找到相关资料。接着的分析过程，可能得从metalink上，google前辈们的 贡献，trace执行过程来跟踪到底是怎么回事了。同时，以上描述内容，由于个人E文不好，理解可能错误，各位要是发现错误的地方，请帮我纠正过来，谢 谢。