Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,319,109 Views

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: