Row chaining
& Row migration Oracle DB:
DENTIFY 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
===========================================================================================
High level water mark:
================
conn scott/tiger
create table as select * From sys.dba_objects;
select num_rows,avg_row_len,avg_space,blocks,empty_blocks
from user_tables where table_name='T_T';
analyze table t_t compute statistics;
select num_rows,avg_row_len,avg_space,blocks,empty_blocks
from user_tables where table_name='T_T';
51953 180 929 2076 33
SQL> select count(*),count(distinct(object_id))from
t_t;
COUNT(*) COUNT(DISTINCT(OBJECT_ID))
---------- --------------------------
103953
51953
here i m going to delete table more record that time HMW i
ll occur:
how to find out HMW :
=========
SQL> select
count(distinct(dbms_rowid.rowid_block_number(rowid))) from t_t;
COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
2086
SQL> alter table t_t row movement;
alter table t_t row movement
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table t_t rowmovement;
alter table t_t rowmovement
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
here used block only 2086 only other blocks are HWM
SQL> alter table t_t enable row movement;
Table altered.
SQL> alter table t_t shrink space;
Table altered.
SQL> select
count(distinct(dbms_rowid.rowid_block_number(rowid))) from t_t;
COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
1327
No comments:
Post a Comment