Hi all,
I’m trying to get to the bottom of an issue where a SQL statement (in this case
an insert) that normally runs in 15 minutes suddenly takes two hours. Checking
the session, it’s not blocked, the plan hasn’t changed and seems fine, all row
estimates are good, there is no more data than usual, backups aren’t running
(though I can’t rule out other background resource hogs, as I have somewhat
limited access). Then I notice that the start time shown in SQL Monitor has
changed, and going back to the session I see there have been six sql_exec_ids
and sql_exec_starts for the sid/serial#/sql_id, even though the procedure only
calls it once. Some of them are a couple of minutes apart, some are after 40
minutes. (With hindsight I should have looked at v$sql.object_status and also
checked whether the session was rolling back, which I’m guessing it was.)
The last time we saw this, it coincided with a job that gathered stats on all
partitions marked as stale, passing no_invalidate = false. We fixed that by
changing it to true and the issue seemed to go away. This time though, this
stats job wasn’t running and neither was anything else I could see that would
have affected the same tables and partitions - all our large tables are
list-partitioned by business date and many are subpartitioned by business line
etc, partly to isolate batch processes. Looking at dba_active_sess_history I
can see it’s happened before but sporadically.
I’m a bit stumped about what to do about it. Would this be logged anywhere
(similar to a deadlock report)? I couldn’t see anything likely in
v$diag_trace_file_contents but maybe I’m looking for the wrong thing. Is it the
case that another session must have hard-invalidated the cursor, and I just
have to find the smoking gun, or is there some other scenario like an internal
failure? This is 12.2.0.1.
Thanks,
William--
//www.freelists.org/webpage/oracle-l
