Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 4124 invoked from network); 4 Dec 2007 11:05:12 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 4 Dec 2007 11:05:04 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E8477CCBAA; Tue, 4 Dec 2007 12:05:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23152-08; Tue, 4 Dec 2007 12:05:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7A0B27D152B; Tue, 4 Dec 2007 12:05:01 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 04 Dec 2007 11:18:04 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 17FBA7CF8A3 for ; Tue, 4 Dec 2007 11:18:04 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16054-02 for ; Tue, 4 Dec 2007 11:18:03 -0500 (EST) Received: from kbspmxp1.starhub.net.sg (kbspmxp6.starhub.net.sg [203.116.61.216]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3BB627CDF7F for ; Tue, 4 Dec 2007 11:18:01 -0500 (EST) Received: from kbsmtao2.starhub.net.sg (kbsmtao2.starhub.net.sg [203.116.2.167]) by kbspmxp1.starhub.net.sg (8.13.7+Sun/8.13.7) with ESMTP id lB4GBuxo005579 for ; Wed, 5 Dec 2007 00:11:57 +0800 (SGT) Received: from windows01 ([218.212.240.172]) by kbsmtao2.starhub.net.sg (Sun Java System Messaging Server 6.2-4.03 (built Sep 22 2005)) with ESMTPP id <0JSJ0011C99XV110@kbsmtao2.starhub.net.sg> for oracle-l@freelists.org; Wed, 05 Dec 2007 00:17:58 +0800 (SGT) Date: Wed, 05 Dec 2007 00:17:54 +0800 From: Tanel Poder Subject: RE: Forcing ASMM component to shrink In-reply-to: <001a01c8364f$900ac3c0$b0204b40$@co.uk> To: vishal@vishalgupta.co.uk, oracle-l@freelists.org Message-id: <026701c83691$39ca3490$3201a8c0@windows01> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 Content-Type: multipart/alternative; boundary="Boundary_(ID_uv6IolH+1we7vvHycpO6iQ)" References: <001a01c8364f$900ac3c0$b0204b40$@co.uk> X-archive-position: 3694 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-to: tanel.poder.003@mail.ee List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --Boundary_(ID_uv6IolH+1we7vvHycpO6iQ) Content-Type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Note that Oracle 10.2 *does* shrink shared pool automatically if needed... it can even keep buffer cache buffers in shared pool starting from 10.2... http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm -- Regards, Tanel Poder http://blog.tanelpoder.com _____ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Vishal Gupta Sent: Tuesday, December 04, 2007 16:28 To: oracle-l@freelists.org Subject: RE: Forcing ASMM component to shrink I have found the way to forcibly shrink shared pool dynamically. Documentation stats that ASMM can only increase shared pool, it can not shrink it. To shrink shared_pool_size immediately, you have to switch to manual mode. After shrinking you can again switch back to ASMM mode. http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#st href383 Automatic Tuning and the Shared Pool When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit. --Boundary_(ID_uv6IolH+1we7vvHycpO6iQ) Content-Type: text/html; charset=us-ascii Content-transfer-encoding: 7BIT
Note that Oracle 10.2 *does* shrink shared pool automatically if needed... it can even keep buffer cache buffers in shared pool starting from 10.2...
 
 
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Vishal Gupta
Sent: Tuesday, December 04, 2007 16:28
To: oracle-l@freelists.org
Subject: RE: Forcing ASMM component to shrink

I have found the way to forcibly shrink shared pool dynamically. Documentation stats that ASMM can only increase shared pool, it can not shrink it. To shrink shared_pool_size immediately, you have to switch to manual mode. After shrinking you can again switch back to ASMM mode.

 

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref383

Automatic Tuning and the Shared Pool

When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.

 

--Boundary_(ID_uv6IolH+1we7vvHycpO6iQ)-- -- http://www.freelists.org/webpage/oracle-l