Re: v$sql - executions vs loads vs invalidations

From: Gerry Miller <gerry_at_millerandbowman.com>
Date: Wed, 02 May 2012 17:18:26 +1000
Message-ID: <4FA0DFC2.4080303_at_millerandbowman.com>

 Hi Subodh
Linux RHEL 5.3 64-bit and a vendor-written application.

Thanks

Gerry

Subodh Deshpande wrote: is this is a linux..rhel 4.x what is the front end application..oracle forms... there is one parameter with which you can increase the size of generated trc files and make sure that there not too many audit programs running..and then start drilling down.. thanks..subodh
On 1 May 2012 18:07, Gerry Miller <gerry_at_millerandbowman.com[1]> wrote: 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[2]

--
http://www.freelists.org/webpage/oracle-l[3]





-- 
=============================================
Love me or Hate me both are in my Favour.
Love me, I am in your Heart. Hate me, I am in your Mind.
=============================================




--- Links ---
   1 mailto:gerry_at_millerandbowman.com
   2 http://www.tanelpoder.com
   3 http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 02 2012 - 02:18:26 CDT