pl/sql dbms_scheduler hanging, how to debug further?

Question

I've got a PL/SQL block executed by the DBMS_SCHEDULER. It was started some time last week and should've been completed by now, but it's not. I'm trying to pin down the problem but need some advice on how to continue debugging this. Here are the facts.

1) Oracle 11g

2) The PL/SQL code outputs to a logging table and I can get the general area where the problem is and this is how I found the hang. I can't get a line number as to where it's stuck. I don't know how.

3) The V$SESSION_LONGOPS table shows me the user/schema is still running however the dates are all several days in the past. It hasn't budged since then.

4) The OPNAME in V$SESSION_LONGOPS is split between "Gather Table's Index Statistics" and "Table Scan" for a bunch of different tables (8 for the first, 8 for the second). They look to be a wide range of tables. For each of the lines, the SOFAR and TOTAL values are equal to one another. The "Gather..." lines are all zeros while the "Table Scan" lines are non zero but equal.

5) If I do a query on V$SESSION, my job is ACTIVE.

6) There is a lock on a table that I'm processing, however I see no other sessions that are playing in my schema.

7) CPU utilization is pretty low.

Is it possible to peek deeper into the PL/SQL block that is being executed? I can find the full SQL in V$SQL which shows me the raw block that was passed in. I would like to see deeper into compiled custom procedures that are called (line number, etc).

Regarding locks, I can see a lock in V$LOCKED_OBJECTS assigned to a table of mine, is there a table that will show me processes that are blocked on a lock? I want to find deadlock if there is any.

Any suggestions are greatly appreciated,

mj

Answer 1

Without building your own instrumentation, or enabling debugging or profiling, there is no way to find the line number that is currently executing. Which seems kind of limiting, but imagine the performance impact of tracking every line number.

Most long-running jobs are waiting on SQL, not PL/SQL. (If that's not true you've got other problems.) There are many ways to track SQL statements. V$SESSION_LONGOPS can be useful, but there are usually better ways. Probably the best is to use Oracle Enterprise Manager, if you have it. Go to the target, then go to Performance -> Top Activity, and you'll see a chart of system activity and SQL statements for the past hour.

For example (from the OEM Concepts guide):

If you don't have OEM installed, or don't have access, there are several other ways to track SQL. First, you want to identify which are the relevant SQL statements. This will tell you what's running right now, and how long it's been running:

select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
where users_executing > 0
order by 1 desc;

If you're lucky it's only one SQL statement taking a long time. If there are a series of small SQL statements, you may need to exclude the where users_executing > 0 predicate and find the most expensive statements related to your application.

In general a SQL statement will be slow or stopped for three main reasons:

1. Waiting for resources. Check select * from dba_resumable; Perhaps your query took up too much temporary tablespace and is waiting for a DBA to add more. Or maybe you have parallel queueing enabled and it's waiting for more parallel sessions. (I'm not sure if that would show up in DBA_RESUMABLE.)
2. Waiting for a lock. Check select * from gv$session where final_blocking_session is not null; Any rows in there could be a problem. You generally do not want to track locks at the object level. It's much easier to find out who is blocking you instead of what is blocking you. When a row or table is locked, your session will wait on the transaction, not the row or object. Note that deadlocks only occur when two sessions require the same resource in a different order, and if that had happened there would be an error message.
3. Waiting for SQL to execute. This is clearly the most difficult issue. There's no simple checklist for what to do now. Normally I'll start by generating an active report, which shows the plan and how long each step is taking: select dbms_sqltune.report_sql_monitor(sql_id => 'your sql id', type=>'active') from dual;. That report also works for a PL/SQL block, but then it will only show you how long each SQL is taking. Then it's up to you to figure out how to make it run better.