RE: Largest shared pool
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Apr 2004 12:00:10 -0400
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@xxxxxxxxxxxx]=20
Sent: Tuesday, April 06, 2004 11:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Largest shared pool
Since we are talking about shared pool, I wish, Oracle introduces two
enhancements in this area:
1. 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.
2. 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Tuesday, April 06, 2004 9:13 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx]=3D20
Sent: Monday, April 05, 2004 11:03 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx 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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
