SGA STATS monitoring Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SGA STATS monitoring

  1. #1
    Join Date
    Oct 2007

    SGA STATS monitoring

    He friends,

    I found a script to monitor database SGA statistics

    rem -----------------------------------------------------------------------
    rem Filename: sga_stat.sql
    rem Purpose: Display database SGA statistics
    rem Date: 14-Jun-2001
    rem Author: Anjan Roy (
    rem -----------------------------------------------------------------------
    set server output on;
    libcac number(10,2);
    rowcac number(10,2);
    bufcac number(10,2);
    redlog number(10,2);
    spsize number;
    blkbuf number;
    logbuf number;
    select value into redlog from v$sysstat
    where name = 'redo log space requests';
    select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
    select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
    select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
    from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
    where cur.statistic# = ncu.statistic#
    and = 'db block gets'
    and con.statistic# = nco.statistic#
    and = 'consistent gets'
    and phys.statistic# = nph.statistic#
    and = 'physical reads';
    select value into spsize from v$parameter where name = 'shared_pool_size';
    select value into blkbuf from v$parameter where name = 'db_block_buffers';
    select value into logbuf from v$parameter where name = 'log_buffer';
    dbms_output.put_line('> SGA CACHE STATISTICS');
    dbms_output.put_line('> ********************');
    dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
    dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
    dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
    dbms_output.put_line('> Redo Log space requests = '||redlog);
    dbms_output.put_line('> ');
    dbms_output.put_line('> INIT.ORA SETTING');
    dbms_output.put_line('> ****************');
    dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
    dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
    dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
    dbms_output.put_line('> ');
    libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
    END IF;
    rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
    END IF;
    bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
    END IF;
    redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
    END IF;

    But when I run this, I only get:

    SQL> @sga_stats

    PL/SQL procedure successfully completed.


    I dont see the output of the>> dbms_output.put_line('>

    Please help.....thanks a lot

  2. #2
    Join Date
    Feb 2006

    Before running this script,just give "set serveroutput on" at SQL prompt.

  3. #3
    Join Date
    Oct 2007
    Ohhhh Thanks a lot dearest friend Malru
    I did that but mine is a wrong syntax ===sql> set server output on

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width