Re: High shared pool usage

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 26 Sep 2011 16:12:36 -0700 (PDT)
Message-ID: <1317078756.7109.YahooMailNeo_at_web65412.mail.ac4.yahoo.com>

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

Received on Mon Sep 26 2011 - 18:12:36 CDT