Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 6.3 Schema-Level Event TriggersChapter 7Next: 7.2 DBMS_TRACE: Providing a PL/SQL Trace Facility
 

7. New and Enhanced Built-in Packages in Oracle8i

Contents:
DBMS_PROFILER: Providing Code Profiling
DBMS_TRACE: Providing a PL/SQL Trace Facility
DBMS_RLS: Implementing Fine-Grained Access Control
UTL_COLL: Using Collection Locators
LOB Enhancements
New DBMS_AQ and DBMS_AQADM Features
New DBMS_UTILITY Features

Oracle has added a number of new built-in packages in Oracle8i. Many of these packages offer very specialized capabilities for replication, database administration, and online analytical processing (OLAP). Other packages will play a more crucial role for PL/SQL developers in the brave new world of Oracle8i. We'll introduce you to the capabilities of the packages in this second category (and existing packages, such as DBMS_UTILITY, that have been enhanced) in this chapter. More comprehensive, reference-oriented coverage of the packages will be included in the second edition of Oracle Built-in Packages (O'Reilly & Associates, expected in 2000). In addition to the new packages, Oracle8i includes a new built-in conversion function, TO_LOB, which is also described in this chapter.

In addition, a number of other new and modified packages are covered throughout the book in their appropriate chapters, as follows:

Table 7.1 provides a complete list of packages added in Oracle8i.


Table 7.1: New Built-in Packages in Oracle8i

Package Name

Description

DBMS_INDEXING

Contains specifications for packages and types used in DBMS extensibility infrastructure (indexing, optimization, etc.).

DBMS_JAVA

