Friday, July 1

Oracle I/O tuning (or not to)

When an Oracle table is updated and new inserts happen it is possible for a row to end up on more than one block, this will cause more disk I/O than what is really necessary. It's not a huge problem in a smaller installation but may become an issue if you have very large tables and lots of fetches.
Here is a small script that a schema owner can use to find chained rows in his/hers tables.
column AVG format 99.99
select
table_name,
avg_row_len,
num_rows,
chain_cnt,
chain_cnt/num_rows*100 AVG
from
user_tables
where
chain_cnt >0
/


TABLE_NAME AVG_ROW_LEN NUM_ROWS CHAIN_CNT AVG
-------------- ----------- ---------- ---------- ------
INVENTORY 87 3066 1377 .45

Ok, so we got a table that has got some quite a high percentage of chained rows.
First thought in most DBAs head would be to resolve the "problem" as soon as possible, start rebuilding things. Why do DBAs always want to rebuild things? Indexes tables and god knows what. I'd say 75% or all object rebuild are unnecessary or even damaging (i.e. loosing index statistics).
But does it really matter, do we really fetch these rows? Lets check.
select 
name,
value
from
v$sysstat
where
name like 'table fetch cont%'
/


NAME VALUE
------------------------------- ----------
table fetch continued row 4000

So there seems to be some problems with chained rows. We now need to either rebuild the table or simply delete and re-insert the chained rows. The later solution is of course preferred but may not be possible due to constraints that cannot be dropped.
Oracle provides nice tools to find chained rows and re-insert them.
A quick guide on how to that is provided here: Eliminating Migrated or Chained Rows in a Table
To create the CHAINED_ROWS table that is mention in the above article issue this command at your SQL*Plus prompt.
@?/rdbms/admin/utlchain

There is also a quite nice thread about this over at Ask Tom

Update:
To see all you basic row fetch stats use this query.
select 
name,
value
from
v$sysstat
where
name like 'table fetch%'
or name like 'table scan%gotten'
/

No comments: