Re: High shared pool usage

- 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