Gives you the ability to modify the behavior of the Aurora Java Virtual Machine ( JVM) in Oracle. You can enable output (meaning that Java's System.out.println will act like DBMS_OUTPUT.PUT_LINE), set compiler and debugger options, and more.

DBMS_JAVA_TEST

Interface to Aurora JVM to let you easily test methods (functions) defined in classes that you have loaded into Oracle.

DBMS_LOGMNR

DBMS_LOGMNR_D

Interact with and support a variety of operations in the new Oracle utility LogMiner, which allows you to read information contained in online and archived redo logs based on selection criteria.

DBMS_PROFILER

Accesses performance and code coverage analysis of your PL/SQL application.

DBMS_REPAIR

Offers a set of procedures to facilitate data corruption repair.

DBMS_REPCAT_FLA DBMS_REPCAT_FLA_MAS DBMS_REPCAT_FLA_UTL

New replication packages.

DBMS_REPCAT_RGT

Offers an API to control the maintenance and definition of refresh group templates.

DBMS_RESOURCE_MANAGER

An API to the Database Resource Manager that allows you to maintain resource management plans, consumer groups, and plan directives.

DBMS_RESOURCE_MANAGER_PRIVS

Maintains privileges associated with resource consumer groups.

DBMS_RLS

Offers an interface to the fine-grained access control administrative features of Oracle8i; only available with the Enterprise Edition.

DBMS_RULE

DBMS_RULE_ADM

DBMS_RULE_EXIMP

Administer export/import information in the Rules Engine Administrator.

DBMS_SPACE_ADMIN

Provides tablespace and segment space administration that is not available through the standard SQL statements.

DBMS_STATS

Provides a PL/SQL-based mechanism to allow users to view and modify optimizer statistics gathered on database objects.

DBMS_SUMMARY

Manages and refreshes table-based summaries.

DBMS_TRACE

Allows PL/SQL developers to trace the execution of stored PL/SQL functions, procedures, and exceptions.

DBMS_TTS

Contains procedures and functions supporting the Pluggable Tablespace feature. These programs are mostly called by the import/export utilities.

OUTLN_PKG

Contains a functional interface for procedures and functions that are associated with the management of stored outlines.

UTL_COLL

Allows PL/SQL programs to use collection locators in order to perform queries and updates.

7.1 DBMS_PROFILER: Providing Code Profiling

In Oracle8i, Oracle adds a new package, DBMS_PROFILER, to facilitate performance and code coverage analysis of your PL/SQL application. Use this package to define one or more runs of your code and collect a wide range of information about the code that is executed in that run.

The API for this package is very simple. You start the profiler, run your code, and stop the profiler. The PL/SQL engine will have populated up to three different tables with the performance and code coverage information. You can then use SQL -- either your own queries or one of the reports offered by Oracle -- to examine the results of the run.

7.1.1 DBMS_PROFILER Programs

The DBMS_PROFILER package contains the two functions listed in Table 7.2.


Table 7.2: DBMS_PROFILER Programs

Program

Description

DBMS_PROFILER.START_PROFILER function

Starts profiling in the current connection. You can provide an optional string to give a name to the profiling session.

DBMS_PROFILER.STOP_PROFILER function

Ends profiling in the current connection.

7.1.2 Installing DBMS_PROFILER

The DBMS_PROFILER package may not have been installed automatically for you when the database was set up. In this case, you will need to install the package specification and body. After that, you will want to create profiler tables and packages to help you analyze the output (stored in the profiler tables) more effectively.

Table 7.3 shows all of the files related to DBMS_PROFILER that you will probably want to run. You will find most of these files on Windows NT in one of the following directories (the exceptions are the demo files profrep.sql and profsum.sql), depending on how you installed the database:

\Oracle\Ora81\Rdbms\Admin\
\Ora81\Rdbms\Admin\

Regardless of the higher-level directory structure, these files will always be found in the Rdbms\Admin subdirectory under the Oracle 8.1 home directory.


Table 7.3: DBMS_PROFILER Files

File

Description

dbmspbp.sql

DBMS_PROFILER package specification; this package should be created automatically when you install the Oracle8i database. You may want to examine this file to read the documentation.

prvtpbp.plb

DBMS_PROFILER package body/library definition. This is a wrapped file and should be executed automatically when you install the Oracle8i database.

profload.sql

Orchestrates the loading of dbmspbp.sql and prvtpbp.plb. This script must be run under a DBA account.

proftab.sql

Script to create the profiling tables. When you profile a program, data is written to one or more of these tables:

  • plsql_profiler_runs

  • plsql_profiler_units

  • plsql_profiler_data

profrep.sql

Creates a set of views and a reporting package named prof_report_utilities that offers an API to more easily extract reports from the profiling tables. Found under \plsql\demo.

profsum.sql

A set of ad hoc queries and calls to programs in the prof_report_utilities package. Don't run the whole file unless you have a small volume of data in your profiling tables. Instead, go through the file and extract those queries that you want to run. Found under \plsql\demo.

You can define the profiler tables in each individual schema developers want to profile. You can also define the profiler tables in a central schema, to be shared among multiple developers. In this case, you need to grant full access to all of the tables and to the sequence that defines the run number. You will also need to create public synonyms.

The profiler will not profile a PL/SQL block unless the current user has CREATE access on the block.

7.1.3 DBMS_PROFILER Example

It's certainly easy enough to use the PL/SQL profiler. Here's an example of the code you would execute:

/* Filename on companion disk: profiler.sql */
BEGIN
   DBMS_OUTPUT.PUT_LINE (
      DBMS_PROFILER.START_PROFILER (
         'showemps ' || 
         TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS')
         )
      );
   showemps;
   DBMS_OUTPUT.PUT_LINE (
      DBMS_PROFILER.STOP_PROFILER);
END;
/

If you do not pass an argument to START_PROFILER, then the "name" of the profile run is SYSDATE. In the example just given, I want to record both the name of the program I am running and the date-time stamp so that I can distinguish this run from others for the same program.

7.1.4 Profiler Return Codes

Both START_PROFILER and END_PROFILER are functions that return a status code. A value of 0 means that the program was called successfully. A nonzero return code indicates a problem, and may be one of the values listed in Table 7.4.


Table 7.4: DBMS_PROFILER Return Codes

Database Constant

Value

Description

DBMS_PROFILER.SUCCESS

0

No problem!

DBMS_PROFILER.ERROR_PARM

1

A subprogram was called with an incorrect parameter.

DBMS_PROFILER.ERROR_IO

2

An attempt to flush profile data to the tables failed. Make sure the tables are present and accessible and have sufficient space for the inserts.

DBMS_PROFILER.ERROR_VERSION

-1

The engine has detected a mismatch between the profiler package and the database version. The only possible recovery is to install the correct version of the package.

Consider yourself warned: unless you are running a very simple application, the profiler will write thousands of rows of data to its tables. To make it easier for you to manage all this information, I have created the following scripts, located on the companion disk:

proftrunc.sql

Truncates all three profiling tables

profdel.sql

Deletes all rows from the three profiling tables for the specified run number


Previous: 6.3 Schema-Level Event TriggersOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 7.2 DBMS_TRACE: Providing a PL/SQL Trace Facility
6.3 Schema-Level Event TriggersBook Index7.2 DBMS_TRACE: Providing a PL/SQL Trace Facility

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference