Submit a ticketCall us

Announcing NPM 12.2
With NPM 12.2 you can monitor your Cisco ASA firewalls, to monitor VPN tunnels for basic visibility and troubleshooting tunnels. NPM 12.2 also uses the SolarWinds Orion Installer so you can easily install and upgrade one or more Orion Platform products simultaneously.
See new features and improvements.

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

Use DBMS APPLICATION INFO for more detailed wait analysis

Table of contents
No headers
Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 55 Votes: 0 Revisions: 11

 

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
19:19, 22 Jun 2016

Tags

Classifications

Public