Re: Can you explain the indication of "command_type" and "open_versions" column in the v$sqlarea

"ΠάμΗ" <xiongye_at_runway.cn.net> wrote in message news:<bk903k$2q4v$1_at_mail.cn99.com>...
> What is the meaning of "command_type" and "open_versions" column in the
> v$sqlarea ?
> what does it meanings where command_type=2 and open_versions >0 Thanks !

Hi, xiongye,

The reference manual for v$sqlarea may not be that straightforward. But you can find the command type in the notes for v$session ("Table 3-3 COMMAND Column of V$SESSION and Corresponding Commands" in my 9i documentation).

Open versions count in v$sqlarea is the number of open child cursors. To understand child vs. parent cursors, read some of Steve Adams' articles at www.ixora.com.au, or James Morle's Scaling Oracle8i. Here's my understanding. When the cursor is in library cache, even just the parent or cursor head, it'll show up in v$sql and v$sqlarea (version_count in the latter is 1 or greater). When the cursor's context or cursor tail is also loaded (not just the parent or head), loaded_versions in both views is incremented. Further, if the cursor is open (not just head and tail present in the library cache), open_versions is incremented. Finally, when it's being executed, users_executing is non-zero.

Note that loaded_versions and open_versions in v$sql are just 1 and 0; would be better if called loaded and open for the boolean status. In v$sqlarea they're actual counts.

Yong Huang Received on Wed Sep 17 2003 - 08:35:28 CDT