Hi All,
I am a bit lost here , we upgraded from 11g to 12c R2 base version recently. One of the queries we have used the optimizer hint _unnest_subquery. We are monitoring this specific query every day as it is occassionally dumping ORA-7445. Oracle support is working on the fix for this. But my main concern here is the query did a core dump last week at the time when I checked the v$sql for the sql id I got '8kxzg9rj4p409'. But yesterday someone ran the query and when i checked the v$sql the above sql id it was not longer available but instead I found a new sql id for the same query ('d3cm9yw3qz25m' ) . But today again I checked the v$sql and what I got was the old sql id.
I tried to check the historical data tables for active sessions sqlstat and sqlbind I am able to get data for both the sql_id . There are more records for the sql_id '8kxzg9rj4p409' ( almost everyday run ) but for the second I have only occassional snap like for once a day but only five days.
Unfortunately I am no longer able to find the sql text as for the second sql_id to reconfirm my findings.
Any body has seen this behaviour. Please suggest