your query returns 20 rows but the CBO estimates 38091 - so there is an error somewhere in the estimations.

1. step 5 does a Full Table Scan on M_TRANS and thinks that the given filter conditions limit the resultset to contain only 10 rows - so the first question would be if the 10 are plausible.

2. based on the small number of rows from step 5 the following step step 4 does a NL join with an access on IIS_TRANS_MASTER using the index IDX_IIS_TRANS_ID. Here the CBO estimates a resultset of 38091 rows - and again the question is: is this number in the right ballpark.

3. the last join is a hash join in step 2 combining the (estimated) 38091 rows from the preceeding NL join with the > 2M rows in IIS_REQUEST_TRANS. Since your resultset only contains 20 rows it's quite obvious that the hash join is not a good idea here and a NL join would perhaps be a better idea.

You could try to add a couple of hints (/*+ leading (B A C), use_nl(B A C) */ ) to check if the performance gets better with a NL join (instead of the hash join) in step 2. Another option would be to check the size of the resultsets for the filtered FTS on M_TRANS and the join of M_TRANS und IIS_TRANS_MASTER. This could be done by using simple count-queries or by the use of trace options (sql trace, rowsource statistics, or sql monitoring - if you have the appropriate licence)

Hi~!
I think that the main reason of bad performance is big difference between "Actual Rows" and "Estimate Rows".
so, if you use "gather_plan_statistics"hint dbms_xplan package, you can find the difference.
for example.