Table of Contents Previous Next


3 Built-In Packages : 3.11 DBMS_PROFILER : 3.11.8 Using DBMS_PROFILER

DBMS_PROFILER works by recording a set of performance-related counters and timers for each line of PL/pgSQL or SPL statement that executes within a profiling session. The counters and timers are stored in a table named SYS.PLSQL_PROFILER_DATA. When you complete a profiling session, DBMS_PROFILER will write a row to the performance statistics table for each line of PL/pgSQL or SPL code that executed within the session. For example, if you execute the following function:
DBMS_PROFILER adds one PLSQL_PROFILER_DATA entry for each line of code within the getBalance() function (including blank lines and comments). The entry corresponding to the SELECT statement executed exactly one time; and required a very small amount of time to execute. On the other hand, the entry corresponding to the RAISE INFO statement executed once or not at all (depending on the value for the balance column).
To start a profiling session, invoke the DBMS_PROFILER.START_PROFILER function (or procedure). Once you've invoked START_PROFILER, Advanced Server will profile every PL/pgSQL or SPL function, procedure, trigger, or anonymous block that your session executes until you either stop or pause the profiler (by calling STOP_PROFILER or PAUSE_PROFILER).
While the profiler is active, Advanced Server records a large set of timers and counters in memory; when you invoke the STOP_PROFILER (or FLUSH_DATA) function/procedure, DBMS_PROFILER writes those timers and counters to a set of three tables:
SYS.PLSQL_PROFILER_RAWDATA
Contains the performance counters and timers for each statement executed within the session.
SYS.PLSQL_PROFILER_RUNS
Contains a summary of each run (aggregating the information found in PLSQL_PROFILER_RAWDATA).
SYS.PLSQL_PROFILER_UNITS
Contains a summary of each code unit (function, procedure, trigger, or anonymous block) executed within a session.
In addition, DBMS_PROFILER defines a view, SYS.PLSQL_PROFILER_DATA, which contains a subset of the PLSQL_PROFILER_RAWDATA table.
Please note that a non-superuser may gather profiling information, but may not view that profiling information unless a superuser grants specific privileges on the profiling tables (stored in the SYS schema). This permits a non-privileged user to gather performance statistics without exposing information that the administrator may want to keep secret.

3 Built-In Packages : 3.11 DBMS_PROFILER : 3.11.8 Using DBMS_PROFILER

Table of Contents Previous Next