Re: High shared pool usage
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Sep 2011 16:12:36 -0700 (PDT)
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@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
