Re: High shared pool usage
Date: Tue, 27 Sep 2011 10:04:27 +0300
Message-ID: <OF8D604B43.58ED338D-ONC2257918.00262F4E-C2257918.0026DC33_at_seb.lt>
> more than a million hard parse
a day. That number looks too much to me. This is an OLTP financial application. Any comments?
Comment 1: that's about 11.5 hard parses a second. Not that bad.
Comment 2: looking into counters is good but looking into time spent is even better: first comes hard parsing time then contention: lathing on pool structures, etc. Specially at hard parsing peak times.
Comment 3: Developers will never learn: SQL standard allows for literal sql. Formally, developers are in their right. Now decision must be taken to fix the code (which has lot of risks) or try to make the system hard parse more efficiently (if that is necessary.)
just my 2 cents,
Laimis N
Please consider the environment before printing this e-mail
From: Ram Raman <veeeraman_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org>
Date: 2011.09.27 01:31
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-lReceived on Tue Sep 27 2011 - 02:04:27 CDT