From oracle-l-bounce@freelists.org Tue Apr 6 10:09:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i36F94X24098 for ; Tue, 6 Apr 2004 10:09:04 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i36F93o24093 for ; Tue, 6 Apr 2004 10:09:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C094E635942; Tue, 6 Apr 2004 09:58:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20784-94; Tue, 6 Apr 2004 09:58:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EAC85635967; Tue, 6 Apr 2004 09:58:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 06 Apr 2004 09:57:40 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A9FE663604E for ; Tue, 6 Apr 2004 09:57:39 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20768-83 for ; Tue, 6 Apr 2004 09:57:39 -0500 (EST) Received: from marcie.jcpenney.com (146-235-101-30-jcp.jcpenney.com [146.235.101.30]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 294B763604D for ; Tue, 6 Apr 2004 09:57:39 -0500 (EST) Received: from i006.jcpenney.com (i006.jcpenney.com [10.33.181.215]) by marcie.jcpenney.com (8.12.10+Sun/8.12.10) with ESMTP id i36F65Pn024358 for ; Tue, 6 Apr 2004 10:06:51 -0500 (CDT) Received: from rshamsudxp ([10.32.47.33]) by i006.jcpenney.com (iPlanet Messaging Server 5.2 HotFix 1.17 (built Jun 23 2003)) with ESMTPA id <0HVR00I0C8F8R9@i006.jcpenney.com> for oracle-l@freelists.org; Tue, 06 Apr 2004 10:01:57 -0500 (CDT) Date: Tue, 06 Apr 2004 10:01:54 -0500 From: Riyaj Shamsudeen Subject: RE: Largest shared pool In-reply-to: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B45427@bosmail00.bos.il.pqe> To: oracle-l@freelists.org Message-id: <003601c41be8$19e22470$212f200a@rshamsudxp> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Mailer: Microsoft Outlook, Build 10.0.3416 Content-type: text/plain Content-Transfer-Encoding: 8bit Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Disclaimer: Outbound Correspondence X-VirusScan: JCP virus scan detected no virus X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2608 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rshamsud@jcpenney.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 ;-) Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Bobak, Mark Sent: Tuesday, April 06, 2004 9:13 AM To: oracle-l@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@sagelogix.com]=20 Sent: Monday, April 05, 2004 11:03 PM To: oracle-l@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 =3D 14,352Mb (i.e. 14.02Gb). >From V$SGASTAT where POOL =3D 'shared pool': * free memory =3D 434.05 Mb * SQL area =3D 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@yahoo.com wrote: > What's the largest shared pool size you've ever seen? >=20 > 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@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@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@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 -----------------------------------------------------------------