Re: Problem with Oracle SGA larger than 1GB

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Fri, 27 Aug 1999 15:20:30 GMT
Message-ID: <37c6ab03.241965170@news.earthlink.net>

Why are you making your shared_pool_size so big? I never had one bigger then 25M
shared_pool contains parsed code(shared sql pool) and dictionary cache, and unless you have millions of users accessing the DB via ad hoc queries, you don't need shared_pool_size that big. You might want to increase DB_BLOCK_BUFFERS which store the actual data from the disks. So if your DB is under 1Gig, you can store most of it in RAM.

Run the following queries in sqlplus as user system. And see the results. Also, the reason you might be having problems with increasing your SGA b/c of certain OS parameters that you have to modify for Oracle to be able to allocate a piece of memory in one chunk of 2Gig.

prompt **********************************************************

prompt Hit Ratio Section

prompt **********************************************************
prompt
prompt         ========================= 
prompt         BUFFER HIT RATIO 
prompt         ========================= 

prompt (should be > 70, else increase db_block_buffers in init.ora)

column "logical_reads" format 99,999,999,999
column "phys_reads"    format 999,999,999
column "phy_writes"    format 999,999,999
select a.value + b.value  "logical_reads",
       c.value            "phys_reads",
       d.value            "phy_writes",
       round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))  
         "BUFFER HIT RATIO"

from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d where

   a.statistic# = 37
and

   b.statistic# = 38
and

   c.statistic# = 39
and

   d.statistic# = 40;

prompt
prompt

prompt         ========================= 
prompt         DATA DICT HIT RATIO 
prompt         ========================= 

prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt

column "Data Dict. Gets" format 999,999,999 column "Data Dict. cache misses" format 999,999,999 select sum(gets) "Data Dict. Gets",

       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT

RATIO"
from v$rowcache;

prompt

prompt         ========================= 
prompt         LIBRARY CACHE MISS RATIO
prompt         ========================= 

prompt (If > 1 then increase the shared_pool_size in init.ora) prompt

column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions"    format 999,999,999
column "Cache misses while executing"    format 999,999,999

select sum(pins) "executions", sum(reloads) "Cache misses while executing",

    (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO" from v$librarycache;  

prompt

prompt         ========================= 
prompt          Library Cache Section
prompt         ========================= 

prompt hit ratio should be > 70, and pin ratio > 70 ... prompt

column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio", trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads" from v$librarycache;
prompt
prompt

prompt         ========================= 
prompt         REDO LOG BUFFER 
prompt         ========================= 

prompt (should be near 0, else increase size of LOG_BUFFER in init.ora)
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),

       value
from v$sysstat where name = 'redo log space requests';

On Fri, 27 Aug 1999 14:39:50 +0200, Alexander Schiftner <weissdv_at_via.at> wrote:

>I am trying to increase the shared_pool_size of my Oracle-Instance to about 3GB.
>When starting up the instance,
>Oracle is not able of attaching the shared memories. I am using Oracle 7.3.4 on
>a NCR S50 with NCR-Unix MP-RAS 3.02. Since the S50 is configured with 4GB of
>RAM, I would like to use 3GB of it for the Oracle-Instance, to improve
>performance.
>Is there anyone who knows such problems?
>Please help!!!
>
>Alex Schiftner
>
>--
>-----------------------------------------------------------------------------
> | AUSTROSOFT (R) Weiss
> | Datenverarbeitung GmbH
>Email: | Lilienberggasse 13
>Alexander Schiftner <weissdv_at_via.at> | A -1130 Wien, Austria
> | Telephon: +43 1 877 62 05-17
> | Fax: +43 1 877 62 05-21
>-----------------------------------------------------------------------------
>
>

---

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 373-5417
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Fri Aug 27 1999 - 10:20:30 CDT