We are using Version 11.2.0.4.0 of oracle. We see suddenly the execution path changed for two queries causing it to run longer. Below is the original and new execution paths for one of the query. These are having remote objects being referred in this query from another database through DB link. We had some changes went into the remote database object(mainly TAB1) and i am trying to figure out from the execution plans(before VS after) , what/which change in stats is making this plan going bad? Need expert advice if by looking these two plans i should get some obvious hints regarding what change in stats would make this new plan come into picture?
Note- we just asked to revert all changes in the remote database so plan is back to before.
I don't have the Sql monitor, so i query the dba_hist_active_sess_history and see the new plan was taking considerable amount of time at line number 6 and 7 and the wait event was "SQL*Net message from dblink".
SELECT *
FROM TCBP BP,
TAB1@PR P,
DEP@PR D,
ADD@PR AD
WHERE BP.PDAY = :B1
AND BP.PNUM = P.TAB1
AND P.DEP = D.DEP
AND D.LO = AD.LO
AND AD.RTYP = '01'
Execution plan BEFORE(Query finishing in few seconds):-
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21500 (100)| | | |
| 1 | NESTED LOOPS | | 146 | 18980 | 21500 (1)| 00:04:18 | | |
| 2 | NESTED LOOPS | | 1982 | 129K| 13570 (1)| 00:02:43 | | |
| 3 | NESTED LOOPS | | 1982 | 81262 | 7700 (1)| 00:01:33 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| TCBP | 1982 | 29730 | 1747 (1)| 00:00:21 | | |
| 5 | INDEX RANGE SCAN | TCBP_IX1 | 1995 | | 13 (0)| 00:00:01 | | |
| 6 | REMOTE | TAB1 | 1 | 26 | 3 (0)| 00:00:01 | PR | R->S |
| 7 | REMOTE | DEP | 1 | 26 | 3 (0)| 00:00:01 | PR | R->S |
| 8 | REMOTE | ADD | 1 | 63 | 4 (0)| 00:00:01 | PR | R->S |
------------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "TAB1","DEP" FROM "TAB1" "P" WHERE :1="TAB1" (accessing 'PR.TECH.COM' )
7 - SELECT "DEP","LO" FROM "DEP" "D" WHERE :1="DEP" (accessing 'PR.TECH.COM' )
8 - SELECT "LO","RTYP","ADD_TXT" FROM "ADD" "AD" WHERE "RTYP"='01' AND
:1="LO" (accessing 'PR.TECH.COM' )
Execution plan AFTER(query running for 24minutes):-
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11G(100)| | | |
| 1 | TABLE ACCESS BY INDEX ROWID| TCBP | 1 | 15 | 1754 (1)| 00:00:22 | | |
| 2 | NESTED LOOPS | | 7516 | 954K| 11G (1)|999:59:59 | | |
| 3 | NESTED LOOPS | | 6041K| 662M| 547M (1)|999:59:59 | | |
| 4 | NESTED LOOPS | | 78M| 3876M| 234M (1)|782:47:29 | | |
| 5 | REMOTE | TAB1 | 78M| 1938M| 53761 (4)| 00:10:46 | PR | R->S |
| 6 | REMOTE | DEP | 1 | 26 | 3 (0)| 00:00:01 | PR | R->S |
| 7 | REMOTE | ADD | 1 | 63 | 4 (0)| 00:00:01 | PR | R->S |
| 8 | INDEX RANGE SCAN | TCBP_IX1 | 2006 | | 12 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT "TAB1","DEP" FROM "TAB1" "P" (accessing 'PR.TECH.COM' )
6 - SELECT "DEP","LO" FROM "DEP" "D" WHERE :1="DEP" (accessing
'PR.TECH.COM' )
7 - SELECT "LO","RTYP","ADD_TXT" FROM "ADD" "AD" WHERE "RTYP"='01' AND
:1="LO" (accessing 'PR.TECH.COM' )