|
"If there were in
the world today any large number of people who desired their own
happiness more than they desired the unhappiness of others, we
could have paradise in a few years." Bertrand Russell (1872 -
1970) |
Read
first then play the video:
DBA-VIDEO -Detecting
ROW Migration and Chaining
Detecting ROW Migration and
Chaining
Introduction
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_rows 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
In this exercise you will
learn how to analyze a table to detect row migration and fix the
problem. Note that a "Chained Record" happens when a user updates
a record and the new value can not fit in the existing allocated
location. So, Oracle stores the value in a space that allocated for
them (PCT) and add a pointer to the row so that it knows where the
rest of the record is. This process calls row Chaining. Now, if there
was no space in the PCT area, then Oracle will take the whole
record and migrate it in a different block that has space. This
process calls row Migration.
Now, connect to SQLPlus as
the iself user.
SQL> CONNECT iself/schooling
Create a dummy table
First, let's create a table and name it TEST_MIGRATE.
SQL> CREATE TABLE test_migrate
(col1 NUMBER,
col2 VARCHAR2(1000),
CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
)
/
Populate a table with a
good data
Write a procedure to insert
a few records. This procedure will insert 1000 records into the
TEST_MIGRATE table.
SQL> BEGIN
SQL>
FOR i IN 1..1000 LOOP
SQL>
INSERT INTO test_migrate VALUES (i,'A');
SQL>
COMMIT;
SQL>
END LOOP;
SQL> END;
SQL> /
View a table statistics
Query the TEST_MIGRATE table information using the USER_TABLES
view.
SQL> 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.
Analyze a table
Analyze the TEST_MIGRATE table with the compute statistics option.
SQL> ANALYZE TABLE test_migrate COMPUTE
STATISTICS
/
Now, query the USER_TABLES
directory view again.
SQL> 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.
Populate a table with lots
of space problem
Now, let's write a
procedure to create 1000 of row migrations and chaining.
SQL> DECLARE
SQL>
v_data CHAR(1000) := 'This is very very long text ...';
SQL> BEGIN
SQL>
UPDATE test_migrate
SQL>
SET col2 = v_data;
SQL>
COMMIT;
SQL> END;
SQL> /
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.
Analyze the table
Analyze the TEST_MIGRATE
table with the compute statistics option again.
SQL> ANALYZE TABLE test_migrate COMPUTE
STATISTICS
/
View table statistics
Query the TEST_MIGRATE table information again.
SQL> 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.
Clear all Problems
Now, let's create a tablespace and move or relocate the table to
an alternative destination.
SQL> CREATE TABLESPACE data2move DATAFILE
SIZE 100K
/
Notice that the ISELF user does not have sufficient
privileges to create or drop tablespaces.
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.
SQL> CONNECT system/manager AS SYSDBA
SQL> GRANT CREATE TABLESPACE TO iself
/
SQL> GRANT DROP TABLESPACE TO iself
/
SQL> CONNECT iself/schooling
Now, lets create a tablespace again.
SQL> CREATE TABLESPACE data2move DATAFILE
SIZE 100K
/
Here, the tablespace was created.
Check to see where the table is located.
SQL> SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/
Relocate or move the table from ISELF_DATA to the DATA2MOVE
tablespace.
SQL> ALTER TABLE test_migrate MOVE
TABLESPACE data2move
/
Check to see where the table is moved.
SQL> SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/
Note that the location was changed.
Check the TEST_MIGRATE index table using the USER_INDEXES view.
SQL> SELECT index_name, tablespace_name
FROM user_indexes
WHERE table_name = 'TEST_MIGRATE'
/
Create the INDEX2MOVE tablespace and relocate the index table to it.
SQL> CREATE TABLESPACE index2move DATAFILE
SIZE 100K
/
The tablespace will be created.
Rebuild or relocate the index table.
SQL> 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.
Analyze the TEST_MIGRATE table with the compute statistics option
again.
SQL> ANALYZE TABLE test_migrate COMPUTE
STATISTICS
/
Query the USER_TABLES view
to display the TEST_MIGRATE table information again.
SQL> 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.
When should I rebuild
Delete most of the records.
Let us remove almost all the records except the record number 1, 500,
and 999, and keep only three records in the table.
SQL> DELETE FROM test_migrate
WHERE col1 NOT IN (1,500,999)
/
Now, analyze the TEST_MIGRATE index table with the validate structure
option.
SQL> ANALYZE INDEX pk_test_migrate
VALIDATE STRUCTURE
/
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%.
SQL> 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.
Now, drop the TEST_MIGRATE table, along with the DATA2MOVE and
INDEX2MOVe tablespaces.
SQL> DROP TABLE test_migrate
/
SQL> DROP TABLESPACE data2move
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/
SQL> DROP TABLESPACE index2move
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/
Also, connect to SQLPlus as the SYSTEM/MANAGER user and revoke the
CREATE TABLESPACE and DROP TABLESPACE from the ISELF user.
SQL> CONNECT system/manager AS SYSDBA
SQL> REVOKE CREATE TABLESPACE FROM iself
/
SQL> REVOKE DROP TABLESPACE FROM iself
/
|
"The foolish man
seeks happiness in the distance, the wise grows it under his
feet." James Oppenheim |
Questions:
Q: What is a ROW Migration?
Q: What is a Chained
record?
Q: What are the differences
between a row migration and chained record?
Q: How do you detect a row
migration and chaining?
Q: How do you analyze a
table?
Q: Describe the following
views.
USER_TABLES view
USER_INDEXES view
INDEX_STATS view
Q: What do the following
columns contain in the USER_TABLES view?
NUM_ROWS column
BLOCKS column
CHAIN_CNT column
Q: How do you move a table?
Q: How do you grant a
system privilege to a user?
Q: How do you reorganize an
index table?
Q: What does the DELETED
ROWS RATIO value show?
Q: What are the lf_rows and
del_lf_rows columns in the INDEX_STAT table?
Q: How do you revoke a
system privilege from a user?
Q: How do you create a
table constraint?
Q: What do the following
SQL statements do?
SQL> CREATE TABLE
test_migrate
(col1 NUMBER,
col2 VARCHAR2(1000),
CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
)
/
SQL> SELECT table_name, num_rows, blocks, chain_cnt
FROM user_tables
WHERE table_name = 'TEST_MIGRATE'
/
SQL> ANALYZE TABLE
test_migrate COMPUTE STATISTICS
/
SQL> ALTER TABLE
test_migrate MOVE TABLESPACE data2move
/
SQL> ALTER INDEX pk_test_migrate
REBUILD TABLESPACE
index2move
/
SQL> SELECT name,
lf_rows, del_lf_rows,
del_lf_rows/lf_rows
"Over 30%"
FROM index_stats
WHERE name = 'PK_TEST_MIGRATE'
/
SQL> DROP TABLESPACE
index2move
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/
|