RE: Largest shared pool

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 6 Apr 2004 12:00:10 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B4542E@bosmail00.bos.il.pqe>

Riyaj,

As to your first point, I read here, I think it was stated by Tanel, that starting in 9iR2 there is an undocumented parameter that allows you to define multiple shared pool latches? Unfortunately, searching my archive proved unsuccessful. =20

Tanel, can you refresh my memory?

As to the second point, yeah, that would be a cool idea. Maybe with hints that would automatically direct the statement to that pool? /*+ KEEP */, /*+ DEFAULT */, or /*+ RECYCLE */, or something like that?

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----
From: Riyaj Shamsudeen [mailto:rshamsud_at_jcpenney.com]=20 Sent: Tuesday, April 06, 2004 11:02 AM
To: oracle-l_at_freelists.org
Subject: RE: Largest shared pool

Since we are talking about shared pool, I wish, Oracle introduces two enhancements in this area:

Have multiple latches for the shared pool governance. Have the processes try the latches in a round robin way for n-1 latches and try to get the Nth latch in willing-to-wait mode. This should reduce the stress on shared pool latch. Having a solitaire latch on shared pool *really* gives headache, particularly with the app which does not use bind variables.
Have three pools for library cache also: KEEP, RECYCLE and DEFAULT. If the # of executions for a SQL is beyond a threshold move them to the correct pool. I know, dbms_shared_pool.keep emulates somewhat of the KEEP functionality, but we would really love to have RECYCLE and DEFAULT functionality also. I am not asking to change the library cache bucket structures and as such, but just the way the SQLs are flushed out. May be having couple of lists might solve the problem ?

I know, there are many flaws in here, but may be, somebody is working on Oracle 11g scoping ;-)=20

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Tuesday, April 06, 2004 9:13 AM
To: oracle-l_at_freelists.org
Subject: RE: Largest shared pool

I've had an Oracle analyst tell me in MetaLink, that it's not uncommon to have a 10GB shared pool, which I thought was ludicrous, but, she was adamant. But, Tim seems to have topped even that statement!

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----
From: Tim Gorman [mailto:tim_at_sagelogix.com]=3D20 Sent: Monday, April 05, 2004 11:03 PM
To: oracle-l_at_freelists.org
Subject: Re: Largest shared pool

RDBMS 9.2.0.4 64-bit
Solaris 5.9 (E15K, 36 UltraSPARCIII 1.2Ghz CPUs, 160Gb RAM)

SHARED_POOL_SIZE =3D3D 14,352Mb (i.e. 14.02Gb).

>From V$SGASTAT where POOL =3D3D 'shared pool':

free memory =3D3D 434.05 Mb
SQL area =3D3D 12,099.33 Mb

I think it's way too big for the application (DW), but with 160Gb of RAM to use up, we're doing our best! I've thought about throwing office parties in all that RAM, but then I worry about the sticky mess the next day. And don't even ask about DB_CACHE_SIZE... :-)

We're not experiencing problems querying V$SQL or unusual latch contention or anything (yet)...

on 4/5/04 2:54 PM, Paul Baumgartel at treegarden_at_yahoo.com wrote:

> What's the largest shared pool size you've ever seen?
>=3D20
> I'm doing an evaluation of a company's Oracle environment and, boy, is

>their shared pool huge. Before I say it's the largest I've ever seen,

>though, I'd like to get some confirmation!

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any
review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Apr 06 2004 - 10:58:41 CDT