Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Use DBMS APPLICATION INFO for more detailed wait analysis

Use DBMS APPLICATION INFO for more detailed wait analysis

Table of contents
No headers

 

DPA may show waits associated with a PL/SQL call and not the individual SQL statements or steps that comprise the code. For example, DPA may show wait time associated with SQL that looks similar to:

BEGIN CONCAP.QUICKPOLL ('ORA102_TESTBOX', TRUE); END;

In this case, the QUICKPOLL procedure is large and contains many SQL statements, so how can we use DPA to drill into more details? You can use the DBMS_APPLICATION_INFO package supplied by an Oracle database.  This only works if you have access to the code for the PL/SQL package. In this example of the QUICKPOLL procedure, the modified code is similar to the following:

PROCEDURE QUICKPOLL (p_db_name        IN VARCHAR2,
    p_report        IN BOOLEAN DEFAULT FALSE)
 AS
    current_module VARCHAR2(48);
   current_action VARCHAR2(32);
 BEGIN
   -- save the current settings for module and action to reset them when done
   DBMS_APPLICATION_INFO.READ_MODULE(current_module, current_action);
    -- run the startup code
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'STARTUP');
    STARTUP (p_db_name);
 
    -- Gather monitoring data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'MONITOR');
    MONITOR (p_db_name);
        
    -- Publish the data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'PUBLISH');
    PUBLISH (p_db_name);
 
    -- cleanup monitoring data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'CLEANUP');
    CLEANUP (p_db_name);
   -- reset the module and action to the previous value
   DBMS_APPLICATION_INFO.ST_MODULE(current_module, current_action);
 END QUICKPOLL;

When the QUICKPOLL code executes, the MODULE column will be populated with "QUICKPOLL" and the ACTION column will contain "STARTUP", "MONITOR", "PUBLISH" or "CLEANUP". DPA collects this information and associates wait time with each module or action and allows you to see which portion of the PL/SQL code to focus on. In the background, the MODULE and ACTION columns from V$SESSION are populated with the information, and DPA collects it. In the following screenshot, the MONITOR action is shown to accumulates the most wait time. If the QUICKPOLL procedure requires tuning, this section of code should be the top priority.

dbms_application_info(1).jpg

Last modified

Tags

Classifications

Public