12 Replies Latest reply: Apr 5, 2013 11:34 AM by Hesh RSS

    DBMS_APPLICATION_INFO for code instrumentation

    Hesh
      hi,

      I was reading about code instrumentation using DBMS_APPLICATION_INFO, by logging the different programming events in PL/SQL code and tracing them through V$SESSION.

      But after the session is closed, we are not able to 'see' the information, If some java/other code is calling this procedure or this procedure is scheduled in DB level itself and something happend during the execution, then can we trace that info? If not how can we relie on this package in a better manner? one custom logging mechanism looks more convenient way of logging I feel.

      Please correct me if my understanding is wrong!

      Thanks
      Hesh
        • 1. Re: DBMS_APPLICATION_INFO for code instrumentation
          Keith Jamieson
          If you have a pl/sql program which for example , has 10 separate tasks to perform, all of which are relatively long running , and you want to know where abouts you are in that task, then you should use dbms_application_info.


          If you need to trace what has happened subsequent to your procedure run, then you should use a custom table and a custom autonomous transaction procedure to log any details.

          The reason for using the autonomous transaction, is so that you can log and commit information to your tracking table without affecting the normal transaction handling.

          In some cases , you may want both tasks. You should also be able to turn your logging on and off dynamically.
          • 2. Re: DBMS_APPLICATION_INFO for code instrumentation
            Hesh
            Thanks Keith,

            Got it now!, this is for monitoring long running procedures during the execution.

            You should also be able to turn your logging on and off dynamically.
            did not understand this, how can we disable and enable logging dynamically?



            Regards
            Hesh
            • 3. Re: DBMS_APPLICATION_INFO for code instrumentation
              Keith Jamieson
              There are a number of ways, but the most common would be to have a table and numbers to hold the level of debugging:

              eg

              so 0 would be no debugging
              and 10 would be complete debugging.

              The downside is that all your procedures have to first find out what the current level of debug is set to. eg by calling a function.

              The upside is that you can customise this to whatever level of detail you need
              • 4. Re: DBMS_APPLICATION_INFO for code instrumentation
                Hesh
                Thank you Keith, for your valuable responses and time, can you do me some more favor by pointing to the links or working example you know, to get more insight

                Regards
                Hesh
                • 5. Re: DBMS_APPLICATION_INFO for code instrumentation
                  Hoek
                  Tom Kyte has blogged about it ( http://tkyte.blogspot.nl/2005/06/instrumentation.html )
                  and explained/demonstrated it on his site, so you might want to do a search on 'instrumentation' or 'dbms_application_info' @ http://asktom.oracle.com
                  • 6. Re: DBMS_APPLICATION_INFO for code instrumentation
                    Hesh
                    Thanks Hoek ! for point to Toms article, I was actually asking for any link or references to Switch on/off the code instrumentation which Keith mentioned

                    Regards
                    Hesh

                    PS: I was there in Delft last year for few weeks for a client, enjoyed a lot ,surfing allover using a bike !, nice place to live :)
                    • 7. Re: DBMS_APPLICATION_INFO for code instrumentation
                      Hoek
                      I was actually asking for any link or references to Switch on/off the code instrumentation which Keith mentioned
                      I'm pretty sure there's an example on asktom, but I cannot find it at this moment (too busy, will look later today).
                      You could switch on/off instrumentation through a package variable, in a nutshell.

                      PS: thanks ;)
                      • 8. Re: DBMS_APPLICATION_INFO for code instrumentation
                        Hoek
                        edit
                        Here you can download a presentation and several ready-to-go routines:
                        http://asktom.oracle.com/pls/apex/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D1479032009923865622&p_cat=odtug.zip&p_company=822925097021874
                        • 9. Re: DBMS_APPLICATION_INFO for code instrumentation
                          Keith Jamieson
                          Turning debugging on or off could be as simple as changing a value in a table. You could use a packaged procedure to turn the value on or off.

                          If you keep the value in a table , it should be quite straightforward to add other debug levels.

                          So
                          create table debug_level
                          (level number);
                          So lets assume that debug_level is 0 is no debugging and any other value requires debugging.

                          So now teh first job of each procedure or function will be to interrogate this table to find out the debug_level, so do this via a function.
                          create or replace 
                          function getdebuglevel
                          return number
                          as
                          v_debuglevel number;
                          begin
                          select level 
                            into v_debuglevel
                            from debug_level;
                          
                          return v_debuglevel;
                          end;
                          Now in the code when we want to add debug info, we do the following:
                          if v_debuglevel = 1 
                          then 
                                -- log parameter values into the procedure
                                log_info(); /* loginfo must be declared as automonous_transaction procedure */
                          end if;     
                          For development I use the plsql preprocessor (available in Oracle 11g )
                          $if $$MY_DEBUG
                          $THEN
                          my_debug('param_name1','param_value1');
                          my_debug('param_name2','param_value2');
                          $END


                          So in this case if I want to set debugging on (for a session)
                          my_debug will log the debug information to a table.

                          alter session set plsql_ccflags='MY_DEBUG:TRUE';

                          and then I compile my procedure or function in the same session

                          alter procedure mytestproc compile;

                          You can also do this at the system level if you have the correct privileges.
                          • 10. Re: DBMS_APPLICATION_INFO for code instrumentation
                            rp0428
                            >
                            I was actually asking for any link or references to Switch on/off the code instrumentation which Keith mentioned
                            >
                            You have to write code to change the log level as part of your logging package.

                            One way is to use 'Conditional Compilation'. This Oracle-Base article has an example of that specifically for LOGGING
                            http://www.oracle-base.com/articles/10g/conditional-compilation-10gr2.php

                            You can also use 'conditional compilation' if your procedures to either 'include' or 'exclude' logging calls.

                            NOTE: Once the code is compiled the setting is fixed.

                            The second way is to ALWAYS include logging calls in your code. These call a LOG package procedure. It is the LOG package itself that controls the level of logging and you can change it during the session.

                            This isolates the control of the logging so your application code doesn't deal with it. The log functions in the LOG package are written to perform different amounts or types of logging based on what hte LOG_LEVEL is.

                            I provided some template code that shows how this is used in this thread from January
                            How to get a called procedure/function name within package?

                            Also see my reply in this thread from last year.

                            The package code could also use a separate procedure for each level. The appropriate procedure could be called directly by the app code if you know what level should be used (prevents developers from accidentally passing the wrong 'level' parameter) or, more often, the appropriate procedure is called by the top-level log procedure based on the current log level. See the links above for explanation of the 'TYPE_LOG_INFO' custom type.
                              gc_pk_name         CONSTANT VARCHAR2(30) := 'PK_LOG';
                              SUBTYPE g_log_level_type IS VARCHAR2(10);
                            
                              gc_log_level_fatal CONSTANT g_log_level_type := 'FATAL';
                              gc_log_level_error CONSTANT g_log_level_type := 'ERROR';
                              gc_log_level_info  CONSTANT g_log_level_type := 'INFO';
                              gc_log_level_debug CONSTANT g_log_level_type := 'DEBUG';
                            
                              -- use default log_level of 'error''
                              gv_log_level                g_log_level_type := gc_log_level_error;
                            
                            
                              -- procedure to set the logging level. Should be AUTONOMOUS.
                              PROCEDURE P_SET_LOG_LEVEL(p_log_level g_log_level_type);
                            
                              -- procedure to log error message. Should be AUTONOMOUS.
                              PROCEDURE P_LOG_ERROR(p_log_info TYPE_LOG_INFO);
                            
                              -- procedure to log fatal messages. Should be AUTONOMOUS.
                              PROCEDURE P_LOG_FATAL(p_log_info TYPE_LOG_INFO);
                            
                              -- procedure to log info messages. Should be AUTONOMOUS.
                              PROCEDURE P_LOG_INFO(p_log_info TYPE_LOG_INFO);
                            
                              -- procedure to log debug messages. Should be AUTONOMOUS.
                              PROCEDURE P_LOG_DEBUG(p_log_info TYPE_LOG_INFO);
                            
                            --- package body
                            
                            /******************************************************************************
                             *  NAME:      P_LOG_DATA
                             *  PURPOSE:   Logs data for the other procs.
                             *
                             ******************************************************************************/
                             PROCEDURE P_LOG_DATA(p_log_info TYPE_LOG_INFO)
                             IS
                               PRAGMA AUTONOMOUS_TRANSACTION;
                               c_proc_name  VARCHAR2(80) := 'P_LOG_DATA';
                               v_step_num   INTEGER      := 1; -- start with step one
                            
                             BEGIN
                               -- log the info
                            
                               INSERT INTO log_table (PACKAGE_NAME, PROC_NAME, STEP_NUMBER, LOG_LEVEL,
                                           LOG_CODE, LOG_MESSAGE, LOG_TIME)
                                 VALUES (p_log_info.package_name,
                                         p_log_info.proc_name,
                                         p_log_info.step_number,
                                         p_log_info.log_level,
                                         p_log_info.log_code,
                                         p_log_info.log_message,
                                         p_log_info.log_time
                                        );
                               COMMIT;
                            
                             EXCEPTION
                             WHEN - - - as required
                               . . .
                             END P_LOG_DATA;
                            
                            
                            /******************************************************************************
                             *  NAME:      P_LOG_INFO
                             *  PURPOSE:   Log INFOs appropriately.
                             *
                             *  DESC: An autonomous transactions is used so that the log transaction can
                             *        COMMIT independently of any calling transaction COMMIT or ROLLBACK.
                             *
                             ******************************************************************************/
                             PROCEDURE P_LOG_INFO(p_log_info TYPE_LOG_INFO)
                             IS
                                 PRAGMA AUTONOMOUS_TRANSACTION;
                                 c_proc_name  VARCHAR2(80) := 'P_LOG_INFO';
                                 v_step_num   INTEGER      := 1; -- start with step one
                                 v_log_info  type_log_info := p_log_info;
                            
                             BEGIN
                               -- set the log level and log the data
                            
                                 v_log_info.log_level := gc_log_level_info;
                                 p_log_data(v_log_info);
                            
                            
                            EXCEPTION
                             WHEN . . . -- as required 
                                . . .
                             END P_LOG_INFO;
                            • 11. Re: DBMS_APPLICATION_INFO for code instrumentation
                              Hoek
                              Another nice one:

                              http://method-r.com/software/ilo

                              "How does the developer turn trace on with ILO?

                              The job of turning trace on and off is normally one that is performed at
                              application runtime, long after the developer's involvement in the project
                              has occurred. The BEGIN_TASK call checks to see whether someone has
                              expressed the intent to trace a specific MODULE/ACTION pair by calling
                              ILO_TIMER.GET_CONFIG. This method determines which tasks should be
                              traced.

                              A developer who wishes to unit test his code can call
                              ILO_TIMER.SET_MARK_ALL_TASKS_INTERESTING(TRUE,TRUE) to override the
                              normal GET_CONFIG schedule, but calls to this method are typically not going
                              to be present in production code unless they are implemented as menu options
                              (for example, Help > Debug > Trace All). The decision about whether or not
                              to trace should be reserved until runtime."
                              • 12. Re: DBMS_APPLICATION_INFO for code instrumentation
                                Hesh
                                Awasome ! OTN is really a great place to learn!

                                Thank you Keith(It was easy !), rp428 and Hoek for sharing your knowledge, I will spend some time to study all these

                                Regards
                                Hesh