Tools for PL/SQL Performance
One of the basic Oracle Development Tools is the PL/SQL Programming Language. Over the years there have been many rumors of PL/SQL being replaced (for example, Oracle 8i when the Java Virtual Machine was introduced into the RDBMS engine). However, what is often neglected in these rumors, is the mere fact that many of the RDBMS New Features are developed and maintained by Oracle using PL/SQL. One of the first this author recalls is Resource Manager, managed through DBMS_RESOURCE_MANAGER.
As the basic Oracle Programming Language, PL/SQL has many utilities for interacting with tables, formatting and displaying data, as well as providing a means of adding iterative processing and conditional processing to the Structured Query Language (SQL). These are important aspects of PL/SQL in the Oracle RDBMS.
Performance Optimization of PL/SQL should be a vital aspect of Database Applications in Oracle. The Oracle RDBMS provides three tools for PL/SQL Optimization.
|
TOOL |
API |
DESCRIPTION |
REPORTING |
||
| DBMS_PROFILER | Profiler API | Computes the time that the PL/SQL program spends at each line and in each subprogram. User must have CREATE privileges on the units to be profiled. |
Saves runtime statistics in database tables, which can be queried. | ||
| DBMS_TRACE | Trace API | Traces the order in which subprograms run. Specify the subprograms to trace and the tracing level. | Saves runtime statistics in database tables, which can be queried. | ||
| DBMS_HPROF | PL/SQL Hierarchical Profiler | Reports the dynamic execution program profile of the PL/SQL program, organized by subprogram invocations. Accounts for SQL and PL/SQL execution times separately. Requires no special source or compile-time preparation. |
Generates reports in HTML. Provides the option of storing results in relational format in database tables for custom report generation. | ||
This series of posts is designed to take one through setup and use of these tools.
