Sunday, October 28, 2012

6 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

6 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
  4. i found the best reading blogs on google, thanks for sharing. perfumes online

    ReplyDelete
  5. Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome!
    interior design companies in uae | engineering consultants in dubai

    ReplyDelete
  6. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!.
    dubai municipality approval | abu dhabi municipality approval

    ReplyDelete

 

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