Re: v$sql - executions vs loads vs invalidations

From: GG <grzegorzof_at_interia.pl>
Date: Tue, 01 May 2012 14:04:14 +0200
Message-ID: <4F9FD13E.5010302_at_interia.pl>



Good , You've got MOS access ,so
follow *Troubleshooting: High Version Count Issues [ID 296377.1]

and then **Script - Determine reason for high Child Cursor Count - V$SQL_SHARED_CURSOR [ID 1422573.1] . And I recommend following Carlos Sierra advice related to '*SQLT XTRACT' as he is the Author :) that neat tool .
I had bad experience with cursor sharing similar but looks like FORCE could cause troubles as well (unsafe binds). Speaking about shared pool imbalance You can use Tanel script:



--
  • File name: sgastatx
  • Purpose: Show shared pool stats by sub-pool from X$KSMSS
    --
  • Author: Tanel Poder
  • Copyright: (c) http://www.tanelpoder.com
    --
  • Usage: _at_sgastatx <statistic name>
  • _at_sgastatx "free memory"
  • _at_sgastatx cursor
    --
  • Other: The other script for querying V$SGASTAT is called sgastat.sql
    --
    --
    --

COL subpool HEAD SUBPOOL FOR a30

PROMPT
PROMPT -- All allocations:

SELECT
     'shared pool ('||NVL(DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx), 'Total')||'):' subpool

   , SUM(ksmsslen) bytes
   , ROUND(SUM(ksmsslen)/1048576,2) MB
FROM
     x$ksmss
WHERE
     ksmsslen > 0
--AND ksmdsidx > 0

GROUP BY ROLLUP
    ( ksmdsidx )
ORDER BY
     subpool ASC
/

BREAK ON subpool SKIP 1
PROMPT -- Allocations matching "&1":

SELECT
     subpool

   , name
   , SUM(bytes)
   , ROUND(SUM(bytes)/1048576,2) MB
FROM (
     SELECT
         'shared pool ('||DECODE(TO_CHAR(ksmdsidx),'0','0 - 
Unused',ksmdsidx)||'):'      subpool
       , ksmssnam      name
       , ksmsslen      bytes
     FROM
         x$ksmss
     WHERE
         ksmsslen > 0
     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
)
GROUP BY
     subpool
   , name
ORDER BY
     subpool ASC
   , SUM(bytes) DESC
/

BREAK ON subpool DUP

#########################################end
like _at_sgastatx free

Regards
GregG

*
*
--

http://www.freelists.org/webpage/oracle-l Received on Tue May 01 2012 - 07:04:14 CDT

Original text of this message