| |
 |
|
Identifying Oracle Tables with migrated/chained rows
Don Burleson
|
As we stated in a previous chapter, SAP has designed
their tables to store row data in CHAR format rather than variable
length VARCHAR columns. Hence, we should not see row chaining
except in cases where row length exceeds the database block size.
The identification of these tables that do have migrated/chained rows
is important because of their use of RAW and LONG RAW data columns.
Listing 5-5 shows a list of all tables that contain migrated/chained
rows. Listing 5-6 shows the script that generates this 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 regardless of a database reorganization. A properly tuned
SAP 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 SAP
tables (you can tell because they begin with the letter “Z”).
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
SAPR3 ZG_TAB 10 40 80 5,003 1,487 .30
SAPR3 ZMM 10 40 422 18,309 509 .03
SAPR3 Z_Z_TBLS 10 40 43 458 53 .12
SAPR3 USR03 10 40 101 327 46 .14
SAPR3 Z_BURL 10 40 116 1,802 25 .01
SAPR3 ZGO_CITY 10 40 56 1,133 10 .01
6 rows selected.
Listing 5-5. A report of SAP tables with
migrated/chained rows
Here is the code that generated the report. 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')
)
and
chain_cnt > 0
order by chain_cnt desc
;
Listing 5-6. The chain.sql script to detect SAP
tables with migrated/chained rows
Because of the large row length of some of the SAP
cluster tables, you will see a lot of chaining in tables that contain
RAW and LONG RAW columns. (This is because the row length will exceed
the db_block_size, forcing the huge rows to chain onto many blocks)
Listing 5-7 shows such tables. Listing 5-8 contains the script that
generates this report. These types of chained row tables are commonly
found in the PSAPCLUD tablespace, and database reorganization will not
reduce the chaining. Unfortunately, there is no remedy to this row
chaining, 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, the rows in this table are going
to chain.
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
SAPR3 KOCLU 40 60 1,981 597,125 472,724 .79
SAPR3 CDCLS 40 60 809 712,810 328,989 .46
SAPR3 VBFCL 40 60 1,398 340,917 285,930 .84
SAPR3 EDIDOC 40 60 2,211 158,426 114,859 .73
SAPR3 D010S 10 40 5,129 76,635 43,791 .57
SAPR3 TST03 40 60 6,559 44,596 22,298 .50
SAPR3 T512CLU 40 60 1,055 24,393 21,344 .88
SAPR3 D020L 10 40 1,629 84,968 6,294 .07
SAPR3 EUDB 40 60 3,068 13,910 6,028 .43
SAPR3 D010L 10 40 13,454 4,747 4,747 1
SAPR3 D022S 10 40 774 113,835 3,794 .03
SAPR3 D010Y 10 40 5,435 5,497 2,998 .55
SAPR3 D010Q 10 40 7,351 5,768 2,596 .45
SAPR3 D010T 40 60 1,220 39,550 1,720 .04
SAPR3 DSYO2 40 60 2,946 627 289 .46
SAPR3 SFHOA 40 60 3,247 319 152 .48
SAPR3 DSYO1 40 60 2,695 426 56 .13
SAPR3 RFDT 40 60 2,629 740 26 .04
SAPR3 INDX 40 60 2,288 302 20 .07
SAPR3 MACOB 40 60 694 103 2 .02
SAPR3 USR04 40 60 74 434 1 .00
Listing 5-7. A
report of migrated/chained rows in SAP 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')
)
and
chain_cnt > 0
order by chain_cnt desc
;
spool off;
Listing 5-8. The chain1.sql script to identify
migrated/chained rows in SAP tables that contain RAW or LONG RAW
columns.
|
