We have following query which joins two tables A and B. Table B is out driving table and it joined to TABLE A on a PK column.
In 10.2.0.5, the query is completing in 0.0003 seconds however in 11.2.0.4 the same query taking 0.0554 seconds.
This query is executed very frequently, so due to the increased in response time, this query is appearing as Top resource consuming sql in 11g.
We didn't see this query in top query list in 10g database.
Any one can please explain what is the difference between the execution plan in 10g and 11g ? I expected to see the same plan as of 10g (simple one nested loop with subsequent table visit), however in 11g, i am seeing a additional nested loop. Thanks in advance for your time and advise.
SELECT
A.COLUMN-1,
B.COLUMN_2
FROM TABLE_A A ,
TABLE_B B
WHERE A.COL_ID = B.COL_ID
AND B.COL_NUM = :B1
AND ROWNUM = 1 ;
10.2.0.5:
-----------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | HASH UNIQUE | | 1 | 64 | 7 (15)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 1 | 64 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 1 | 14 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_COL_NUM | 1 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 50 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_COL_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
11.2.0.4:
------------
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | HASH UNIQUE | | 1 | 64 | 7 (15)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 1 | 64 | 6 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 64 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 1 | 14 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_COL_NUM | 1 | | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_COL_ID | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TABLE_A | 1 | 50 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------