RE: Shared pool from 8i to 9i

Thanks, Everyone. We have reduced the SGA size and waiting for the two other OS kernal config changes to be done by our SA.

One is the data limit is very low (ulimit -a) and the other one filesystem buffer has been assigned as 50% default for the server. I hope this will resolve the ORA-4030 error we are getting.

Thanks, Again!

Raja.

-----Original Message-----
From: Mladen Gogala [mailto:mgogala_at_allegientsystems.com] Sent: Tuesday, January 25, 2005 9:51 AM
To: Nagarajan.Subbiah_at_aetn.com
Cc: 'Alexander Gorbachev'; Wolfgang Breitling; Paula_Stankus_at_doh.state.fl.us; DGoulet_at_vicr.com; BSpears_at_Limitedbrands.com; Michael.Kline_at_SunTrust.com; oracle-l_at_freelists.org Subject: Re: Shared pool from 8i to 9i

Subbiah, Nagarajan wrote:

>
>Can we have different sub pools of shared pool and specify the range
>for the SQL size to go to the particular sub pool? I know that the
>shared_pool_reserved_min_alloc parameter helps to certain extent. Is
>there any view to find out what is the usage of the reserved size of
>the shared_pool and how to find out the optimal value of the
>shared_pool_reserved_size?
>
>

Raja, take a look at the V$SHARED_POOL_RESERVED. It contains column "LAST_ABORTED_SIZE".
It will tell you the size of the last request for the reserved allocation that failed. You also have a column about aborted requests to tell you how many aborted requests were issued. Note that this is a RESERVED allocation. It doesn't automatically mean 4031. Also, there is a table called V$SHARED_POOLwhich is gone in 10g, probably because oracle thinks that you should pay for being able to estimate the size of your shared pool. In 10g it is called DBA_HIST_SHARED_POOL_ADVICE <http://localhost/oradoc/ora10.1.0/server.101/b10755/statviews_2151.htm#sthr ef1843>
and, as any "DBA_HIST" table should not be used without license. Enjoy while you can.

-- 
Mladen Gogala
Oracle DBA
--
http://www.freelists.org/webpage/oracle-l