DB version: 11.2.0.4
OS : OL 6.9
Yesterday, I had a badly performing SQL with SQL_ID 6aza4jda0dg3u
To fix the performance issue, I gathered optimizer stats for a table in this query (during off-peak hours).
Today morning, I ran the same query from SQL*Plus. SQL's text was not changed because I was using bind variables to pass the parameters.
The bind variables I passed today was same too.
After running the below query, from another toad session, I ran the below V$SESSION query, but it is not returning any rows !!
SELECT * FROM GV$SESSION WHERE SQL_ID = '6aza4jda0dg3u'
This means that the SQL_ID has changed. When I gathered optimizer stats, I used NO_INVALIDATE => TRUE . This means the dependent cursors are not invalidated. Even if it was invalidated, the SQL_ID should remain same. Right ?
--- Below is the SQL I ran from SQL*Plus. It has four bind variables
variable var1 number;
variable var2 varchar2(50);
variable var3 varchar2(50);
variable var4 varchar2(50);
exec :var1 := 4;
exec :var2 := 'Allocated';
exec :var3 := 'Pending2';
exec :var4 := 'Returned';
SELECT
COL1,
COL2,
...
FROM
TABLE1 T1,
TABLE2 T2,
TABLE3 T3,
.
.
.
where
T20.BU_ID = T4.BU_ID AND T20.ROW_ID = T4.ASSET_ID AND
T4.BU_ID = T13.ROW_ID AND
T4.BU_ID = T16.PAR_ROW_ID AND
(T3.VER_NUM = :var1 AND T20.X_MIG_FLAG = 'Y') AND
(T20.SERIAL_NUM IS NULL AND T15.TYPE = :var2 AND T20.STATUS_CD IN (:var3,:var4) AND T15.PROCESS_RET IS NULL);
And one more thing. The following gv$sql query is still returning rows, with LAST_LOAD_TIME value showing yesterday evening's time
select * from gv$sql WHERE SQL_ID = '6aza4jda0dg3u'