BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Identifying Oracle Tables with Chained Rows

Oracle Tips by Burleson Consulting

The Remote DBA should always monitor and fix row chaining whenever feasible. However, chained rows will always exist in cases where row length exceeds the database block size. The identification of these tables that do have chained rows is important because of their use of RAW and LONG RAW data columns.

The excessive I/O caused by chained rows can degrade an entire database. To illustrate this problem, consider the following example. An application initially loads data rows that have many VARCHAR columns into 8K blocks. Because the VARCHAR columns are unpopulated, they only consume 80 bytes, and we can fit 90 80-byte rows onto our 8K block size, reserving 10 percent of the block for growth by setting pctfree=10, as shown in Figure 10-15.

Figure 10-93: Small unexpanded rows in a datablock

Several weeks after the initial load of these rows, an update job is run that expands the VARCHAR columns from 4 bytes each to 900 bytes each. At update time, Oracle checks to see if there is room for the row to expand on the data block. Since the pctfree=10 only reserved 800 bytes, Oracle must chain the row. Oracle retrieves the next data block, only to find that it also does not have room to accept the expanded row. This process continues until Oracle abandons using the freelists, and raises the high-water mark for the table and places the expanded row onto a fresh 8K block. In Figure 10-16, we can see that this causes a huge amount of I/O, both at update time and for subsequent retrieval operations. In practice, Oracle is intelligent enough to see that subsequent blocks will not have room for the expanded rows. After a few tries to chain onto subsequent blocks, Oracle gives up and raises the high-water mark to get a fresh block for the chained row.

Figure 10-94: Excessive row chaining with Oracle rows

So, what could we have done to fix this huge mess? Because we know that each expanded row consumes 900 bytes, we can adjust pctfree to only allow nine rows on each data block instead of the original 90 rows per block. This way, when the rows expand, there will be room on the data block without any rows chaining. Basically, we want Oracle to unlink the data block from the freelist after nine 80-byte rows (720 bytes) are added to the block, and about 7,500 bytes of free space remain. In Figure 10-17, setting pctfree=92 will make the block unlink from the freelist when it is more than 8 percent full.

Figure 10-95: Storing rows with room for expansion

NOTE: Please remember that when you do these calculations, a rough approximation is best. You cannot be exact because Oracle reserves space on each block for the block header and footer.

While this may look like space is being wasted on the block, what we are really doing is making room for the later updates. Now, when the subsequent rows are expanded, there is enough room for each row to expand on the original block, as shown Figure 10-18.

Figure 10-96: A data block after row expansion

Remember, chained rows are bad because they cause excessive I/O, and there are only two causes for chained rows:

  •  pctfree is set too low to accommodate row expansion.

  • The row length exceeds the database block size.

Identifying Chained Rows

The following script can be run to quickly identify tables in your database that contain 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 Remote DBA_tables data dictionary view. Also note that this script does not include tables that contain RAW or LONG column datatypes, since such columns cause long rows that commonly span database blocks. Later in this chapter we will examine STATSPACK scripts that can be used to track chained rows over time.

chained_rows.sql

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 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 Remote DBA_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from Remote DBA_tab_columns
   where
 data_type in ('RAW','LONG RAW')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

A properly tuned database should not have any row chaining, because the Remote DBA has set pctfree high enough to accommodate row expansion. Hence, the report shown here may be used as a database integrity check. Any excessive chained rows should be immediately investigated.

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

 

We can also extend the report to show tables that have long rows. Because of the large row length of some tables with RAW or BLOB columns, you will see chained rows because the row length will exceed the db_block_size, forcing the huge rows to chain onto many blocks.

chained_rows.sql

