Monitor PL/SQL

View PL/SQL within the Oracle System Global Area (SGA)

When monitoring the activity of a PL/SQL execution within Oracle, AppDynamics for Databases exposes the costly sections of code within the PL/SQL body rather than exposing the PL/SQL procedure name.

For example, consider the simple stored procedure below, which consists of a single SELECT query.

CREATE OR REPLACE PROCEDURE procExample1 
( prc out sys_refcursor ) 
IS 
BEGIN 
   open prc for SELECT col1, count(*) FROM bigtab group by col1; 
END; 
/

This can be executed within Oracle via a command similar to the following:

var rc refcursor execute procExample1(:rc)

When AppDynamics for Databases monitors the execution of this stored procedure, the SELECT query represents the resource consumption cost of the procedure, therefore the SELECT query is exposed on the AppDynamics for Databases Activity page.

To find out the PL/SQL procedure name associated with the SELECT query you must enable the SQL automatic grouping functionality within AppDynamics for Databases as documented in SQL Group Management for Oracle Only. Once done, the SQL Group Reports provides details of the PL/SQL procedures.