Re: v$sql - executions vs loads vs invalidations
- From: GG <grzegorzof@xxxxxxxxxx>
- To: Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 01 May 2012 14:04:14 +0200
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: @sgastatx <statistic name>
-- @sgastatx "free memory"
-- @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 @sgastatx free
Regards
GregG
*
*
--
//www.freelists.org/webpage/oracle-l
Other related posts:
