High shared pool usage
- From: Ram Raman <veeeraman@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Sep 2011 17:30:24 -0500
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
Other related posts:
