SQL Script

Oracle Parallel Server Statistics

Updated: 26-Sep-2002
Version: 1.0

Description

Lists Basic File Pinging and other statistics related to Parallel Server

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

REM You must run this script as SYS
REM You must run catparr for 7.3 before running this script.

col tablespace_name form a10
col file_name form a45
col lock_element_addr form a8

set pages 200

spool opsstat.lst

REM    The following query shows how many times each buffer has been pinged
REM    out of the buffer cache.

SELECT  df.tablespace_name, df.file_name, SUM(NVL(buf.xnc,0))    "PINGS"
  FROM  dba_data_files df, v$bh buf
  WHERE df.file_id = buf.file#
GROUP BY df.tablespace_name, df.file_name
/

REM    The following query shows the number of cross instance operations 
REM    that caused writes to the database and the number of blocks written.

SELECT value / (a.counter + b.counter + c.counter) "Ping Rate"
   FROM v$sysstat,
              v$lock_activity a,
              v$lock_activity b,
              v$lock_activity c
 WHERE  a.from_val = 'X'
       AND a.to_val       = 'NULL'
       AND b.from_val = 'X'
       AND b.to_val       = 'S'
       AND c.from_val  = 'X'
       AND c.to_val        = 'SSX'
       AND name            = 'DBWR cross instance writes'
/

REM    The following query shows the average number of blocks allocated to 
REM    each class & shows the number of locks allocated to each class

SELECT  class, avg(block_count), count(*)
  FROM  v$lock_element
 GROUP BY class
/


REM The following query shows the percentage of physical writes to the
REM database that have been due to pings.

SELECT(a.value / b.value) * 100 , a.value Xinst_writes, b.value PhyWrites
  FROM v$sysstat a, v$sysstat b
 WHERE a.name = 'DBWR cross instance writes'
    AND b.name  = 'physical writes'
/

REM    The following query shows the lock conversion ratio. If the ratio 
REM    falls below 95% after you have tuned parallel server, it is likely
REM    that your site will not scale if you have to add additional nodes
REM    and instances.

SELECT (a.value - b.value) * 100 
                           /
                   (a.value)  "Lock Conversion Ratio"
  FROM v$sysstat a, v$sysstat b
 WHERE a.name = 'consistent gets'
   AND b.name = 'async lock converts'
/

REM The following query shows the number of locks per datafile.

SELECT ts_name tablespace_name, file_name, frequency 
  FROM sys.file_ping;

SELECT file_name, start_lk, nlocks, blocking
  FROM sys.file_lock;

REM    The following query shows the number of global locks. If you are 
REM    tuning parallel server and see no reduction in the figures below, 
REM    you are wasting your time tuning further.

SELECT name, COUNT(*) 
  FROM v$sysstat
 WHERE name LIKE 'global%'
    OR name LIKE 'nxt scns gotten%'
GROUP BY name
/

REM    The following query shows the index contention.

SELECT  usr.name, vp.name, vp.kind, SUM(vp.xnc)
  FROM  sys.user$ usr, v$ping vp
WHERE  vp.owner# = usr.user#
GROUP  BY usr.name, vp.name, vp.kind
/

REM    The following query shows the number of PCM locks being used by 
REM    the datafile.

SELECT df.tablespace_name, df.file_name, buf.lock_element_addr, COUNT(*)
  FROM dba_data_files df, v$bh buf
  WHERE df.file_id = buf.file#
GROUP BY df.tablespace_name, df.file_name, buf.lock_element_addr
HAVING COUNT(*) > 1
/


spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Oracle Parallel Server Statistics

Updated: 26-Sep-2002Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 26-Sep-2002
Version: 1.0