4 Replies Latest reply on Jul 29, 2013 7:40 AM by Karthick2003

    Reg: PL/SQL profiler-

    ranit B

      Hi Experts,


      I've some doubts regarding the PL/SQL profiler utility - http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i48283.

      Also reading - http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2012/05/09/using-the-pl-sql-profiler-amp-debugger.aspx
      Never used the utility prior to this, started reading the docs now.


      I'll try to explain my requirement below:

      Having huge quantity of PL/SQL code (packages and procs). I'm aware of the overall flow of data but there are lot of instances where 2 obejcts (procs or packages) are interacting among themselves a number of times.

      The business logic there is very tightly coupled. I mean- need to track the data flow for few table columns inside those objects. I feel like this utility can help in tracking the inter-dependencies and can give me the actual flow.


      Doubt- Using this utlity, can we track the data flow in variable or column level data?


      Please suggest...


      -- Ranit

        • 1. Re: Reg: PL/SQL profiler-

          Using DBMS_PROFILES i don't think its possible to know the values of variables. It just tells the execution time of each block  ( loops, cursors, inserts etc )  of code in the procedure.

          Im not sure of the debugger in TOAD, but i have used the pl/sql debugger in PL/SQL Developer tool. Here you can see the propagation of the code, values of variables, OUT variable's and cursor's values/data set etc. I think this debugger would help.

          • 2. Re: Reg: PL/SQL profiler-
            ranit B

            Thanks for responding Michael.


            Is there any way I can track both the program execution and process flow, and at the same time capture (or logging) all the variable values?

            Will DBMS_TRACE help me in that case?


            Please suggest.

            • 3. Re: Reg: PL/SQL profiler-

              RanitB - I have not use DBMS_TRACE package. As mentioned earlier PL/SQL Developer tool helps in debugging. You can see the program/procedure flow/propagation and the local variable's values using this debugger. First you need to get the system privilege DEBUG ANY PROCEDURE and DEBUG CONNECT SESSION to debug. After doing this right-click on the procedure's name you want to debug and select Test. A new test window appears, providing the necessary input parameters to the procedure click on F9 or the icon present above the BEGIN keyword to start debug. The test window will be loaded with entire procedure you wanted to test. Either you can click on the Run button (identified by the Play icon) in green color - this executes the proc step by step on its own or press the icon present next to Run button (shortcut Ctrl+N) every time to go from one step to next manually in the procedure. In the bottom pane there are 3 columns: Variable, Value and Call stack. If you copy paste the local variables into this Variable column, the value gets automatically populated when the corresponding variables get assigned some value during the course of execution.


              Message was edited by: michaelrozar17 - missed one of the sys privs

              • 4. Re: Reg: PL/SQL profiler-

                If your objective is to reverse engineer a existing process then DBMS_PROFILER or DBMS_TRACE will not be of much use. I have been there. I have always found using pencil and paper and some quality time with the program helps in reverse engineering. There are some fancy tools out there but cant beat your pencil and paper approach


                There are certain aspect of software engineering which are best done by manual approach, I feel till date reverse engineering is one among them!!