CODEWITHSUNDEEP
plsqloracle11g
tp9
tp9

Reputation: 341

How to monitor a plsql block statement

I'm trying to figure out why insert and update statements take a lot longer to run on the production server versus the test server. I don't know how to go about monitoring the state of the block as it is running in SQL Developer. Something like a rownum plus sysdate output after so many rows have been inserted would be a good start.

Is there a way to output dbms_output.put_line statements while the block is running?

I am currently running SQL Developer 3.1.07 on Oracle 11g.

EDIT TO ADD SOLUTION:

PROCEDURE log_timestamp (
    PI_trans_num    number  
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO timestamp_log (log_timestamp, trans_num)
    VALUES (SYSTIMESTAMP, PI_trans_num);
    COMMIT;
END log_timestamp;

Upvotes: 1

Views: 893

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

Another method is to use DBMS_Application_Info.

You can add metadata to your session in v$session to share progress by setting "action name", and by maintaining progress reports through v$session_longops.

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52376

One of the better ways of monitoring PL/SQL performance on a row-by-row basis is to use DBMS_Profiler, which will give you execution statistics for each row and how many times it executed.

It won't diagnose a SQL execution problem of course, but it will highlight how much of your execution time is spent on executing each statement.

Upvotes: 2

Burhan Ali
Burhan Ali

Reputation: 2229

dbms_output.put_line statements will only be shown after the pl/sql block has been executed so they're not suitable for identifying problems while code is running.

If you want to keep track of long running processes you could log to a database table using an autonomous transaction (so you don't interfere with the current transaction) and monitor the logging table.

Taking an alternative approach, you could look at the explain plans for the insert and update statements being run on both the test and production servers. This might identify a difference on the production system which will explain the time difference.

Upvotes: 1

Related Questions