Hands-On 17 (Detecting ROW Migration and Chaining)

As a DBA, you are responsible for detecting row migration and chaining. The more migration and chaining you have, cause more performance problems for application software. You should identify them and if there are many of them, organize the table. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a table

Inserting lots of records

Generating lots of Migration and Chaining

Analyzing a table

Using the USER_TABLES view

Displaying table statistics

The NUM_ROWS column

The BLOCKS column

The CHAIN_CNT column

Moving or relocating a table

Grant a system privilege to a user

Checking an index table

Using the USER_INDEXES view

Rebuilding or relocating an index table

Analyzing an index table

Using the INDEX_STATS view

Checking the DELETED ROWS RATIO value

The lf_rows column

The del_lf_fows column

Dropping a table

Dropping an index table

Dropping a tablespace

Revoking a system privilege from a user

Commands:

CREATE TABLE CONSTRAINT PRIMARY KEY

ANALYZE TABLE COMPUTE STATISTICS

CREATE TABLESPACE DATAFILE

GRANT CREATE TABLESPACE TO

GRANT DROP TABLESPACE TO

ALTER TABLE MOVE TABLESPACE

ALTER INDEX REBUILD TABLESPACE

ANALYZE VALIDATE STRUCTURE

DROP TABLE

DROP TABLESPACE

REVOKE CREATE TABLESPACE FROM

-- Hands-On 17 (Detecting ROW Migration and Chaining)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a30
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to Analyze
-- a table to detect row migration and fix the
-- problem.

-- Now, connect to SQLPlus as the iself user.

pause


CONNECT iself/schooling

pause

CLEAR SCR
-- First, let's create a table and name it TEST_MIGRATE.

pause


CREATE TABLE test_migrate
(col1 NUMBER,
col2 VARCHAR2(1000),
CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
)

/

pause

CLEAR SCR
-- Write a procedure to insert a few records.

-- This procedure will insert 1000 records into 
-- the TEST_MIGRATE table.

pause

BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO test_migrate VALUES (i,'A');
COMMIT;
END LOOP;
END;
/

pause

CLEAR SCR
-- Query the TEST_MIGRATE table information using 
-- the USER_TABLES view.

pause


SELECT table_name, num_rows, blocks, chain_cnt
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

-- Check the num_rows, blocks, and chain_cnt columns.
-- Notice that they are null.

pause

CLEAR SCR
-- Analyze the TEST_MIGRATE table with the compute 
-- statistics option.

pause


ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

pause

CLEAR SCR
-- Now, query the USER_TABLES directory view again.

pause


SELECT table_name, num_rows, blocks, chain_cnt
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

-- Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns again.
-- The analyze statement computed statistical information
-- for the TEST_MIGRATE table. Notice that we have no problems
-- in the row migration or chaining since the CHAIN_CNT 
-- column is zero.

pause


CLEAR SCR
-- Now, let's write a procedure to create 1000 of 
-- row migrations and chaining.

pause


DECLARE
v_data CHAR(1000) := 'This is very very long text ...';

BEGIN
UPDATE test_migrate
SET col2 = v_data;
COMMIT;
END;
/

-- Can you tell why this procedure creates so many
-- row chaining or migration?

-- Notice that the V_DATA's datatype is CHAR and was 
-- sized to 1000 characters long. The migration will occur
-- since the record cannot be fitted in the block.

pause

CLEAR SCR
-- Analyze the TEST_MIGRATE table with the compute 
-- statistics option again.

pause


ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

pause

CLEAR SCR
-- Query the TEST_MIGRATE table information again.

pause


SELECT table_name, num_rows, blocks, chain_cnt
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

-- Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns.
-- Notice that you have row chaining and migration 
-- problems. 

-- The problem needs to be fixed.

pause

CLEAR SCR
-- Now, let's create a tablespace and move or relocate
-- the table to an alternative destination.

pause


CREATE TABLESPACE data2move DATAFILE SIZE 100K
/

-- Notice that the ISELF user does not have sufficient
-- privileges to create or drop tablespaces.

pause

