ROW CHAINING AND ROW MIGRATION

HOW TABLE DATA IS STORED

• When a table is created, a segment is created to hold its data
• A tablespace contains a collection of segments.
• A row is ultimately stored in a database block in the form of a row piece.

ROW CHAINING:

• Normally, we encounter row chaining when the size of the row is larger than the size of the database block used for storing it. 
• In this situation, the row is split across more than one database block.
• When this row needs to be accessed, more than one database blocks are traversed, resulting in greater I/O.
• To avoid row chaining we can create larger size data block table space.

Non-default data block tablespace creation:

Before we start we need to alter an initialization parameter. Assuming the default block size is 8096 KB.

SQL>ALTER SYSTEM SET DB_16K_CACHE_SIZE=16M SCOPE=BOTH;

We can understand row chaining by following steps:


STEP 1

Create a new table ROW_CHAIN

 SQL> CREATE TABLE SCOTT.ROWS_CHAIN
(
Id number not null primary key,
  Field1 char(2000) default 'A' not null,
  Field2 char(2000) default 'B' not null,
  Field3 char(2000) default 'C' not null,
  Field4 char(2000) default 'D' not null,

);

STEP 2

INSERT VALUES IN TABLE

INSERT INTO SCOTT.ROW_CHAIN (ID) SELECT ROWNUM FROM SYS.DBA_OBJECTS WHERE ROWNUM<101;

STEP 3

GATHER STATISTICS FOR THE TABLE


SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 4

CHECK IF ROW CHAINING IS THERE


SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

 CHAIN_CNT
----------

       100

STEP 5

CREATE A TABLESPACE WITH DIFFERENT BLOCK SIZE

SQL> CREATE TABLESPACE TS_16K BLOCKSIZE 16K DATAFILE 'TS_16K.DBF'  SIZE 30M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

STEP 6

MOVE TABLE ROW_CHAIN IN NEWLY CREATED TABLESPACE

SQL> ALTER TABLE SCOTT.ROW_CHAIN MOVE TABLESPACE TS_16K;

STEP 7

REBUILD INDEX OF TABLE ROW_CHAIN

SQL> ALTER INDEX SCOTT.PK_ROW_CHAIN REBUILD;

STEP 8

NOW REFRESH THE TABLE STATISTICS

SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 9

NOW CHECK FOR THE ROW CHAINING

SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

 CHAIN_CNT

----------

         0

TO CHECK ROW CHAINING IN ANY TABLE

SQL> SELECT owner, table_name, NVL(chain_cnt,0) "Chained Rows"  FROM all_tables WHERE owner NOT IN ('SYS','SYSTEM') AND NVL(chain_cnt,0) > ORDER BY owner, table_name;

ROW MIGRATION

In row migration, Oracle Database moves the entire row to a new data block, because in block there is not enough size to fit in the block after updation the entire row will migrated to another block and and the original row piece of a migrated row contains a pointer of forwarding address to the new block containing the migrated row. The rowid of migrated row does not change.

Row migration is depends on the pct_used and pct_free of oracle block.

When varchar2 datatype's value will be updated and if there is insufficient space is available in pct_free then row chaining will takes place.

Avoid Row Chaining

To avoid row migration we can move table in another tablespace.

Managing Oracle database instance

Starting Up an Oracle Database Instance

A normal database operation means that an instance is started and database is mounted and open.

Oracle database instance has 3 phase of start up

SQL> startup nomount 

• An instance is typically started only in nomount mode during database creation, during re creation of control file, or during certain backup and recovery scenarios.
• Starting an instance includes the following tasks:
• Searching ORACLE_HOME/dbs for a parameter file of a particular name the sequence.
• search for spfile<SID>.ora
• If spfile<SID>.ora not found, search for spfile.ora
• If spfile.ora is not found, search for init<SID>.ora
• Allocating the SGA
• Starting the background processes
• Opening the alert_<SID>.log file and trace files.

This command starts the instance and the database is not mounted.

SQL> startup mount

• Associating a database with a previously started instance.
• Locating and opening all control files specified in the parameter file.
• Reading control files to obtain the names and statuses of the data files and online redo log files.
• However, no checks are performed to verify the existence of the data files and online redo log files at this time.

We can open database in mount mode to perform certain maintenance operations.

• Renaming datafiles
• Enabling and disabling online redo log file archive operations
• Performing full database recovery

SQL> startup

Opening database includes following tasks:

• Opening the data files
• Opening the online redo log files

If any of above file is not present when you attempt to open the database, it will returns an error.

Sequence of command in case we open it in mount or no mount state.

SQL> startup nomount
SQL> alter database mount
SQL> alter database open

Using srvctl starting database

$srvctl start database -d orcl -o mount

During this final stage, the oracle server verifies that all data files and online redo log files can be opened, and checks the consistency of the database. If requires SMON background process will perform Instance Recovery.

Note:when the database is enabled with ORACLE RESTART, the srvctl utility can be used to start oracle database instance, the advantage is it can also start all required dependent resources such as asm instance, asm disk group and listener.  srvctl utility is located in both the $ORACLE_HOME/bin directory for grid infrastructure and for the oracle database software.

SHUTTING DOWN ORACLE DATABASE INSTANCE

There is four modes of shutting down oracle database instace

SQL> shutdown abort;

• Current SQL statements being processed by the Oracle server are immediately terminated.
• The Oracle server does not wait for users who are currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or mounted.
• The next startup requires instance recovery, which occurs automatically.

SQL> shutdown Immediate;

• Current SQL statements being processed by the oracle database are not completed.
• the oracle server does not wait for the users who are currently connected to the database to disconnect.
• The oracle server rolls back active transactions and disconnects all connected users.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

NOTE: IMMEDIATE is the default shutdown mode when using OEM.

SQL> shutdown transactional;

• It will allow existing transactions to finish,(It will wait till user commit the transaction or rollback the pending transaction) but not starting new transactions.
• No client can start a new transaction on this particular instance.
• When all transactions have been completed, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SQL> shutdown;(normal)

• It will waits for all sessions to disconnect.
• No new connections can be made.
• Database and redo buffers are written to disk.
• Background processes are terminated and the SGA is removed from memory.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

$srvctl stop database -d orcl -o abort

If we think about time that is taken to perform shutdown abort is fastest and normal is the slowest.



Thank You,