Oracle has added a number of new built-in packages in Oracle8 i . 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 Oracle8 i . 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, Oracle8 i 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 Oracle8 i .
In Oracle8 i , 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.
The DBMS_PROFILER package contains the two functions listed in Table 7.2 .
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:
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.
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.
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.
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 .
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:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.