set heading off;
select 'Tables with 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 Remote DBA_tables
where
owner not in ('SYS','SYSTEM')
and
table_name in
 (select table_name from Remote DBA_tab_columns
   where
 data_type in ('RAW','LONG RAW')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

Next is an example of the output from this report. Note that many tables have an average row length greater than 4K. If your database supports a large block size, you can remove these chained rows by rebuilding the database with an 8K or 16K block size.

Owner     Table     PCTFREE PCTUSED avg row         Rows       Chains  Pct
--------- --------- ------- ------- ------- ------------ ------------ ----
SAPR3     KOCLU          40      60   8,981      597,125      472,724  .79
SAPR3     CDCLSP         40      60     809      712,810      328,989  .46
SAPR3     VBFCL          40      60   5,398      340,917      285,930  .84
SAPR3     EDIDOM         40      60   6,211      158,426      114,859  .73
SAPR3     D01XX          10      40   5,129       76,635       43,791  .57
SAPR3     TSTIJP         40      60   6,559       44,596       22,298  .50
SAPR3     T51GHKU        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

Again, we must remember that chained rows cause excessive I/O because multiple blocks must be read to access the data. When chained rows are found, the chains can be repaired by reorganizing the tables and resetting pctfree to a lower value. Now that we understand how to identify row chaining, letís look at techniques for identifying sparse tables.

Identifying Tables with Long Rows

With the introduction of the RAW and LONG RAW datatypes, many Oracle tables have table row lengths that exceed the block size. Of course, these long rows will always chain onto several data blocks, and there is nothing that the Remote DBA can do about this chaining except to redefine the whole database with larger block sizes. However, RAW and LONG RAW datatypes can often be redefined in Oracle8, 8i or 9i as BLOB, CLOB, or NCLOB datatypes. When using these datatypes, Oracle will automatically move Large Objects (LOBs) into offline storage when the column length becomes excessive (greater then 4,000 bytes), thereby preventing chained rows.

Remember to use caution here. If a LOB storage clause is not specified and the LOB is too large then the LOB and its LOB index are collocated with the base table which will cause performance problems.

However, the Remote DBA still needs to know which tables in their database contain these large rows. The listing here shows an extract from an Oracle database showing all tables where the average row length is greater than one-fourth of the block size:

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name = 'db_block_size';

select
   table_name,
   tablespace_name,
   avg_row_len
from
   Remote DBA_tables
where
avg_row_len > &blksz/4
order by
   avg_row_len desc
;

Here is a sample listing. Here we see all of the tables that have long row lengths.

TABLE_NAME                     TABLESPACE_NAME                AVG_ROW_LEN
------------------------------ ------------------------------ -----------
D010L                          PSAPLOADD                            15,775
TST03                          PDONPROTD                            10,041
D010Y                          PDONLOADD                             7,913
SFDG                           PDONDOCUD                             7,819
D010Q                          PDONLOADD                             3,732
SNAP                           PDONBTABD                             3,250
MONI                           PDONSTABD                             2,851
MCSI                           PDONSTABD                             2,781
EUDB                           PDONSTABD                             2,684
RFDT                           PDONBTABD                             2,673
DSYO2                          PDONCLUD                              2,656
SFHOA                          PDONSTABD                             2,393
DSYO1                          PDONCLUD                              2,072
SOC3                           PDONBTABD                             2,018

For example, consider the Oracle table that has an average of 1,700 bytes per row, stored on a 4K block size. For the sake of a simple example, letís say that 4K = 4,000, even though we know that it is really 4,096 bytes. After two rows are added to the block, 3,400 bytes are consumed, and 600 bytes remain in the block. If pctfree=10, the block must reach 3,600 bytes to be removed from the freelist, and the block will remain on the freelist even though it cannot hold another entire row. The third row will not chain, and Oracle will grab another free block from the freelist. This is illustrated in Figure 10-19.

Figure 10-97: A data block on the freelist without room to accept a row

Now that we see how to monitor the chaining for our tables, letís revisit the sparse table concept and see how to detect sparse tables.

Identifying Sparse Tables

Sparse tables generally occur when a table is defined with multiple freelists, the table has heavy insert and delete activity, and the deletes are not parallelized. For example, a table with 20 freelists that has 20 concurrent insert processes is purged by a single process, causing all of the free blocks to go to only one of the 20 freelists. This causes the table to extend, even though it may be largely empty. Extension occurs because each freelist is unaware of the contents of other freelists.

A sparse table can usually be detected by selecting tables whose actual size (number of rows times average row length) is greater than the size of the next extent for the table. Of course, we must set the number of freelists to the number of simultaneous insert or update operations, so we cannot reduce the number of freelists without introducing segment header contention.

Identifying Chained Rows, Identifying Tables with Long Rows, Identifying Sparse Tables

(avg_row_len) in the data dictionary view and the number of rows (num_rows) with a weekly table analyze (i.e., analyze table xxx estimate statistics). The query here selects tables that contain multiple freelists with more than one extent where there is excessive free space:

sparse.sql

column c1  heading "Tablespace";
column c2  heading "Owner";
column c3  heading "Table";
column c4  heading "Bytes M" format 9,999;
column c5  heading "Extents" format 999;
column c7  heading "Empty M" format 9,999;
column c6  heading "Blocks M" format 9,999;
column c8  heading "NEXT M" format 999;
column c9  heading "Row space M" format 9,999;
column c10  heading "Pct Full" format .99;
column db_block_size new_value blksz noprint
select value db_block_size from v$parameter where name = 'db_block_size';

select
        substr(dt.table_name,1,10) c3,
        ds.extents c5,
        ds.bytes/1048576 c4,
        dt.next_extent/1048576 c8,
       (dt.empty_blocks*4096)/1048576 c7,
       (avg_row_len*num_rows)/1048576 c9,
       (ds.blocks*&blksize)/1048576 c6,
       (avg_row_len*num_rows)/(ds.blocks*&blksize) c10
from    sys.Remote DBA_segments ds,
        sys.Remote DBA_tables dt
where   ds.tablespace_name = dt.tablespace_name
and   ds.owner = dt.owner
and   ds.segment_name = dt.table_name
and dt.freelists > 1
and ds.extents > 1
and dt.owner not in ('SYS','SYSTEM')
and (avg_row_len*num_rows)/1048576 > 50
and ds.bytes/1048576 > 20
order by c10;

Next is an example of the output from this script. When we multiply the number of rows in the table by the average row length, we approximate the actual consumed size of the data within the table. We then compare this value with the actual number of allocated bytes in the table. The idea is that a sparse table will have far more allocated space than consumed space.

Table      Extents Bytes M NEXT M Empty M Row space M Blocks M Pct Full
---------- ------- ------- ------ ------- ----------- -------- --------
TST99           65   1,241     20      14         118    1,241      .10
LIKP             3     148     49      24          76      148      .52
VBRK             2     124      4       0          69      124      .56
STXL            35   1,775     40       7       1,021    1,775      .57
VBAK             5     234     49       0         136      234      .58
KOCLU           27   1,889     49      27       1,144    1,889      .61
VBUP             2     866     49       0         570      866      .66
VBUK             2     147     28       0         103      147      .70
VBAP            46   4,314     50       0       3,034    4,314      .70
NAST             3     137     10       2          97      137      .71
VBPA             5     582     32       0         426      582      .73
LIPS             7   2,350     49       0       1,735    2,350      .74
VBRP            45   2,675     49       0       2,029    2,675      .76
WFPRC           30     123     10       7          95      123      .77
VLPMA           16     575     25      23         444      575      .77
EDIDOC          18     432     20      13         337      432      .78
VRPMA           24     700     20       7         549      700      .78
VBEP             4   2,134     49      49       1,698    2,134      .80

As we stated earlier, sparse tables are caused by an imbalance in multiple freelists, and are evidenced by tables that are continuing to extend although they are not very full. In the preceding example, we might take a closer look at the KOCLU, VBRP and TST99 tables because they have a high number of extents while they are largely empty.

Next, letís take a look at a very important concept in object tuning. As we have repeatedly noted, anything that can be done to reduce I/O will improve performance, and resequencing table rows can result in dramatic reductions in expensive disk I/O.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter