查看游标相关的信息

-- 查看命中率
select (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ratio"
  from v$rowcache;
 
 
  -- 查看硬解析情况
  select name,value from v$sysstat where name like 'parse%';
 
  -- 各会话打开的游标数量
  select sid,count(*) from v$open_cursor group by sid;
 
    select * from v$open_cursor;

       
        -- 没有关闭游标的语句
        select * from X$KGLLK where KGLLKFLG=8;
       
       
           
        select count(1) from v$open_cursor;
       
        select KGLLKFLG,KGLNAOBJ from X$KGLLK where user_name=???? and KGLLKFLG=8;
       
        select KGLLKFLG,KGLNAOBJ from X$KGLLK;
       
        select * from X$KGLLK;
       
-- 游标相关的参数       
show parameter session_cached_cursors;   

OPEN_CURSORS

Property Description

Parameter type Integer

Default value 50

Modifiable ALTER SYSTEM

Range of values 0 to 65535

Basic Yes

OPEN_CURSORS specifies the maximum number of open cursors (handles to private 

SQL areas) a session can have at once. You can use this parameter to prevent a session 

from opening an excessive number of cursors.

It is important to set the value of OPEN_CURSORS high enough to prevent your 

application from running out of open cursors. The number will vary from one 

application to another. Assuming that a session does not open the number of cursors 

specified by OPEN_CURSORS, there is no added overhead to setting this value higher 

than actually needed.

SESSION_CACHED_CURSORS

Property Description

Parameter type Integer

Default value 50

Modifiable ALTER SESSION

Range of values 0 to operating system-dependent

Oracle RAC Multiple instances can have different values.

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. 

Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement 

cause the session cursor for that statement to be moved into the session cursor cache. 

Subsequent parse calls will find the cursor in the cache and do not need to reopen the 

cursor. Oracle uses a least recently used algorithm to remove entries in the session 

cursor cache to make room for new entries when needed.

以下两个参数大部分情况使用默认值。

CURSOR_SHARING

Property Description

Parameter type String

Syntax CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

Default value EXACT

Modifiable ALTER SESSION, ALTER SYSTEM

Basic No

CURSOR_SHARING determines what kind of SQL statements can share the same 

cursors.

Values :

■ FORCE

Allows the creation of a new cursor if sharing an existing cursor, or if the cursor 

plan is not optimal.

■ SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to 

share a cursor, unless the literals affect either the meaning of the statement or the 

degree to which the plan is optimized.

■ EXACT

Only allows statements with identical text to share the same cursor

CURSOR_SPACE_FOR_TIME

Property Description

Parameter type Boolean

Default value false

Modifiable No

Range of values true | false

Basic No

Note: The CURSOR_SPACE_FOR_TIME parameter is deprecated. It is 

retained for backward compatibility only.

CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time. 

It affects both the shared SQL area and the client's private SQL area.

Most users will not need to set this parameter because of the significantly enhanced 

concurrency modifications introduced in Oracle Database 10g Release 2 (10.2.0.2) and 

later.

Values:

■ true

Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas 

are not aged out of the pool as long as an open cursor references them. Because 

each active cursor's SQL area is present in memory, execution is faster. However, 

the shared SQL areas never leave memory while they are in use. Therefore, you 

should set this parameter to true only when the shared pool is large enough to 

hold all open cursors simultaneously.

In addition, a setting of true retains the private SQL area allocated for each cursor 

between executions instead of discarding it after cursor execution, saving cursor 

allocation and initialization time.

■ false

Shared SQL areas can be deallocated from the library cache to make room for new 

SQL statements.

Note: If this parameter is set to true, then the SERIAL_REUSE 

parameter is disabled.