Proof of Concept: There are two circumstances when this can occur, the data for a row in a table may be too large to fit into a single data block. This can be caused by either row chaining or row migration.
Row Chaining: Occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Row Migration: Occurs when a row that originally fitted into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid of a migrated row does not change.
When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.
o INSERT and UPDATE statements that cause migration and chaining perform poorly, because they perform additional processing.
o SELECTs that use an index to select migrated or chained rows must perform additional I/Os.
Detection: Migrated and chained rows in a table or cluster can be identified by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows,
1. Execute script UTLCHAIN.SQL. i.e @?/rdbms/admin/utlchain.sql --> to crated the table chained_Rows which will contain the data after analyze.
2.ANALYZE TABLE scott.emp LIST CHAINED ROWS; --> Analyze table
3.SELECT * FROM chained_rows; --> it will populate data in chained_rows regarding row chaining and migrations
4.You can also detect migrated and chained rows by checking the ‘table fetch continued row’ statistic in the v$sysstat view.
SQL> SELECT name, value FROM v$sysstat WHERE name = ‘table fetch continued row’;
NAME VALUE
—————————————————————- ———
table fetch continued row 308
Although migration and chaining are two different things, internally they are represented by Oracle as one. When detecting migration and chaining of rows you should analyze carrefully what you are dealing with.
Resolving:
o In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger blocksize.
e.g.: You have a database with a 2K block size. Different tables have multiple large varchar columns with an average row length of more than 2K. Then this means that you will have a lot of chained rows because you block size is too small. Rebuilding the database with a larger block size can give you a significant performance benefit.
o Migration is caused by PCTFREE being set too low, there is not enough room in avoid migration, all tables that are updated should have their PCTFREE set so that there is enough space within the block for updates.
You need to increase PCTFREE to avoid migrated rows. If you leave more free space available in the block for updates, then the row will have more room to grow.
SQL Script to eliminate row migration :
— Get the name of the table with migrated rows:
ACCEPT table_name PROMPT ‘Enter the name of the table with migrated rows: ‘
— Clean up from last execution
set echo off
DROP TABLE migrated_rows;
DROP TABLE chained_rows;
— Create the CHAINED_ROWS table
@…/rdbms/admin/utlchain.sql
set echo on
spool fix_mig
— List the chained and migrated rows
ANALYZE TABLE &table_name LIST CHAINED ROWS;
— Copy the chained/migrated rows to another table
create table migrated_rows as
SELECT orig.*
FROM &table_name orig, chained_rows cr
WHERE orig.rowid = cr.head_rowid
AND cr.table_name = upper(‘&table_name’);
— Delete the chained/migrated rows from the original table
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows);
— Copy the chained/migrated rows back into the original table
INSERT INTO &table_name SELECT * FROM migrated_rows;
spool off
Tips
1. Analyze the table and check the chained count for that particular table
8671 Chain Count
analyze table tbl_tmp_transaction_details compute statistics
select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’
2. Increase the pctfree size to 30
alter table tbl_tmp_transaction_details pctfree 30
3. Regenerate Report (When rows get updated only we will have Chained rows)
tbl_report_generation_status
begin dbms_job.run(190); end;
4. Analyze the table and check the chained count for that particular table
0 Chain Count
analyze table tbl_tmp_transaction_details compute statistics
select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’
Note:
If we want to do the procedure to delete the chained rows from original table and insert the same again, then we need chained_rows table
To create chained rows we need to run the utlchain.sql from $ORACLE_HOME/rdbms
Find out the chained rows.
analyze table tbl_tmp_transaction_details list chained count;
The above command will move the chained rows to chained_row table
Based on the rowid in chained_row table we can move those record to temp table and delete those chained rows from original table then insert the same again into original table.
select * from tbl_tmp_transaction_details where rowid=’AAAG8DAAGAAAGOKABD’:
Example:
SQL> Create table frag_tab(code number,x1 char(2000),x2 char(2000),
x3 char(2000),x4 char(2000)); 2
Table created.
SQL> Insert into frag_tab(code) values(1);
Insert into frag_tab(code) values(2);
Insert into frag_tab(code) values(3);
commit;
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL> update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=2;
update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=1;
update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=3;
commit;
1 row updated.
SQL>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL>
SQL> @?/rdbms/admin/utlchain.sql
Table created.
SQL> Analyze table frag_tab list chained rows;
Table analyzed.
SQL> select * from chained_rows;
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAA 03-MAY-12
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAB 03-MAY-12
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAC 03-MAY-12
SQL> Create table duptab as select * from frag_tab where 1=0;
Table created.
SQL> Insert into duptab select * from frag_tab
where rowid in(select head_rowid from chained_rows); 2
3 rows created.
SQL> delete frag_tab where rowid in(select head_rowid from chained_rows);
3 rows deleted.
SQL> Insert into frag_tab
as select * from duptab 2
3 /
as select * from duptab
*
ERROR at line 2:
ORA-00926: missing VALUES keyword
SQL> Insert into frag_tab
select * from duptab 2
3 /
3 rows created.
SQL> commit;
Commit complete.
SQL> truncate table chained_rows;
Table truncated.
SQL> Create tablespace bigtbs
datafile '/home/oracle/bigtbs.dbf' size 10m
blocksize 16k; 2 3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
SQL> ALter system set db_16k_cache_size=10m;
System altered.
SQL> Create tablespace bigtbs
datafile '/home/oracle/bigtbs.dbf' size 10m
blocksize 16k; 2 3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-01119: error in creating database file '/home/oracle/bigtbs.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
SQL> l
1 Create tablespace bigtbs
2 datafile '/home/oracle/bigtbs.dbf' size 10m
3* blocksize 16k
SQL> Create tablespace bigtbs
datafile '/tmp/bigtbs.dbf' size 10m
blocksize 16k;
2 3
Tablespace created.
SQL> Alter table frag_tab move tablespace bigtbs;
Table altered.
SQL> Analyze table frag_tab list chained rows;
Table analyzed.
SQL> select * from chained_rows
2 /
no rows selected
SQL>
Row Chaining: Occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Row Migration: Occurs when a row that originally fitted into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid of a migrated row does not change.
When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.
o INSERT and UPDATE statements that cause migration and chaining perform poorly, because they perform additional processing.
o SELECTs that use an index to select migrated or chained rows must perform additional I/Os.
Detection: Migrated and chained rows in a table or cluster can be identified by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows,
1. Execute script UTLCHAIN.SQL. i.e @?/rdbms/admin/utlchain.sql --> to crated the table chained_Rows which will contain the data after analyze.
2.ANALYZE TABLE scott.emp LIST CHAINED ROWS; --> Analyze table
3.SELECT * FROM chained_rows; --> it will populate data in chained_rows regarding row chaining and migrations
4.You can also detect migrated and chained rows by checking the ‘table fetch continued row’ statistic in the v$sysstat view.
SQL> SELECT name, value FROM v$sysstat WHERE name = ‘table fetch continued row’;
NAME VALUE
—————————————————————- ———
table fetch continued row 308
Although migration and chaining are two different things, internally they are represented by Oracle as one. When detecting migration and chaining of rows you should analyze carrefully what you are dealing with.
Resolving:
o In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger blocksize.
e.g.: You have a database with a 2K block size. Different tables have multiple large varchar columns with an average row length of more than 2K. Then this means that you will have a lot of chained rows because you block size is too small. Rebuilding the database with a larger block size can give you a significant performance benefit.
o Migration is caused by PCTFREE being set too low, there is not enough room in avoid migration, all tables that are updated should have their PCTFREE set so that there is enough space within the block for updates.
You need to increase PCTFREE to avoid migrated rows. If you leave more free space available in the block for updates, then the row will have more room to grow.
SQL Script to eliminate row migration :
— Get the name of the table with migrated rows:
ACCEPT table_name PROMPT ‘Enter the name of the table with migrated rows: ‘
— Clean up from last execution
set echo off
DROP TABLE migrated_rows;
DROP TABLE chained_rows;
— Create the CHAINED_ROWS table
@…/rdbms/admin/utlchain.sql
set echo on
spool fix_mig
— List the chained and migrated rows
ANALYZE TABLE &table_name LIST CHAINED ROWS;
— Copy the chained/migrated rows to another table
create table migrated_rows as
SELECT orig.*
FROM &table_name orig, chained_rows cr
WHERE orig.rowid = cr.head_rowid
AND cr.table_name = upper(‘&table_name’);
— Delete the chained/migrated rows from the original table
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows);
— Copy the chained/migrated rows back into the original table
INSERT INTO &table_name SELECT * FROM migrated_rows;
spool off
Tips
1. Analyze the table and check the chained count for that particular table
8671 Chain Count
analyze table tbl_tmp_transaction_details compute statistics
select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’
2. Increase the pctfree size to 30
alter table tbl_tmp_transaction_details pctfree 30
3. Regenerate Report (When rows get updated only we will have Chained rows)
tbl_report_generation_status
begin dbms_job.run(190); end;
4. Analyze the table and check the chained count for that particular table
0 Chain Count
analyze table tbl_tmp_transaction_details compute statistics
select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’
Note:
If we want to do the procedure to delete the chained rows from original table and insert the same again, then we need chained_rows table
To create chained rows we need to run the utlchain.sql from $ORACLE_HOME/rdbms
Find out the chained rows.
analyze table tbl_tmp_transaction_details list chained count;
The above command will move the chained rows to chained_row table
Based on the rowid in chained_row table we can move those record to temp table and delete those chained rows from original table then insert the same again into original table.
select * from tbl_tmp_transaction_details where rowid=’AAAG8DAAGAAAGOKABD’:
Example:
SQL> Create table frag_tab(code number,x1 char(2000),x2 char(2000),
x3 char(2000),x4 char(2000)); 2
Table created.
SQL> Insert into frag_tab(code) values(1);
Insert into frag_tab(code) values(2);
Insert into frag_tab(code) values(3);
commit;
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL> update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=2;
update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=1;
update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=3;
commit;
1 row updated.
SQL>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL>
SQL> @?/rdbms/admin/utlchain.sql
Table created.
SQL> Analyze table frag_tab list chained rows;
Table analyzed.
SQL> select * from chained_rows;
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAA 03-MAY-12
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAB 03-MAY-12
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS FRAG_TAB
N/A AAASOtAABAAAU9ZAAC 03-MAY-12
SQL> Create table duptab as select * from frag_tab where 1=0;
Table created.
SQL> Insert into duptab select * from frag_tab
where rowid in(select head_rowid from chained_rows); 2
3 rows created.
SQL> delete frag_tab where rowid in(select head_rowid from chained_rows);
3 rows deleted.
SQL> Insert into frag_tab
as select * from duptab 2
3 /
as select * from duptab
*
ERROR at line 2:
ORA-00926: missing VALUES keyword
SQL> Insert into frag_tab
select * from duptab 2
3 /
3 rows created.
SQL> commit;
Commit complete.
SQL> truncate table chained_rows;
Table truncated.
SQL> Create tablespace bigtbs
datafile '/home/oracle/bigtbs.dbf' size 10m
blocksize 16k; 2 3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
SQL> ALter system set db_16k_cache_size=10m;
System altered.
SQL> Create tablespace bigtbs
datafile '/home/oracle/bigtbs.dbf' size 10m
blocksize 16k; 2 3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-01119: error in creating database file '/home/oracle/bigtbs.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
SQL> l
1 Create tablespace bigtbs
2 datafile '/home/oracle/bigtbs.dbf' size 10m
3* blocksize 16k
SQL> Create tablespace bigtbs
datafile '/tmp/bigtbs.dbf' size 10m
blocksize 16k;
2 3
Tablespace created.
SQL> Alter table frag_tab move tablespace bigtbs;
Table altered.
SQL> Analyze table frag_tab list chained rows;
Table analyzed.
SQL> select * from chained_rows
2 /
no rows selected
SQL>
No comments:
Post a Comment