Systems Engineering and RDBMS

Auditing Row Chaining in the tables in the schema

Posted by decipherinfosys on March 19, 2007

Here is a script (and the explanation of the steps) to identify row-chaining in the tables in the schema and how to get rid of it:

SET DOC OFF
rem  ———————-
rem  chained_rows_audit.sql
rem  ———————-
rem  This script will collect Row Chaining information on the tables in the schema using
rem  information generated from the last ANALYZE from USER_TABLES view.
rem
rem  NOTE:
rem  Run this script under your Oracle Schema.
rem
rem  This script will create a table called USP_CHAINED_ROWS_AUDIT under the
rem  schema it is run to store the results of the chaining query.  Also this
rem  script creates a stored procedure called USP_CHAINED_ROWS that is used
rem  to generate the chaining information of the tables under your schema.
rem  The output is spooled to a file called chained_rows_audit.html
rem
rem  Once the output is collected the procedure and the table are dropped.
rem
rem  ***********************************************************************************
rem  Two ways to get rid of chained rows:
rem  *  If you see a greater percentage (>50%) of row chaining in a particular table,
rem     then it is ideal to:
rem
rem     1. Create a temporary table with all rows from the table where row chaining
rem        is high
rem        For example:
rem        a. CREATE TABLE TEMP_USER_ROLE AS SELECT * FROM USER_ROLE;
rem        b. COMMIT;
rem     2. Disable all Foreign key constraints pointing to USER_ROLE
rem        For example:
rem        a. SELECT table_name, constraint_name
rem             FROM user_constraints
rem            WHERE constraint_type = ‘R’
rem              AND r_constraint_name = (SELECT constraint_name
rem                                         FROM user_constraints
rem                                        WHERE table_name = ‘USER_ROLE’
rem                                          AND constraint_type = ‘P’)
rem        b. ALTER TABLE <table_name from above> DISABLE constraint <constraint_name>;
rem     3. Truncate the chained row table along with dropping the storage
rem        For Example:
rem        TRUNCATE TABLE USER_ROLE DROP STORAGE;
rem     4. Re-Populate the chained row table from the temporary table
rem        For Example:
rem        a. INSERT /*+APPEND*/ INTO USER_ROLE
rem           SELECT * FROM TEMP_USER_ROLE;
rem        b. COMMIT;
rem     5. Enable all FK Constraints that were disabled in step 3.
rem        For example:
rem        Using the same query to get the FK constraints mentioned in Step 3
rem        ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;
rem     6. Verify data and drop the temporary table.
rem        Example:
rem        DROP TABLE TEMP_USER_ROLE;
rem
rem  *  If row chaining is less but significant enough to cause I/O contentions then
rem     follow the steps below.
rem
rem     1. Create the table CHAINED_ROWS using $ORACLE_HOME/rdbms/admin/utlchain.sql
rem        script
rem     2. Analyze the table that has chained rows using LIST CHAINED ROWS clause
rem        Example:
rem        ANALYZE TABLE USER_ROLE LIST CHAINED ROWS INTO CHAINED_ROWS;
rem     3. Create a temporary table that has the same structure as the table that has
rem        chained rows and insert all the chained rows using the ROWID values present
rem        in the CHAINED_ROWS for that table.
rem        Example:
rem        a. CREATE TABLE TEMP_USER_ROLE AS
rem           SELECT *
rem             FROM USER_ROLE
rem             WHERE ROWID IN (SELECT HEAD_ROWID
rem                               FROM CHAINED_ROWS
rem                              WHERE TABLE_NAME = ‘USER_ROLE’);
rem     4. Disable all Foreign key constraints pointing to USER_ROLE
rem        Example:
rem        a. SELECT table_name, constraint_name
rem             FROM user_constraints
rem            WHERE constraint_type = ‘R’
rem              AND r_constraint_name = (SELECT constraint_name
rem                                         FROM user_constraints
rem                                        WHERE table_name = ‘USER_ROLE’
rem                                          AND constraint_type = ‘P’)
rem        b. ALTER TABLE <table_name from above> DISABLE constraint <constraint_name>;
rem     5. Delete the rows from the table that has chained rows using the same ROWID
rem        values present in the CHAINED_ROWS table using ROWID
rem        Example:
rem        a. DELETE FROM USER_ROLE
rem            WHERE ROWID IN (SELECT HEAD_ROWID
rem                              FROM CHAINED_ROWS
rem                             WHERE TABLE_NAME = ‘USER_ROLE’);
rem        b. COMMIT;
rem     6. Re-insert back the data/rows from the temporary table back into the main table.
rem        Example:
rem        a. INSERT INTO USER_ROLE SELECT * FROM TEMP_USER_ROLE;
rem        b. COMMIT;
rem     7. Enable all FK Constraints that were disabled in step 4.
rem        For example:
rem        Using the same query to get the FK constraints mentioned in Step 4
rem        ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;
rem     8. Re-Analyze the table using ANALYZE with COMPUTE STATISTICS and then
rem        execute this script (chained_rows_audit.sql) to check to see whether there
rem        are any more chained rows.   If Chained Rows exists  Repeat the steps 2 thru 8.
rem        Example:
rem        ANLAYZE TABLE USER_ROLE COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES
rem        FOR ALL INDEXED COLUMNS;
rem  ***************************************************************************************
SET TERMOUT OFF
CREATE OR REPLACE PROCEDURE USP_CHAINED_ROWS
(
p_ChainedPct     IN INT
)
AS
v_Count INT := 0;
BEGIN
BEGIN
SELECT 1
INTO v_Count
FROM user_tables
WHERE table_name = ‘USP_CHAINED_ROWS_AUDIT’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE USP_CHAINED_ROWS_AUDIT
(SCHEMA_NAME         VARCHAR2(30),
TABLE_NAME          VARCHAR2(30),
ROW_COUNT           NUMBER,
ROWS_CHAINED        NUMBER,
CHAIN_PCT           NUMBER,
ANALYZE_DATE        DATE)’;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, ‘ERROR in Creating USP_CHAINED_ROWS_AUDIT table’ || CHR(10) || SQLERRM);
END;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, ‘ERROR – ‘ || CHR(10) || SQLERRM);
END;

