IDENTIFY AND SOLVE THE CHAINED ROWS:
ROW CHAINING:
It occurs due to huge inserts where a particular row is too large to fit in the empty block. In this case, the oracle stores the data of the row in a chain
of two or more data blocks. So more I/O is needed to fetch a particular block.
It usually occurs with large rows that contain a large object[LOB] datatypes. LOB supports upto 2GB data.
ROW MIGRATION:
It occurs due to updations. An update increases the amount of data in a row so that the row no longer fits in its datablock, then
the oracle tries to find another block with enough free space to hold the entire row.
Solutions:
1. Move the table from one tablespace to another tablespace which is bigger blocksize.
Alter table test move tablespace users;
2. Take the export of the table and drop the existing table and import the table.
Steps:
SQL> conn sys/sys@info as sysdba
SQL> alter system set db_4k_cache_size=1m scope=both;
System altered.
SQL> create tablespace info datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\INFO\INFO01.DBF'
2 size 20m blocksize 4k;
Tablespace created.
SQL> create user robo identified by robo
2 default tablespace info
3 quota unlimited on info;
User created.
SQL> grant connect,resource to robo;
Grant succeeded.
SQL> conn robo/robo
Connected.
SQL> create table test as select * from all_objects;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
40686
SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';
AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
TEST
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';
AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
98 0 TEST
SQL> alter table test add address varchar2(1000);
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
ADDRESS VARCHAR2(1000)
SQL> update test set address='asasasaasasasasasasasasasasasaasasasasasasas';
40686 rows updated.
SQL> commit;
Commit complete.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';
AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
145 10310 TEST
SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>exp 'sys/sys as sysdba' file=abc.dmp tables=robo.test direct=y
Export: Release 10.2.0.1.0 - Production on Mon Aug 3 06:56:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
Current user changed to ROBO
. . exporting table TEST 40686 rows exported
Export terminated successfully without warnings.
C:\>exit
SQL> show user
USER is "ROBO"
SQL> drop table test purge;
Table dropped.
SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>imp 'sys/sys as sysdba' file=abc.dmp tables=test fromuser=robo touser=robo
Import: Release 10.2.0.1.0 - Production on Mon Aug 3 06:59:19 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ROBO's objects into ROBO
. . importing table "TEST" 40686 rows imported
Import terminated successfully without warnings.
C:\>exit
SQL> select count(*) from test;
COUNT(*)
----------
40686
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';
AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
143 0 TEST
Note:
The above output displays the Chained Rows is 0...Now it reduces the I/O while fetching the rows....
Thanks !!
ReplyDelete