3.11.8 Using DBMS_PROFILER
3.11.8 Using DBMS_PROFILERThe DBMS_PROFILER package collects and stores performance information about the PL/pgSQL and SPL statements that are executed during a profiling session; you can review the performance information in the tables and views provided by the 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).Some of the lines in this function contain no executable code so the performance statistics for those lines will always contain zero values.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).It is important to note that when you start (or resume) the profiler, the profiler will only gather performance statistics for functions/procedures/triggers that start after the call to START_PROFILER (or RESUME_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:
Contains the performance counters and timers for each statement executed within the session.
Contains a summary of each run (aggregating the information found in PLSQL_PROFILER_RAWDATA).
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.