[Orca-users] Re: Oracle Stats

[Adam Levin]

On Fri, 23 Aug 2002, Liston Bias wrote:
>   $SQL_SCRIPTS/db_metrics.sql

Whoops, forgot that one, sorry.  It's attached.

>   $UNIX_SCRIPTS/change.sh

That was db_metrics_change.sh -- I sent it along.  I tried to make the
name more descriptive.  :)

-Adam

Adam Levin, Senior Unix Systems Administrator | http://www.audible.com/
Audible, Inc.                       He'd never realized that, deep down
Wayne, NJ, 07470                       inside, what he really wanted to
973-837-2797                               do was make things go splat.
-------------- next part --------------
SET PAGESIZE 60 LINESIZE 132 FEEDBACK OFF HEADING OFF
SELECT  COUNT(username)
FROM    v$session
WHERE   type = 'USER'
;
SELECT COUNT(SID)
FROM   v$session
WHERE  lockwait IS NOT NULL
;
SELECT SUM(phyrds+phywrts)
FROM   v$filestat
;
SELECT value
FROM   v$sysstat
WHERE  name = 'redo log space requests'
;
SELECT value
FROM   v$sysstat
WHERE  name='redo buffer allocation retries'
;
SELECT s.value
FROM   v$statname n
     , v$sysstat s
WHERE  n.statistic# = s.statistic#
AND    n.name = 'table fetch by rowid'
;
SELECT sum(s.value)
FROM   v$statname n
     , v$sysstat s
WHERE  n.statistic# = s.statistic#
AND    n.name IN (
 'table scans (short tables)'
,'table scans (long tables)'
)
;
SELECT ROUND(gethitratio,3)*100
FROM   v$librarycache
WHERE  namespace = 'SQL AREA'
;
SELECT ROUND(SUM(reloads)/SUM(pins),3)*100
FROM   v$librarycache
;
SELECT ROUND(SUM(getmisses)/SUM(gets),3)*100
FROM   v$rowcache
;
SELECT ROUND(1 - (phy.value/(cur.value+con.value)),3)*100
FROM   v$sysstat cur,
       v$sysstat con,
       v$sysstat phy
WHERE  cur.name='db block gets'
AND    con.name='consistent gets'
AND    phy.name='physical reads'
;
SELECT value
FROM   v$sysstat
WHERE  name='free buffer inspected'
;
SELECT SUM(total_waits)
FROM   v$system_event
WHERE  event IN ('free buffer waits','buffer busy waits')
;
SELECT ROUND(1-(sleeps/gets),3)*100
FROM   v$latch
WHERE  name='cache buffers lru chain'
;
SELECT ROUND(SUM(waits)*100/SUM(gets),3)*100
FROM   v$rollstat
;
SELECT SUM(count)
FROM   v$waitstat
where  class like '%undo%'
;
SELECT value
FROM   v$sysstat
WHERE  name='consistent gets'
;