Quick Search:
 Oracle PL/SQL: Display database SGA statistics Jump to:  
Category: >> Oracle PL/SQL >> Display database SGA statistics  

<< lastnext >>

Snippet Name: Display database SGA statistics

Description: Handy routine to display the database's SGA statistics.

Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» Database Links: CURRENT_USER
» Instant Test Database with DCBA
» Show info on current context
» Lookup Oracle error messages
» Display and release DBMS_LOCK locks
» Display locks and latches
» Show rollback segment stats
» Show active transactions
» List supported INIT.ORA parameters
» Measure the Buffer Cache Hit Ratio
» List security related profile informat...
» Find users with deadly privileges
» Audit User Logins (User Login Trigger)
» Block TOAD and other tools
» Kill Session
» Extents
» DBA Users
» DBA Tablespaces
» DBA triggers
» DBA Sessions
» DBA Roles
» DBA Objects
» DBA Links
» DBA Jobs
» Job Queue
» DBA Free Space
» Data Files
» DBA Extents

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

      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 ncu.name = 'db block gets'
        AND con.statistic# = nco.statistic#
        AND nco.name = 'consistent gets'
        AND phys.statistic# = nph.statistic#
        AND nph.name = '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.');
     rowcac < 85  THEN DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
     bufcac < 90  THEN DBMS_OUTPUT.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
     redlog > 100 THEN DBMS_OUTPUT.put_line('*** HINT: Log Buffer value is rather low!');

   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2023 psoug.org