Every query comes into oracle, oracle would assign the cursor(s) in PGA’s private SQL area. OPEN_CURSORS parameter define how many cursors can open by a user sessions.
More cursors open, used more memory in PGA.
SESSION_CACHED_CURSORS
Number of cursors a session can store. 11.2 default value is 50. This is to avoid the soft parse.
Show cursors number by applications and users
SELECT
s.sid,
s.username,
s.osuser,
S.program,
S.SERIAL#,
S.LOGON_TIME,
S.STATUS,
B.class,
b.name,
v.value
from
V$SESSTAT v,
v$statname b,
V$SESSION S,
v$process p
where
v.statistic# = b.statistic#
and s.paddr = p.addr
and V.sid=S.sid
-- and s.username is not null
and B.name='opened cursors current'
-- and value > 10
order by
10 desc
Show the open cursor’s SQL
SELECT
s.sid,
s.username,
s.osuser,
S.program,
S.SERIAL#,
S.LOGON_TIME,
S.STATUS,
B.class,
b.name,
V.value,
sql.SQL_TEXT
FROM
V$SESSTAT v,
v$statname b,
V$SESSION S,
V$PROCESS P,
V$SQLTEXT SQL
WHERE
sql.ADDRESS = S.SQL_ADDRESS
AND v.statistic# = b.statistic#
AND s.paddr = p.addr
AND V.sid =S.sid
AND B.name ='opened cursors current'
ORDER BY
p.spid,
sql.hash_value,
sql.piece
This article is very important to me, raises very important issues. Keep writing.
ReplyDeleteFind deals with hotels
Cartier has recommended many fake watches classic and successful watch collections, replica cartier maillon watchessuch as Cartier Tank,replica cartier tank francaise watches Cartier Santos and Cartier Ronde and so on. Every Cartier watch has its special charm, and Cartier watches are many people's dreams. Our shop provides different Cartier replica watches at very low prices. Just enjoy shopping here.
ReplyDeleteyes, its really amazing to read, thanks for sharing this type of useful blogs. emaar beachfront
ReplyDelete