High shared pool usage

From: Ram Raman <veeeraman_at_gmail.com>
Date: Mon, 26 Sep 2011 17:30:24 -0500
Message-ID: <CAHSa0M3cR3gTVhfRo0utdbHbmOUP3a0iCqYXKJPNXvRG+0iMeQ_at_mail.gmail.com>

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

Received on Mon Sep 26 2011 - 17:30:24 CDT