Rem
Rem $Header: dbmspbp.sql 16-sep-99.16:04:51 jmuller Exp $
Rem
Rem dbmspbp.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmspbp.sql - PL/SQL Profiler API
Rem
Rem    DESCRIPTION
Rem      This package specifies the PL/SQL API that can be used to gather
Rem      performance (profiler) and code coverage data for PL/SQL applications.
Rem
Rem    NOTES
Rem      This package must be installed as SYS.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jmuller     09/16/99 - Fix bug 708690: final pass
Rem    astocks     09/27/99 - Add rollup routines for general use
Rem    astocks     04/19/99 - Get runnumber early, add pause/resume
Rem    astocks     10/21/98 - Force owner into package name
Rem    ciyer       09/06/98 - PL/SQL Profiler package spec
Rem    ciyer       09/06/98 - Created
Rem

create or replace package sys.dbms_profiler
  authid current_user is

  ------------
  --  OVERVIEW
  --
  --  This package provides an API for gathering and persistently storing
  --  execution performance (profiler) and code coverage data for PL/SQL
  --  applications.
  --
  --  Improving application performance is an iterative process. Each
  --  iteration involves:
  --    1. Exercising the application with one or more benchmark tests with
  --       profiler data collection enabled.
  --    2. Analyzing the profiler data and identifying performance problems.
  --    3. Fixing the problems.
  --
  --  To support this process, the PL/SQL profiler supports the notion of
  --  a run. A run involves running the application through some benchmark
  --  test with profiler data collection enabled. The profiler user controls
  --  the beginning and end of the run by calling the API functions
  --  start_profiler, stop_profiler respectively.
  --
  --  A typical sequence of calls in a session may be:
  --    start profiler data collection in session
  --    execute PL/SQL code for which profiler/code coverage data is required
  --    stop profiler data collection
  --
  --  Note that stopping data collection flushes out the collected data
  --  as a side effect.
  --
  --  Profiler data is collected in data structures which last for the
  --  duration of the session. Users may call the flush_data function at
  --  intermediate points during the session to get incremental data and
  --  to free memory for allocated profiler data structures.
  --
  --  Note that some PL/SQL operations, such as the very first execution
  --  of a PL/SQL unit may involve I/O to catalog tables to load the byte
  --  code for the PL/SQL unit to be executed. Also some time may be spent
  --  executing package initialization code, the first time a package
  --  procedure/function is called. To avoid timing this overhead, it is
  --  recommended that the database be "warmed up" before collecting profile
  --  data. Warming up involves running the application once, without
  --  gathering profiler data.
  --
  --  The headers on the interface functions/procedures describe the
  --  meanings of arguments in greater detail.
  
  --  All facilites are available either as functions (which return a
  --  status, and will never raise an exception), or as procedures, (which
  --  will always raise an exception if they fail).
  --
  --------------
  -- ERROR CODES
  --   a 0 return value from any function denotes successful completion
  --   postive error returns are raised from the C implementation
  --   negative errors are reserved for errors from the PL/SQL package
  --   implementation (for example, ICD version mismatch).
  --

  success     constant binary_integer := 0;
  -- interface function/procedure called with an incorrect parameter
  error_param constant binary_integer := 1;
  -- data flush operation failed. check to see if the profiler tables have
  -- been created and there is adequate space.
  error_io    constant binary_integer := 2;

  -- mismatch between package and C implementation
  error_version constant binary_integer := -1;

  -- version history:
  --   1.0 - creation
  --
  major_version constant binary_integer := 2;
  minor_version constant binary_integer := 0;

  version_mismatch exception;
  pragma exception_init(version_mismatch, -6529);
  profiler_error exception ;
  pragma exception_init(profiler_error, -6528);


  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --

  --
  -- Start profiler data collection in session.
  --
  -- PARAMETERS:
  --   comment - each profiler run can be associated with a comment. For
  --             example, the comment could denote the name and version
  --             of the benchmark test that was used to collect data.
  --   comment1 - an additional comment
  --   run_number - each profiler run is uniquely identified by a generated
  --                run number. This allows the caller to determine what the
  --                generated run-number is, so that other tools may use it
  --                as a foreign key. On a successful return, a skeletal 
  --                record in the run_table exists. 

  function start_profiler(run_comment IN varchar2 := sysdate,
                          run_comment1 IN varchar2 := '',
                          run_number  OUT binary_integer)
    return binary_integer;

  procedure  start_profiler(run_comment IN varchar2 := sysdate,
                            run_comment1 IN varchar2 := '',
                            run_number  OUT binary_integer);
  function start_profiler(run_comment IN varchar2 := sysdate,
                          run_comment1 IN varchar2 := '')
    return binary_integer;
  procedure  start_profiler(run_comment IN varchar2 := sysdate,
                            run_comment1 IN varchar2 := '');

  --
  -- Stop profiler data collection in session. This function has the side
  -- effect of flushing data collected so far in the session and denotes
  -- the end of a run.
  --
  function stop_profiler return binary_integer;
  procedure stop_profiler;

  -- Pause profiler data collection, without terminating the run, or flushing
  -- data
  function pause_profiler return binary_integer;
  procedure pause_profiler;

  -- Resume a paused profiler run
  function resume_profiler return binary_integer;
  procedure resume_profiler;

  --
  -- Flushes profiler data collected in session. The data is flushed to
  -- database tables, which are expected to pre-exist. Use proftab.sql
  -- script to create the tables and other data structures required for
  -- persistently storing the profiler data.
  --
  function flush_data return binary_integer; 
  procedure flush_data;

  --
  -- get the version of this API
  --
  procedure get_version(major out binary_integer,
                        minor out binary_integer);

  --
  -- This function verifies that this version of the dbms_profiler package
  -- can work with the implementation in the database.
  --
  function internal_version_check return binary_integer;

  -- General purpose routines
  --
  -- compute the total time spent executing this unit - the sum of the
  -- time spent executing lines in this unit (for this run)
  --
  procedure rollup_unit(run_number IN number, unit IN number);

  -- rollup all units for the given run
  --
  procedure rollup_run(run_number IN number);
end dbms_profiler;
/

grant execute on sys.dbms_profiler to public;
drop public synonym dbms_profiler;
create public synonym dbms_profiler for sys.dbms_profiler;

