Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

what are some commands that i can include to find out what is happening during procedure execution

Posted on 2011-10-14
15
Medium Priority
?
327 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:sikyala
  • 4
  • 3
  • 2
  • +4
15 Comments
 
LVL 7

Accepted Solution

by:
Jacobfw earned 400 total points
ID: 36970120
dbms_output.put_line provide a method to see events as they happend and also to show values of variables.

http://www.dba-oracle.com/t_dbms_output_put_line.htm
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36970134
There is dbms_debug:
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36970142
>>method to see events as they happend

This will not show you things 'as they happen'.  dbms_output buffers output until the code completes.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sikyala
ID: 36970228
yes i did create a log but it only gives output once the procedure is complete
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36970248
Did you issue commits after inserts into the log table?
0
 

Author Comment

by:sikyala
ID: 36970262
commit is at end of procedure
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 36970285
>>commit is at end of procedure

That is why you didn't see the rows in the log table until the end.

you can also do debugging using dbms_pipe:  Example 1: Debugging - PL/SQL

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_pipe.htm#CHDJHGEB

0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36970302
==>>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
0
 
LVL 35

Expert Comment

by:johnsone
ID: 36970874
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;
/
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 400 total points
ID: 36970884
Then in your procedure, just call the log procedure:

log_message('got to part 1');
...
log_message('got to part 2');


Since the procedure commits, you can select from the log table and see the messages immediately.
0
 

Author Comment

by:sikyala
ID: 36970917
ok i will try it
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 36973692
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
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 800 total points
ID: 36978933
i think setting dbms_application.set_module can be used for the purpose.
By this u will be able to monitor session and see what is currently happening.

eg:

dbms_application.set_module ('InserProcedure',v_cnt);

v_cnt --- no of rows inserted in a loop
0
 
LVL 3

Expert Comment

by:gajmp
ID: 36990231
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.
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 800 total points
ID: 36991783
see dbms_application_info demo at http://psoug.org/reference/dbms_applic_info.html
for more details
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question