Thanks a lot for your help David. - Aix 6.1 (not sure of the minor ver) - oracle-10204 - I have to find the answers for the other two questions tomorrow. - psft fin app. On Mon, Sep 26, 2011 at 6:12 PM, David Fitzjarrell <oratune@xxxxxxxxx>wrote: > 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