I am trying to write a function to determine whether Oracle is currently running any statistics gathering operations. I am letting Oracle do it's own automated gathering, but am also planning on running my own in a scheduler job (and occasionally on-demand from the CLI), and am looking to avoid concurrent attempts.
I am able to identify ‘IN PROGRESS’ operations using the dba_optstat_operations view, however I have found this to be less than 100% reliable, because interrupted operations (EG if RDS reboots my instance during the operation) remain ‘IN PROGRESS’ forever (or until the row ages off).
I have tried several ways to check any operations marked ‘IN PROGRESS’ to see if they are actually still running, all involving mapping the operation's session_id to the sid in v$session, but nothing I've tried is quite right:
- Active SID doesn't work because SIDs are reused, so the SID could be active but the operation complete.
- Active SID's SQL text doesn't work because some operations run many different SQL statements under the hood.
- I tried correlating the operation's
start_time with the active SID's SQL start time but that also doesn't work because each individual SQL statement has it's own start time (so SQL that starts minutes or hours after the operation start time may or may not be correlated with the operation).
- I've poked around
v$session_longops a little but but I don't see that giving me a deterministic answer either.
This doesn't seem like that novel a problem, so I've been surprised by how difficult it seems to find a solution. Does anyone have any advice or suggestions? Thanks.