what are some commands that i can include to find out what is happening during procedure execution
I have a procedure that copies the records from one table to another based on certain criteria. It is understandable that it would take a long time to execute. However, some data sets processed successfully while others didn't. I am trying to pinpoint the problem and wuold like to be able to see what is happening as the procedure is executed. Is there a way to add some lines to the procedure to get some feedback? During one run nothing happened and it ran for 3 days for a small dataset that should have completed
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>method to see events as they happend
This will not show you things 'as they happen'. dbms_output buffers output until the code completes.
This will not show you things 'as they happen'. dbms_output buffers output until the code completes.
ASKER
yes i did create a log but it only gives output once the procedure is complete
Did you issue commits after inserts into the log table?
ASKER
commit is at end of procedure
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
==>>method to see events as they happend
==>>This will not show you things 'as they happen'. dbms_output buffers output until the code completes
yes, unless you are using a tool like toad which can poll the buffer
==>>This will not show you things 'as they happen'. dbms_output buffers output until the code completes
yes, unless you are using a tool like toad which can poll the buffer
What I actually do is create a 1 line procedure that takes a log comment as a parameter. It is an autonomous transaction and commits. This way it doesn't bother the flow of the actual procedure and you can see what is happening.
I cannot seem to find the code at the moment, no idea where it went, but it looks something like this:
create or replace procedure log_message(mess varchar) as
pragma autonomous transaction;
begin
insert into log_tbl values (mess);
commit;
end;
/
I cannot seem to find the code at the moment, no idea where it went, but it looks something like this:
create or replace procedure log_message(mess varchar) as
pragma autonomous transaction;
begin
insert into log_tbl values (mess);
commit;
end;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok i will try it
You can set trace on the procedure to figure out the problem.
Alternatively, your DBA should be able to help you out my monitoring using ADDM
Alternatively, your DBA should be able to help you out my monitoring using ADDM
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DBMS_OUTPUT and LOG to another table will give solution but those have some limitation like buffer, commit etc.
if you feel procedure taking time because of some sql and your using any tool like TOAD, then check the session browser it will tell currently which part of the sql running.
if you feel procedure taking time because of some sql and your using any tool like TOAD, then check the session browser it will tell currently which part of the sql running.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_debug.htm#ARPLS66092
I've never used it. I typically create a 'log' table and insert stats into it as the code runs.