To begin collecting data via DBMS_PROFILER, a call is made to either the START_PROFILER Function or the START_PROFILER Procedure. The Package DBMS_PROFILER contains a set of Functions and Procedures all called START_PROFILER. This is an example of Overloading a Package Function or Procedure. Within the PL/SQL Package Specification, the routines with the same name must conform to certain rules. First delineation is Function or Procedure. A Function returns a value and must be called as part of an expression. A Procedure can be called as an executable, and does not return a value although OUT or IN OUT parameters allow retrieval of values from the execution of the Procedure. If the PL/SQL Package routine contains the same number of parameter calls, the datatypes of the parameters must be different. The last delineation is the number of parameters in the specification. The Function or Procedure call with the same name requires a different number of parameters as the last method of overloading. In the examples of the DBMS_PROFILER.START_PROFILER specifications, we see that the IN type parameters have default values (run_comment IN VARCHAR2 := sysdate, run_comment1 IN VARCHAR2 :=”,). The OUT parameter in the overloaded Function and overloaded Procedure will return the RUN_NUMBER
| DBMS_PROFILER.START_PROFILER Specifications |
|
DBMS_PROFILER.START_PROFILER ( run_comment IN VARCHAR2 := sysdate, run_comment1 IN VARCHAR2 :=”, run_number OUT BINARY_INTEGER) RETURN BINARY_INTEGER; [start_profiler_func2.sql] |
|
DBMS_PROFILER.START_PROFILER ( run_comment IN VARCHAR2 := sysdate, run_comment1 IN VARCHAR2 :=”) RETURN BINARY_INTEGER; [start_profiler_func1.sql] |
|
DBMS_PROFILER.START_PROFILER ( run_comment IN VARCHAR2 := sysdate, run_comment1 IN VARCHAR2 :=”, run_number OUT BINARY_INTEGER); [start_profiler_proc2.sql] |
|
DBMS_PROFILER.START_PROFILER ( run_comment IN VARCHAR2 := sysdate, run_comment1 IN VARCHAR2 :=”); [start_profiler_proc1.sql] |
Now on to starting and stopping a session. Below is the code to start the PL/SQL Profiler and using the OUT type parameter report back the RUNID number assigned by the Profiler Sequence.
Simple example code below. Just a simple LOOP that after counting 1,000,000 times, it prints out the number. (10 times since the LOOP runs 10,000,000 times.)
The DBMS_PROFILER.STOP_PROFILER routine is also overloaded with both a Function and a Procedure. Stopping the PL/SQL Profiler can be done with either. (Note there is no facility for specifying a RUNID to terminate. One can only have a single Profiler collection occurring in the User Session.
With that the PL/SQL Profiler Run has been accomplished and the data persisted in the PLSQL_PROFILER Tables.
