| |
 |
|
Identifying Oracle Tables with Migrated/Chained Rows
Don Burleson |
This article pertains to tables with variable
length columns and shows how rows may increase in length as they are
updated, the row spilling into the free space defined by PCTREEE, and
ultimately chaining onto multiple data blocks.. We also see
chained rows in cases where the CLOB or BLOB data columns exceeds the database block size.
The identification of these tables that do have migrated/chained rows
is important because of their use of CLOB, BLOB, RAW and LONG RAW data columns.
As far as performance goes, the presence of migrated
or chained rows can severely affect performance. With SQL, for
example, any statements that either query or create/update migrated or
chained rows will be doubling I/O overhead, thus degrading
performance. It is best to quickly identify and rectify
situations involving migrated or chained rows. Chained rows can
also add overhead to the performance of indexes (index range scans and
index fast full scans) and full-table scans.
Migrated rows and chained rows are similar, but each
has a different performance impact on the database. Attempting
to update a row with data that will make it too large for the block
causes the data to be migrated to a space where it will fit after the
update. All that is left in the original block is the forwarding
information.
Simply put, chained rows happen when a row is too
big to fit into a single database block. Chained rows usually result
from an insert. For example, if the blocksize for the database
is 4 kilobytes and there is an 8 kilobyte row to be inserted, Oracle
will break the data into pieces and store it in 3 different blocks
that are chained together. There is forwarding information
available to allow the database to collect all of the bits of chained
row information from multiple blocks.
There are a couple of other conditions that
can contribute to chained rows:
-
Tables with large objects (BLOB, CLOB, NCLOB, RAW and LONG RAW) columns are
historically more prone to ending up with chained rows.
-
Super-wide tables also end up with chained rows.
Tables with in excess of 255 columns may end up with chained rows.
These wide tables get broken up into pieces, with part of the row
residing on two data blocks.
Specific information on migrated or chained rows is
available as follows:
-
Listing 5-5 shows a list of all tables that contain migrated/chained
rows.
-
Listing 5-6 shows the script that generates the
migrated/chained rows report.
Note that this script does not include tables that contain RAW or LONG
column datatypes, since such columns commonly span database blocks and
will chain rows regardless of a database reorganization.
A properly tuned
database should not have any row chaining, so the report shown in
Listing 5-5 may be used as a database integrity check. As you can see,
all of the migrated/chained rows are located in user-defined
tables beginning with the letter "Z".
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3 ZG_TAB 10 40 80 5,003 1,487 .30
TSTR3 ZMM 10 40 422 18,309 509 .03
TSTR3 Z_Z_TBLS 10 40 43 458 53 .12
TSTR3 USR03 10 40 101 327 46 .14
TSTR3 Z_BURL 10 40 116 1,802 25 .01
TSTR3 ZGO_CITY 10 40 56 1,133 10 .01
6 rows selected.
Listing 5-5. A report of tables with
migrated or chained rows
The following code generated the report on
migrated/chained rows. Note
that the use of this script is predicated on the use of Oracle's
ANALYZE command to populate the chain_cnt and num_rows columns of the
dba_tables data dictionary view.
spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
)
and
chain_cnt > 0
order by chain_cnt desc
;
Listing 5-6. The chain.sql script to detect
tables with migrated/chained rows
Because of the large row length of some of the
cluster tables, you will see a lot of chained rows in tables that contain
BLOB, CLOB, RAW and LONG RAW columns. These chained rows are caused by the row length exceeding
the db_block_size, forcing the huge rows to chain onto many blocks.
-
Listing 5-7 shows tables that
contain chained rows caused by row length in excess of
db_block_size.
-
Listing 5-8 contains the script that
generates the report containing tables with chained rows caused by
excessive row lenght compared to db_block_size.
These types of chained row tables are commonly
found in the specific tablespace(s), and database reorganization will not
reduce the chained rows. Unfortunately, there is no remedy to these
chained rows, provided that you are using the maximum supported Oracle
blocksize for your version of UNIX.
For
example, from listing 5-7, we see that the D010L table has an average
row length of over 13k. Because most UNIX operating systems do not
support blocksizes greater than 8k, and as a result, there will be
chained rows.
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3 KOCLU 40 60 1,981 597,125 472,724 .79
TSTR3 CDCLS 40 60 809 712,810 328,989 .46
TSTR3 VBFCL 40 60 1,398 340,917 285,930 .84
TSTR3 EDIDOC 40 60 2,211 158,426 114,859 .73
TSTR3 D010S 10 40 5,129 76,635 43,791 .57
TSTR3 TST03 40 60 6,559 44,596 22,298 .50
TSTR3 T512CLU 40 60 1,055 24,393 21,344 .88
TSTR3 D020L 10 40 1,629 84,968 6,294 .07
TSTR3 EUDB 40 60 3,068 13,910 6,028 .43
TSTR3 D010L 10 40 13,454 4,747 4,747 1
TSTR3 D022S 10 40 774 113,835 3,794 .03
TSTR3 D010Y 10 40 5,435 5,497 2,998 .55
TSTR3 D010Q 10 40 7,351 5,768 2,596 .45
TSTR3 D010T 40 60 1,220 39,550 1,720 .04
TSTR3 DSYO2 40 60 2,946 627 289 .46
TSTR3 SFHOA 40 60 3,247 319 152 .48
TSTR3 DSYO1 40 60 2,695 426 56 .13
TSTR3 RFDT 40 60 2,629 740 26 .04
TSTR3 INDX 40 60 2,288 302 20 .07
TSTR3 MACOB 40 60 694 103 2 .02
TSTR3 USR04 40 60 74 434 1 .00
Listing 5-7. A
report of migrated/chained rows in tables that contain RAW
columns.
set heading off;
select 'Tables with migrated/chained rows that contain RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
)
and
chain_cnt > 0
order by chain_cnt desc
;
spool off;
Listing 5-8. The chain1.sql script to identify
migrated/chained rows in tables that contain RAW or LONG RAW
columns.
Bobby Durrett has this nice script to detect if a
current session is accessing high volume of chained rows (table fetch
continued row fetch):
-- get the required session ID
variable monitored_sid number;
begin
SELECT
sid
into :monitored_sid
from
v$session
where
audsid=USERENV('SESSIONID');
end;
/
select
b.value "table fetch continued rows"
from
v$sesstat b
where
b.SID = :monitored_sid
and
b.statistic# =
(select
statistic#
from
v$statname
where
name='table fetch continued row');
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
|
|
