Re: cacheing in Oracle

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/06/25
Message-ID: <31D06710.3AF0_at_teldta.com>

Chris Warwick wrote:
>
> We have been doing some timing trials with Oracle 7.1.6 under SCO OSR 5 and
> discovered that the RDDBMS is doing some sort of cacheing (yes I know if I
> had had time to read all the manuals I would have discovered this...)
>
> The manuals seem to indicate that the DBA can manipulate these values, but we
> are unsure as to how we should do this.
>
> We have performance requirements, so we need to understand the cacheing and
> how to get the best performance out of our database...
>
> Does anyone have any ideas?

What a loaded question. If you are realy interested in tuning your database, buy the book http://www.osborne.com/oracle/ora8.htm (Tuning Oracle). You can get it thru any local book store. See http://www.osborne.com/oracle/index.htm also.

Some tuning scripts can be found at http://www.oramag.com under the archive area. The current issue July/August has some scripts under the "Automating Oracle Tuning" article.

Also check out http://www.tusc.com/cgi-bin/down_app for more tuning Oracle scripts.

The following is an old script but still works okay. There are as many ways to write it and this is just one of them. It will show the database buffer blocks cache stuf but this my friend is only the beginning......

/* BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN */ whenever sqlerror exit failure
set pause off
set verify off
set termout off  

column con_gets new_value READ_CON_GETS select value con_gets
from v$sysstat
where name = 'consistent gets';  

column con_changes new_value WRITE_CON_CHANGES select value con_changes
from v$sysstat
where name = 'consistent changes';  

column db_block_gets new_value READ_DB_BLOCK_GETS select value db_block_gets
from v$sysstat
where name = 'db block gets';  

column db_block_changes new_value WRITE_DB_BLOCK_CHANGES select value db_block_changes
from v$sysstat
where name = 'db block changes';  

column logical_reads new_value READS_LOGICAL select (&READ_CON_GETS + &READ_DB_BLOCK_GETS) logical_reads from dual;  

column logical_writes new_value WRITES_LOGICAL select (&WRITE_CON_CHANGES + &WRITE_DB_BLOCK_CHANGES) logical_writes from dual;  

column physical_reads new_value READS_PHYSICAL select value physical_reads
from v$sysstat
where name = 'physical reads';  

column physical_writes new_value WRITES_PHYSICAL select value physical_writes
from v$sysstat
where name = 'physical writes';  

column blsk new_value BLOCK_SIZE_K
select value / 1024 blsk
from v$parameter
where name = 'db_block_size';  

set termout on  

set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off  

col num              format 999      heading "Nbr"
col name             format a20      heading "Name"
col type             format 999      heading "Type"
col value            format a10      heading "Value"
col meg              format 99.99    heading "Size|Meg"
col isdefault        format a10      heading "IsDefault"

ttitle "Current Parameter Report For Oracle Buffer Cache" select num, name, type, value, ((to_number(value) * &BLOCK_SIZE_K) / 1024) meg, isdefault
from v$parameter
where name = 'db_block_buffers';    

col aa format 999,999,999 heading "Logical|Reads|(memory)"
col bb format 999,999,999 heading "Physical|Reads|(disk)"
col cc format 999.999     heading "Hit|Ratio|(memory)"

ttitle "READ HIT RATIO Report for Oracle Buffer Cache" select &READS_LOGICAL aa,

       &READS_PHYSICAL bb,
       ((&READS_LOGICAL - &READS_PHYSICAL) / &READS_LOGICAL) * 100 cc

from dual;
prompt
prompt NOTE:If the "Hit Ratio" is < 90% increase the init.ora

prompt     parameter "db_block_buffers" (the goal is realy as close to
prompt     100 as posible, 98+ is great).

prompt  

REM col dd format 999,999,999 heading "Logical|Writes|(memory)"
REM col ee format 999,999,999 heading "Physical|Writes|(disk)"
REM col ff format 999.999     heading "Hit|Ratio|(memory)"

REM ttitle "WRITE HIT RATIO Report for Oracle Buffer Cache" REM select &WRITES_LOGICAL dd,

REM        &WRITES_PHYSICAL ee,
REM        ((&WRITES_LOGICAL - &WRITES_PHYSICAL) / &WRITES_LOGICAL) * 100 ff

REM from dual;  

exit;
/* END END END END END END END END END END END END END */ brian.maclean_at_teldta.com Received on Tue Jun 25 1996 - 00:00:00 CEST