RE: Shared pool from 8i to 9i

  • From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • To: 'Mladen Gogala' <mgogala@xxxxxxxxxxxxxxxxxxxx>, "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • Date: Thu, 27 Jan 2005 12:17:12 -0500

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@xxxxxxxxxxxxxxxxxxxx] 
Sent: Tuesday, January 25, 2005 9:51 AM
To: Nagarajan.Subbiah@xxxxxxxx
Cc: 'Alexander Gorbachev'; Wolfgang Breitling;
Paula_Stankus@xxxxxxxxxxxxxxx; DGoulet@xxxxxxxx; BSpears@xxxxxxxxxxxxxxxxx;
Michael.Kline@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l

Other related posts: