Re: v$sql - executions vs loads vs invalidations
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
#########################################endlike _at_sgastatx free
Regards
GregG
*
*
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2012 - 07:04:14 CDT