AW: High shared pool usage

From: Petr Novak <Petr.Novak_at_trivadis.com>
Date: Tue, 27 Sep 2011 06:22:45 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B64073C6382D4_at_smxc002.trivadis.com>

Hallo Ram,

you could look in V$SGASTAT how big are the shared pool components , or if you use AWR in DBA_HIST_SGASTAT how the shared pool components increased over time. If you find some surprisingly big compoment , look in Metalink for possible bug. For example , in 10.2.0.4 is bug with memory leak with frequently running snapshots, but I was not able to find this bug now.

Best Regards,
Petr

---

Von: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]&quot; im Auftrag von &quot;Ram Raman [veeeraman_at_gmail.com] Gesendet: Dienstag, 27. September 2011 02:51 Bis: David Fitzjarrell
Cc: ORACLE-L
Betreff: Re: High shared pool usage

Thanks a lot for your help David.

- Aix 6.1 (not sure of the minor ver)
- oracle-10204
- I have to find the answers for the other two questions tomorrow.
- psft fin app.

On Mon, Sep 26, 2011 at 6:12 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:

> 10.2.?? A full release number would be very helpful.
>
> As you stated statements differing only in string literal values can cause
> such a high hard parse rate (a couple of vendors come to mind guilty of not
> using bind variables). In such applications it's not hard to expect such
> behaviour.
>
> Questions that come to mind immediately:
>
> 1) Which operating system?
> 2) Is the hardware NUMA enabled?
> 3) Is Oracle using NUMA? A call to ipcs -m | grep oracle may show
> multiple shared segments with multiple entry points. 10.2.x was notorious
> for not using NUMA correctly causing the hard parse rate to increase since
> sessions may switch memory segments causing Oracle to hard parse
> statements found in the prior accessed NUMA segment but not found in the
> currently used segment. There are ways to address this; MOS document *759565.1
> *describes issues and has links to the various bugs affecting NUMA.
>
> These are merely speculation at this point. More information would be
> greatly beneficial.
>
> David Fitzjarrell
>
>
> *From:* Ram Raman <veeeraman_at_gmail.com>
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Sent:* Monday, September 26, 2011 3:30 PM
> *Subject:* High shared pool usage
>
> List,
> We have a 10.2 db with the ASMM enabled. Here are some of the SGA
> components
> currently:
>
>
> COMPONENT
> CURRENT_SIZE MAX_SIZE OPER_COUNT
> ----------------------------------------------------------------
> ---------------- ---------- ----------
> shared pool
> 12,029,263,872 0 4289
> large pool
> 16,777,216 0 0
> java pool
> 33,554,432 0 2
> streams
> pool
> 0 0 0
> DEFAULT buffer cache
> 9,210,691,584 0 4291
> KEEP buffer cache
> 167,772,160 0 0
> ..
>
> I am surprised to see that the shared_pool is higher than buffer cache. SGA
> target and max size are 20G. I was thinking of the factors that could cause
> high shared pool usage - not using binds, lots of SQLs on a several
> different tables (this is third party apps with 10s of 1000s of tables),
> etc.
>
> I was digging around and I found some statistics that look surprising:
>
> NAME VALUE
> ---------------------------------------- ----------
> parse time cpu 4340323
> parse time elapsed 13931671
> parse count (total) 200405706
> parse count (hard) 34470197
> parse count (failures) 12680
>
>
> SQL> select startup_time from v$instance;
>
>
> STARTUP_T
> ---------
> 30-AUG-11
>
> In less than 30 days, it seems there had been more than a million hard
> parse
> a day. That number looks too much to me. This is an OLTP financial
> application. Any comments?
>
> Thanks.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 27 2011 - 01:22:45 CDT