为什么opened cursors的数量不一样呢

Yong Huang

To answer ccsnmoracle's messages #7 and 8: All you said makes sense. This "就在client端所在server的内存中" is probably misleading; I would say "就在client端所在process的内存中". Yes, a private SQL area is in UGA (you can say PGA only because shared server config is not commonly used).

A good explanation is in documentation
http://docs.oracle.com/cd/E16655 ... emory.htm#CNCPT1239

and Jonathan Lewis explains different meanings of "cursor" on pp.162-3 in his "Oracle Core Essential Internals for DBAs and Developers".

ccsnmoracle

Yong Huang 发表于 2014-4-28 22:57
To answer ccsnmoracle's messages #7 and 8: All you said makes sense. This "就在client端所在server的内 ...

谢谢Yong Huang Sir的解答

<<Oracle Core Essential Internals for DBAs and Developers>>书中的定义我看了，我感觉说的大概也是一回事。（挺难读懂的）
如果从Oracle官方文档上看，cursor的定义也是很简单的。当然我觉得他这里指的是client process中的cursor。
》A cursor is a name or handle to a specific private SQL area.

而private SQL area呢？

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

Do not confuse a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.

private sql area结构中，有指向 sga中，libaray中cursor。这里的cursor就是具体的一个sql文的指向体。


the Oracle instance the term has two (or even three) more commonly used interpretations. In its first
meaning it is (transient) data—holding information about state, a subset of the results, etc.—stored in the
session’s process memory as the statement executes; in its second meaning it is the executable version of
the SQL statement, stored publicly in the library cache.
The potential third interpretation arises because a single statement can end up with many different execution plans (together with details of their working environment) that are grouped together under acommon place-holder in the library cache—as a consequence we tend to speak of a parent cursor (the
place-holder) and its child cursors (the individual execution plans).

简单的来说，1.cursor是在client端执行sql或plsql时必须要获得的一种资源。
                     2.这种资源是一种指针结构。这个指针结构比较复杂，它将pga和sga中的内容联系在一起，最终的目的就是执行sql文，并是结果返回的client端。

Yong Huang

Basically, "cursor" can be a concept on the client side, and on the server side. I wish Oracle used different terms from the beginning.

ccsnmoracle

Yong Huang 发表于 2014-4-29 23:51
Basically, "cursor" can be a concept on the client side, and on the server side. I wish Oracle used  ...

学习了！感谢Yong Huang sir!