On Fri, Jul 18, 2014 at 2:26 AM, Larry Martell <larry.mart...@gmail.com> wrote:
> I have a python cx_Oracle script that does a delete from a table.
> Usually this takes well under 1 second. But sometimes it takes 1 to 2
> minutes. I wanted to monitor that delete and if it's taking too long I
> want to see what is blocking it. I run the delete sql in a thread...
I don't know Oracle specifically, but if it's anything like
PostgreSQL, you'll probably do better with a completely separate
connection to the server, which might need to be a separate process.
In PostgreSQL, I can query currently-active transactions thus:
rosuav=> select state,query from pg_stat_activity;
state | query
---------------------+-------------------------------------------
idle in transaction | select * from pg_stat_activity;
active | select state,query from pg_stat_activity;
active | drop table test;
(3 rows)
(Better than that: Add "where pid=..." to that, using the backend PID
provided by the thread you're monitoring, by "SELECT
pg_backend_pid()". But that's even more PostgreSQL-specific.)
With info like that, you can see what's happening, and whether it's
stalled out or in a query or whatever. You should also be able to get
some timestamps (Postgres can do that, I would be highly surprised if
Oracle can't), such as when the transaction started, so you can see
how long it's been stalled.
Thing is, this requires a quite separate connection, which means
you're monitoring the far end rather than the local thread. I suspect
this will give you better results; Oracle's bound to have facilities
for doing this, whereas your local thread may or may not be usefully
monitorable.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
