How to get explain plan of sql_id and ROW chaining from cursor

First of all, we need to get   child number of the sql_id.

There can be many child number but only have one plan_hash_value.

SELECT sql_id, child_number, plan_hash_value

FROM gv$sql

WHERE sql_id = '98txwedq81qy3';

 

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

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

98txwedq81qy3 1 233137988

 

We can get explain plan using by below

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid', &child_number));

Once you get based on that we can see which table and Index  is using more time based on elapsed time and verify row chaining and leaf index block count

* 10 |      TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS       |     1 |    78 |     3   (0)| 00:00:01 |

|* 11 |       INDEX UNIQUE SCAN         | GL_CODE_COMBINATIONS_U1    |     1 |       |     2   (0)| 00:00:01 |

|  12 |      TABLE ACCESS BY INDEX ROWID| GL_CONS_FLEXFIELD_MAP      |   461 | 51171 |   143K  (1)| 00:28:44 |

|* 13 |       INDEX RANGE SCAN          | GL_CONS_FLEXFIELD_MAP_CST1 |   461 |       |   143K  (1)| 00:28:39 |

SQL> SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME='GL_CONS_FLEXFIELD_MAP';

TABLE_NAME                      CHAIN_CNT

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

GL_CONS_FLEXFIELD_MAP                   0

SQL>  SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS FROM DBA_INDEXES WHERE INDEX_NAME ='GL_CONS_FLEXFIELD_MAP_CST1';

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS

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

GL_CONS_FLEXFIELD_MAP          GL_CONS_FLEXFIELD_MAP_CST1              3      285837