3

I have a traceback from a machine where the following query seemed to get hung for days:

SELECT table_name FROM user_tables

What could possibly generate such a lock? Users can never modify this table; and there were plenty of subsequent instances of this query that ran successfully.

David Fraser
  • 6,475
  • 1
  • 40
  • 56
  • Unfortunately that system isn't directly accessible and the service had to be restarted to resolve the problem; I'm trying to work out how to prevent this happening again... – David Fraser Mar 13 '12 at 12:54
  • It's Oracle 10.2.0.4 on Windows if that's relevant... – David Fraser Mar 13 '12 at 13:09
  • Wondering if it could be a communications issue; this is in Python using cxOracle – David Fraser Mar 13 '12 at 13:18
  • @Devid Fraser - What edition of the database are you using? If you're using the enterprise edition with the performance & tuning pack, we may be able to look at the data in the ASH tables (Active Session History) to get more details about what was going on. – Justin Cave Mar 13 '12 at 14:05
  • Unfortunately this is at a client we're dealing with through a third party, so it's not that easy to debug; I was wondering if there any known causes for a system table like this locking – David Fraser Mar 14 '12 at 10:07

1 Answers1

3

So, since the condition no longer exists, there's no way to tell what happened.

However, in the future, if this or something similar happens again, you'll want to use Oracle's wait interface. That is, look at V$SESSION.

First, you'll want to determine if the process is spinning (i.e., on CPU) or blocking (i.e., waiting on a wait event). The way to determine that, is to look at the STATE column:

  • If STATE is 'WAITING', then the session is blocked. If that's the case, then EVENT column should describe what event the session is waiting on.
  • If the STATE is something other than 'WAITING', then the session is on the CPU, and the EVENT column is the last thing that it waited on.
    • If STATE is 'WAITED KNOWN TIME', then WAIT_TIME is the time waited in centiseconds.
    • If STATE is 'WAITED SHORT TIME', then the session waited less than a centisecond.
    • If STATE is 'WAITED UNKNOWN TIME', then the time waited is not known because timed_statistics was set to FALSE for the session.

Hope that helps.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67