Thursday 21 May 2015

What is Row chaining and how can you find it? and how you will resolve the issue ?

When you are inserting record if a row is not fit into the single block  it will chain across multiple blocks leaving  a pointer between these  blocks.

analyze table owner.tablename;

select table_name,chain_cnt from dba_tables where table_name=’tablename';

Solution:
create a table with bigger the block size
1) Create tablespace tbs1 data file ‘/u01/oradata/test/data01.dbf’ size 100m blocksize 16k;
2) alter table tablename move to tbs1;
Here tbs1 is the tablespace name with larger block size and  before creating tablespace it is assumed that you have created a db buffer cache for it.

What is row migration? When does it occur? Where can you find this information?
Row migration happens when update occurs at one column and the row is not adequate to fit in the block then the entire row will be moved to the new block.

select table_name,chain_cnt from dba_tables where table_name=’tablename';
Solution:
set pct_free storage parameter for table to adequate.;

Do you know about statistics , what is the use of it? What kind of statistics exists in database?
Statistics is a collection information about data or database
There are different types of statistics that oracle maintains-
1)System-Statistics: statistics about the hardware like cpu speed,I/O speed,read time write time etc : select * from aux_stats$
2)Object statistics : For a table oracle collects the information about no.of rows,no.of blocks,avg row length etc.We can view
SQL>select table_name,num_rows,blocks,avg_row_len from dba_tables
for index oracle collect statistics on index column about no.of rows,no.of root blocks,no.of branch blocks,no.of leaf blocks,no.of distinct values etc.

No comments:

Post a Comment