Oracle table object wait statistics

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle table object wait statistics

Oracle Database Tips by Donald Burleson

You can query the v$segment_statistics view to get individual waits on specific tables in Oracle 10g.

col "Object" format a20

set numwidth 12

set lines 132

set pages 50

@title132 'Object Wait Statistics'

spool rep_out\&&db\obj_stat_xtab

select * from(

select DECODE(GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object",

sum(case when a.statistic_name = 'ITL Waits' then a.value else null end) "ITL Waits",

sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits",

sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits",

sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads",

sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads"

from

v$segment_statistics a

where

a.owner like upper('&owner')

group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0);

SEE CODE Depot for more oracle table wait statistics scripts

This script interrogates the v$segment_statistics view and shows table-level ITL waits, buffer busy waits, row lock waits, as well as physical reads for each table.

Date: 11/19/05 Page: 1

Time: 11:31 AM Object Wait Statistics SYSTEM

TEST database

Object ITL Waits Buffer Busy Waits Row Lock Waits Physical Reads Logical Reads

-------------------- ------------ ----------------- -------------- -------------- -------------

TEST_TDL 5 20193 0 4075292 23484160

DUAL 1 0 28 448

I_PROCEDURE1 1 0 3
0

TDL_N02_GLOBAL 2 5219 0 24284 134112

All Objects 7 25414 1 5468217 47396384

��