USNING ORACLE BLOCKS EFFICIENTLY
Row Chaining
- It happens due to inserts.
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;
