Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Same query shows different SQL ID

User170058Sep 10 2019 — edited Sep 10 2019

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

Comments
Post Details
Added on Sep 10 2019
4 comments
4,830 views