| |
 |
|
Oracle Tips by Burleson |
Global Basic Queries
The first step in unraveling any I/O puzzles in databases is to make
a quick check of some of the global database I/O metrics.
A query such as the following globiostats.sql script can be used to
get a bird’s eye view of a database’s I/O:
* globiostats.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
name,
value
from
sys.v_$sysstat
where
name in
('consistent changes',
'consistent gets',
'db block changes',
'db block gets',
'physical reads',
'physical writes',
'sorts (disk)',
'user commits',
'user rollbacks'
)
order by
1;
The output from the above query might look like the following:
NAME VALUE
---------------------------
consistent changes 1
consistent gets 70983
db block changes 243
db block gets 612
physical reads 11591
physical writes 52
sorts (disk) 0
user commits 26
user rollbacks 1
Some database experts do not believe the buffer cache hit ratio is
of much value anymore. There are some valid reasons for assuming
such a stance; however, a cursory check should still be performed to
get an idea of overall disk I/O activity by using the buffratio.sql
script:
* buffratio.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
100 -
100 *
(round ((sum (decode (name, 'physical reads',
value, 0))
-
sum (decode (name, 'physical reads direct',
value, 0)) -
sum (decode (name,
'physical reads direct (lob)',
value, 0))) /
(sum (decode (name, 'session logical reads',
value, 1))
),3)) hit_ratio
from
sys.v_$sysstat
where
name in
('session logical reads',
'physical reads direct (lob)',
'physical reads',
'physical reads direct');
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