CLEAR SCR
-- Connect as the SYSTEM/MANAGER user and 
-- grant the create and drop privileges to the ISELF user
-- and then connect to SQLPlus as the ISELF user.

pause

CONNECT system/manager AS SYSDBA

GRANT CREATE TABLESPACE TO iself
/

GRANT DROP TABLESPACE TO iself
/

CONNECT iself/schooling

pause

CLEAR SCR
-- Now, lets create a tablespace again.

pause


CREATE TABLESPACE data2move DATAFILE SIZE 100K
/
-- Here, the tablespace was created.

pause

CLEAR SCR
-- Check to see where the table is located.

pause


SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

pause

CLEAR SCR
-- Relocate or move the table from ISELF_DATA
-- to the DATA2MOVE tablespace.

pause


ALTER TABLE test_migrate MOVE TABLESPACE data2move
/

pause

CLEAR SCR
-- Check to see where the table is moved.

pause


SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

-- The location was changed. 

pause


CLEAR SCR
-- Check the TEST_MIGRATE index table using
-- the USER_INDEXES view.

pause


SELECT index_name, tablespace_name 
FROM user_indexes
WHERE table_name = 'TEST_MIGRATE'
/

pause

CLEAR SCR
-- Create the INDEX2MOVE tablespace and relocate 
-- the index table to it.

pause


CREATE TABLESPACE index2move DATAFILE SIZE 100K
/

-- The tablespace was created.

pause

CLEAR SCR
-- Rebuild or relocate the index table.

pause


ALTER INDEX pk_test_migrate REBUILD TABLESPACE index2move
/

-- Notice that the REBUILD option not only can recreate 
-- an index tables, but can also be used to move or 
-- relocate the index tables.

pause

CLEAR SCR
-- Analyze the TEST_MIGRATE table with the compute 
-- statistics option again.

pause


ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

pause

CLEAR SCR
-- Query the USER_TABLES view to display the TEST_MIGRATE
-- table information again.

pause


SELECT table_name, num_rows, blocks, chain_cnt
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/

-- Check the NUM_ROWS, BLOCKS, and CHAIN_CNT columns.
-- Notice that with the move clause not only you can
-- relocate the table, but also remove all row migrations
-- and chaining.

pause

CLEAR SCR
-- Delete most of the records.

-- We remove almost all the records except the record
-- number 1, 500, and 999, and keep only three records
-- in the table.

pause


DELETE FROM test_migrate
WHERE col1 NOT IN (1,500,999)
/

pause

CLEAR SCR
-- Now, analyze the TEST_MIGRATE index table with the
-- validate structure option.

pause

ANALYZE INDEX pk_test_migrate VALIDATE STRUCTURE
/

pause

CLEAR SCR
-- Query the index table statistics, INDEX_STATS view,
-- to display the name, number of leaf nodes, deleted leaf nodes,
-- and deleted rows ratio to see if the empty space exceeds 30%.

pause

SELECT name, lf_rows, del_lf_rows, del_lf_rows/lf_rows "Over 30%"
FROM index_stats
WHERE name = 'PK_TEST_MIGRATE'
/

-- The INDEX_STATS dictionary view shows that the number of index
-- entries in leaf nodes in the lf_rows column compared
-- to the number of deleted entries in the del_lf_fows column.

-- If the number of deleted entries is over 30 percent,
-- you should rebuild the index.

pause


CLEAR SCR
-- Now, drop the TEST_MIGRATE table, along with the DATA2MOVE
-- and INDEX2MOVe tablespaces.

pause


DROP TABLE test_migrate
/

DROP TABLESPACE data2move 
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/

DROP TABLESPACE index2move
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/ 

pause

CLEAR SCR
-- Also, connect to SQLPlus as the SYSTEM/MANAGER user and revoke
-- the CREATE TABLESPACE and DROP TABLESPACE from the ISELF user.

pause

CONNECT system/manager AS SYSDBA

REVOKE CREATE TABLESPACE FROM iself
/

REVOKE DROP TABLESPACE FROM iself
/

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause