Why query by DB PK (index) is very slow?

The root cause is DB chained rows.


http://www.dba-oracle.com/t_preventing_chained_migrated_rows.htm

• Preventing chained rows - Chained rows can occur when a row is to large for a data block.  In these cases, moving large objects into a tablespace with a larger blocksize can often relieve chained rows.
  
• Preventing migrated rows - Migrated rows occur when a row expands (usually with a varchar2 data type), and there is not enough reserve defined by PCTFREE for the row to expand. In this case, you prevent future relocated (migrated) rows by adjusting the PCTFREE to ensure that future rows will have enough room to expand and then reorganize the existing table (using data pump, CTAS or dbms_redefinition) to remove the fragments. 

Here is the analysis from our DBA

How to find and eliminate Migrated and Chained rows

---------------------------------------------------

CONCEPTS:

* A row Migrates when a block was found to have the space available for

  a row which underwent an update that increased its size over and beyond

  its block's available space.

* A Chained row occurs when there is no block which can hold the row after

  it underwent an update which increased its size beyond the available free

  space in its hosting block. The solution is to split the row over several

  blocks.         

CAUSES and EFFECTS:

* Causes for migrated and chained rows:  Inserts, updates and deletes over

  periods of time

* Results from migrated and chained rows:  Degraded response for queries.

SOLUTION:

1)  Analyze the table:

    To prevent an ORA-1495 (specified chained row table not found), run the

    $ORACLE_HOME/rdbms/admin/utlchain.sql script.

  

    TRUNCATE TABLE CHAINED_ROWS:

    ANALYZE TABLE <table name> LIST CHAINED ROWS;

2)  List the Migrated or Chained rows.

     

    From SQL*Plus:

    col owner_name format a10

    col table_name format a20

    col head_rowid format a20

    select owner_name, table_name, head_rowid from chained_rows;

3)  You can now eliminate the Migrated or Chained rows by Create Table

    as Select (CTAS), exporting and then importing the table or by following

    the next steps:

    A) Create an empty copy of the table that has the Migrated or Chained rows.

       CREATE TABLE <temporary table name> AS

        SELECT * FROM <table name> WHERE ROWID IN

         (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='<table name'>');

    B) Now delete the Migrated and Chained rows from the table. 

       DELETE FROM <table name> WHERE ROWID IN

        (SELECT HEAD_ROWID FROM CHAINED_ROWS

         WHERE TABLE_NAME='<table name>');

    C) Insert the rows back to the table.

       INSERT INTO <table name> SELECT * FROM <temporary table name>;

      Truncate the chained_rows table and drop the temporary table.

      Alternatively, you can move the table to a tablespace if the row cannot fit in the block and you need a tablespace with a   larger block size:

      alter table <table_name> move <tablespace>;