EXECUTE IMMEDIATE
‘INSERT INTO USP_CHAINED_ROWS_AUDIT
SELECT USER,
ut.table_name,
ut.num_rows,
ut.chain_cnt,
ROUND((ut.chain_cnt/(CASE WHEN ut.num_rows <= 0 THEN 1 ELSE ut.num_rows END))*100, 4),
ut.last_analyzed
FROM User_Tables ut
WHERE ROUND((ut.chain_cnt/(CASE WHEN ut.num_rows <= 0 THEN 1 ELSE ut.num_rows END))*100, 4) >= ‘ || p_ChainedPct;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20099, SQLERRM);
END;
/

PROMPT Executing Chained Rows Procedure…
BEGIN
USP_CHAINED_ROWS(3);
END;
/

SET PAGESIZE 5000 LINESIZE 120 VERIFY OFF ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK OFF
SET MARKUP HTML ON
SPOOL chained_rows_audit.html
PROMPT
PROMPT
PROMPT Chained Rows Report
PROMPT ===================
COLUMN schema_name   HEADING ‘Schema|Name’  FORMAT a30
COLUMN table_name    HEADING ‘Table|Name’   FORMAT a30
COLUMN row_count     HEADING ‘Total|Rows’   FORMAT 9G999G999G999
COLUMN rows_chained  HEADING ‘Chained|Rows’ FORMAT 999G999G999
COLUMN chain_pct     HEADING ‘Chain|Pct’    FORMAT 999D99
COLUMN analyze_date  HEADING ‘Analyze|Date’ FORMAT a20
SELECT schema_name,
table_name,
row_count,
rows_chained,
chain_pct,
TO_CHAR(analyze_date, ‘DD-MON-YYYY HH24:MI:SS’) as analyze_date
FROM USP_CHAINED_ROWS_AUDIT
ORDER BY CHAIN_PCT DESC, TABLE_NAME ASC
/
CLEAR COLUMNS
SPOOL OFF

DROP PROCEDURE USP_CHAINED_ROWS
/
DROP TABLE USP_CHAINED_ROWS_AUDIT
/
EXIT

Sorry, the comment form is closed at this time.

 
%d bloggers like this: