Sunday, October 28, 2012

3 Cursor

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.

image

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

 

image

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

image

3 comments:

  1. This article is very important to me, raises very important issues. Keep writing.
    Find deals with hotels

    ReplyDelete
  2. 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.

    ReplyDelete
  3. yes, its really amazing to read, thanks for sharing this type of useful blogs. emaar beachfront

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates