USNING ORACLE BLOCKS EFFICIENTLY
Row Chaining
- It happens due to inserts.
- While inserting row in block what happens if your row size is bigger than the block size, at this time oracle will do row chaining.
- In row chaining oracle splits the row and insert it in many blocks.
- Due to row chaining application is going to be slow down.
Solution to avoid this draw back
o Normalization of table ( task of application people)
Row Migration
- Happens due to updations , because the PCT_FREE is properly set.
- If the free space is not enough for the row than oracle will allocate new block and allocate that row in new block , but pointer for the new block will be there in old block.
- Report will be slow if the row is scattered in many blocks
Solution to avoid this draw back
o Set proper PCT_FREE
How To Identify The Changed And Migrated Row?
There are 2 ways
[1] First Method
o Run query UTLCHAIN.SQL , This query is oracle supplied and stored at rdbms/admin folder
o To run this query connect as sys
- CONNECT Sys/Manager
- SQL> @:\oracle\oracle90\rdbms\admin\UTLCHAIN;
o Once you run this query table get created
- SQL> desc CHAINED_ROWS;
o Internally oracle doesn’t differentiate between chained and migrated row
[ 2] Method
o From DBA_TABLES;
- SQL> DESC DBA_TABLES;
- SQL> SELECT owner, table_name, chain_cnt, FROM DBA_TABLES WHERE OWNER=’
’;
We can use any of this two methods to identify between chained or migrated rows
How To Analyze The Tables ?
- SQL> ANALYZE TABLE dept COMPUTE STATISTICS ;
o Analyze table compute statistics is used by CBO.
o CBO is used for generating execution plan
o This command generates statistics and update it in dba_tables
Using UTLCHAIN utility, UTL_CHAIN table is generated.
- SQL> ANALYZE TABLE
. COMPUTE STATISTICS ;
It will populates CHAINED_ROW table with chained and migrated row.
- SQL>SELECT owner_name, table_name, head_rowid FROM CHAINED_ROWS;
Best Solution for all this drawbacks
o Export the table and import it back with modified storage clause.
High Water Mark
- It is the last access block in table.
- HWM moves forward only.
- Oracle never resets the HWM when data is deleted, however it resets it when data is Truncated
- When FTS (full table scan) is done, oracle scans up to HWM i.e. improves the performance
How to find HWM?
o For this do ANALYZE
- SQL> ANALYZE TABLE COMPUTE STATISTICS;
Difference between COMPUTE STATISTICS & ESTIMATE STATISTICS that Estimate statistics is only sampling , not actual statistics.
o So always use compute statistics
o Run this query
- SQL>COL TABLE_NAME format a10
- SQL>select table_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from dba_tables where owner = '&OWNER';
Columns For DBA_TABLES
-------------------------------------------------------
§ -- NUM_ROWS – number of rows
§ -- BLOCKS - Below HWM
§ -- EMPTY BLOCKS – Blocks empty Ahead HWM
§ -- AVG_SPACE - Avg Free space in Blocks below HWM in bytes
§ -- CHAIN_CNT - chain count
§ -- AVG_ROW_LEN – average row length in bytes
