Skip to the content.

DBA Profiler Data – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Database Administration · all Database Administration reports

Overview

Excel version of Oracle’s dbms_profiler PLSQL performance analysis, see Oracle note: Using DBMS_PROFILER (KB85737) https://support.oracle.com/support/?kmContentId=97270

PL/SQL units that have been compiled in NATIVE mode cannot be profiled using the DBMS_PROFILER package. To gather information using DBMS_PROFILER, you must ensure that the PL/SQL code is INTERPRETED.” Before compilation of the profiled code, execute: alter session set plsql_code_type=interpreted;

To start and stop profiling code, use the following commands (see use https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_profil.htm#i1000047): dbms_profiler.start_profiler(optional run_comment); dbms_profiler.stop_profiler;

To purge and reset the profiler data, execute the following as sys: truncate table sys.plsql_profiler_data; truncate table sys.plsql_profiler_units; truncate table sys.plsql_profiler_runs; alter sequence plsql_profiler_runnumber restart start with 1;

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Run Id, Module, Time From, Percentage From, Line Text Contains, Line Number From, Line Number To, Time Unit

Oracle EBS Tables Used

dba_source, plsql_profiler_runs

Report Categories

Enginatics, R12 only

DBA Sessions, DBA ORDS Configuration Validation, DBA Hierarchical Profiler Data, DBA AWR SQL Performance Summary, DBA AWR Blocking Session Summary, DBA AWR Active Session History, DBA SGA Active Session History

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output DBA Profiler Data 14-Dec-2025 081549.xlsx
Blitz Report™ XML Import DBA_Profiler_Data.xml
SQL Source DBA Profiler Data.sql
Full Report on Enginatics www.enginatics.com/reports/dba-profiler-data/

Case Study & Technical Analysis: DBA Profiler Data

Executive Summary

This report presents the output of Oracle’s DBMS_PROFILER PL/SQL performance profiler in Excel. It shows each profiled run with its total elapsed time, the PL/SQL time, and for every program unit and source line the number of calls, the time spent and the share of the run. The source text of each line is retrieved from the data dictionary, so the performance analyst sees the code next to its cost. Database administrators and Oracle EBS technical consultants use it to find the custom or seeded PL/SQL that dominates a slow process. The report runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. PLSQL_PROFILER_RUNS joins to PLSQL_PROFILER_UNITS on run id.
  2. PLSQL_PROFILER_UNITS joins to PLSQL_PROFILER_DATA on run id and unit number.
  3. Each line’s text is returned by a function that matches the unit type, owner, name and line number in DBA_SOURCE.
  4. Percentage and PL/SQL time are computed as analytic windows partitioned by run id over the line rows.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is my PL/SQL profile empty? A: DBMS_PROFILER cannot profile units compiled in native mode. The code must be compiled as interpreted before profiling starts.

Q: How do I start and stop profiling? A: Call the start and stop procedures of the DBMS_PROFILER package around the code under test, as described in Oracle note KB85737.

Q: How do I clear old profiler data? A: The profiler tables are truncated and the run number sequence is restarted by a SYS user, as the report’s description states.


© Enginatics GmbH