How to find long running query in oracle AND chained (and migrated) rows

How to find long running query in oracle.

How to find long running query in oracle.

SELECT l.sid, l.start_time, l.username, l.elapsed_seconds
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea a
WHERE a.elapsed = l.elapsed_seconds
AND l.elapsed_seconds > 1

chained (and migrated) rows

chained (and migrated) rows

A chained row is a row that is too large to fit into a single database data block.
For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Some conditions that will cause row chaining are:
Tables whose row size exceeds the blocksize
Tables with long and long raw columns are prone to having chained rows
Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.

This query will show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

To see which rows are chained:

ANALYZE TABLE tablename LIST CHAINED ROWS;

This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script (in $ORACLE_HOME/rdbms/admin).

SELECT * FROM chained_rows;

How to correct chained (and migrated) rows issue:

The steps invloved are:

1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.

Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.

The migrated rows can be cleaned up with SQL plus script also.

Regular pruning of chained rows will improve the performance of your I/O subsystem.

Increase the PCTFREE to avoid migrated rows