RE: v$sql - executions vs loads vs invalidations
Date: Tue, 1 May 2012 18:31:46 -0700
Message-ID: <CFE841DFC1E5401E9AF56B1F90E8B687_at_TerryW520>
I may have missed it, but what does v$sql_shared_cursor say is the reason for the invalidations?
--Terry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Gerry Miller
Sent: Tuesday, May 01, 2012 5:38 AM
To: GG
Cc: Oracle-L Group
Subject: Re: v$sql - executions vs loads vs invalidations
Hi GG
These cursors don't have a high version count; there is only one of each, but it has been loaded thousands of times.
I just had a look at Tanel's script and I have already run some queries
against X$KSMSS , which suggested that the problem is with subpool 2 (out
of 4). I then ran this query:
SELECT KGHLUSHRPOOL "SUBPOOL", KGHLURCR "PINS AND|RELEASES", KGHLUTRN,
KGHLUFSH, KGHLUOPS, KGHLUNFU "ORA-4031|ERRORS", KGHLUNFS "LAST ERROR|SIZE"
FROM SYS.X$KGHLU
and it said that ALL of the ORA_04031 errors occurred in subpool 2.
Until then I was sceptical about subpool imbalance being the cause but now I
am not so sure.
Thanks for your help on this.
Gerry
GG wrote:
> 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 01 2012 - 20:31:46 CDT