Dears,
Today while I was watching a SQL query execution statsistics, I noticed a weired behaviour coming out of oracle's DBMS_SQLTUNE.REPORT_SQL_MONITOR package.
DBMS_SQLTUNE.REPORT_SQL_MONITOR is used to report the live execution statistics of the SQL.
This particular package reported my active SQL as "DONE(ERROR)" as if like some one killed the session or it failed due to a temp or undo or datafile space error.
It didnt report why it failed though still. Interestingly the elapsed time is 7748 vs it's overall query duration reported was 317s.
It took me by surprise, then I check v$session and noticed it isnt rolling back the merge it was running; since it wasnt reading from undo datafile.
So I suspected this is a weird reporting issue. So I continued checking if the session is really active.
Yes the session was really active and v$sql perfectly increments elapsed, cpu, io wait times along with its corresponding other metrics.
Cause: the query ran using parallel slaves, query had plan regression and it ended up running longer with few slaves staying idle for more than 30mins.
Now oracle says this particular condition where a px slave part of the query execution staying idle is an anomaly and is reported as a failed sql execution from oracle end; but really isnt the case :)
RDBMS: Oracle
Version: 12.2
Oracle note: 1900060.1
Bug: 13523091 (Incorrect status in v$sql_monitor with parallel query)
Thank you!
No comments:
Post a Comment