Home

PL/Scope and PL/SQL Hierarchical Profiler

Author: Saurabh Gupta Published on: 6th Nov 2011

Since the time Oracle 11g has been released way back in the year 2003, it has waved a new exploring instinct among the database users. Till now, I observe the professionals engaged trying their hands out with Oracle 11g new features.

Ads

Secure Files, Compound triggers, Result caching, virtual columns, invisible indexes and a lot more to mention have already made their stiff entry into application development and database upgrades. There are several other features which were overseen due to the mass reception of above features. So, let us widen the bag to watch out for some more exciting new features in Oracle 11g version but less talked about.

In the tutorial, I shall highlight two important features which had been the part of Oracle 11g release but less discussed. These features are PL/Scope and PLSQL Hierarchical Profiler. While PL/Scope deals with administrative activity on a program, the hierarchical profiler paves the way to identify the performance hit stages in a program.

PL/Scope: The Identifier Repository

PL/Scope is a compiler driven tool which tracks all the identifiers appearing in a PL/SQL source code. It captures the identifier information like its name, type, and usage in the PL/SQL program and stores it in the SYSAUX tablespace. Note that it can scan the identifiers in an unwrapped program only, but not in the wrapped PL/SQL code.

The PL/Scope feature can be enabled by setting the compilation parameter PLSCOPE_SETTINGS, which is disabled by default. The parameter value has to be set as IDENTIFIERS:ALL to allow the PL/Scope to record the identifier information. The compilation parameter PLSCOPE_SETTINGS can be set at database, session or object level.

When the parameter is set at system level, the identifier logging is available for all the programs created by all users and also in all sessions. For session level setting, the PL/Scope feature is activated only for the current session. If a PL/SQL program is compiled with PL/Scope enabled, the identifier information would be stored only for the specific program.

The identifier information is stored in data dictionary views [DBA | ALL | USER]_IDENTIFIERS. While a DBA can use all three flavors of identifier views, a user can access only USER_ IDENTIFIERS and ALL_ IDENTIFIERS.

Example Syntax [1]

At System or Session level: A DBA sets the compilation parameter at system and session level while a user can set the parameter at session level.

Sample Code

ALTER [SYSTEM | SESSION]
SET PLSCOPE_SETTINGS = ['IDENTIFIERS:ALL' | 'IDENTIFIERS:NONE']

Note that IDENTIFIERS:NONE is the default value for the parameter. It denotes the disabled status of the feature.

At object compilation level: The user can compile a program with different mode of PLSCOPE_SETTINGS.

Sample Code

ALTER [PROGRAM NAME] COMPILE
PLSCOPE_SETTINGS = ['IDENTIFIERS:ALL' | 'IDENTIFIERS:NONE']

The object level setting of PLSCOPE_SETTINGS can be queried from USER_PLSQL_OBJECT_SETTINGS dictionary view. The structure of the USER_PLSQL_OBJECT_SETTINGS is as below. Note the other compilation parameters of similar nature.

Sample Code

SQL> DESC USER_PLSQL_OBJECT_SETTINGS
Name NULL? Type
----------------------------------------- -------- -----------------
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
PLSQL_OPTIMIZE_LEVEL NUMBER
PLSQL_CODE_TYPE VARCHAR2(4000)
PLSQL_DEBUG VARCHAR2(4000)
PLSQL_WARNINGS VARCHAR2(4000)
NLS_LENGTH_SEMANTICS VARCHAR2(4000)
PLSQL_CCFLAGS VARCHAR2(4000)
PLSCOPE_SETTINGS VARCHAR2(4000)

Illustration

Let us examine a scenario where I shall create a PL/SQL function and generate a report of the identifiers used by the program.

As a pre requisite for demonstration, the DBA has enabled the PL/Scope feature at session level.

Sample Code

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'
/
Session altered.

Now, user SCOTT connects and creates a function F_CALCULATE_COMM which calculates new salary of a given employee by adding commission earned to his salary.

Sample Code

CREATE OR REPLACE FUNCTION F_CALCULATE_COMM (P_EMPNO NUMBER)
RETURN NUMBER
IS
CURSOR CUR_EMP IS
SELECT SAL, COMM
FROM EMPLOYEES
WHERE EMPNO=P_EMPNO;

Sample Code

L_COMM NUMBER;
L_SAL NUMBER;
L_NEW_SAL NUMBER;

Sample Code

BEGIN
OPEN CUR_EMP;
FETCH CUR_EMP INTO L_SAL, L_COMM;
CLOSE CUR_EMP;

Sample Code

L_NEW_SAL := L_SAL + L_COMM;

Sample Code

RETURN L_NEW_SAL;
END;

Sample Code

FUNCTION created.

The function has been compiled and created. The dictionary view USER_IDENTIFIERS stores the complete information about the identifiers used in the function. The information includes the identifier type, its name and usage.

Sample Code

SQL> SELECT NAME, TYPE, OBJECT_NAME, USAGE
FROM USER_IDENTIFIERS WHERE OBJECT_NAME='F_CALCULATE_COMM'
/
NAME TYPE OBJECT_NAME USAGE
-------------------- ------------------ -------------------- -----------
L_NEW_SAL VARIABLE F_CALCULATE_COMM REFERENCE
L_COMM VARIABLE F_CALCULATE_COMM REFERENCE
L_SAL VARIABLE F_CALCULATE_COMM REFERENCE
L_NEW_SAL VARIABLE F_CALCULATE_COMM ASSIGNMENT
CUR_EMP CURSOR F_CALCULATE_COMM CALL
L_COMM VARIABLE F_CALCULATE_COMM ASSIGNMENT
L_SAL VARIABLE F_CALCULATE_COMM ASSIGNMENT
CUR_EMP CURSOR F_CALCULATE_COMM CALL
CUR_EMP CURSOR F_CALCULATE_COMM CALL
NUMBER NUMBER DATATYPE F_CALCULATE_COMM REFERENCE
L_NEW_SAL VARIABLE F_CALCULATE_COMM DECLARATION
NUMBER NUMBER DATATYPE F_CALCULATE_COMM REFERENCE
L_SAL VARIABLE F_CALCULATE_COMM DECLARATION
NUMBER NUMBER DATATYPE F_CALCULATE_COMM REFERENCE
L_COMM VARIABLE F_CALCULATE_COMM DECLARATION
P_EMPNO FORMAL IN F_CALCULATE_COMM REFERENCE
CUR_EMP CURSOR F_CALCULATE_COMM DECLARATION
NUMBER NUMBER DATATYPE F_CALCULATE_COMM REFERENCE
NUMBER NUMBER DATATYPE F_CALCULATE_COMM REFERENCE
P_EMPNO FORMAL IN F_CALCULATE_COMM DECLARATION
F_CALCULATE_COMM FUNCTION F_CALCULATE_COMM DEFINITION
F_CALCULATE_COMM FUNCTION F_CALCULATE_COMM DECLARATION
22 rows selected.

Now, I shall generate the above output in a report format.

Sample Code

WITH v AS
(SELECT Line,
Col,
INITCAP(NAME) Name,
LOWER(TYPE) Type,
LOWER(USAGE) Usage,
USAGE_ID, USAGE_CONTEXT_ID
FROM USER_IDENTIFIERS
WHERE Object_Name = 'F_CALCULATE_COMM'
AND Object_Type = 'FUNCTION' )
SELECT LINE, RPAD(LPAD(' ', 2*(Level-1)) ||Name, 20, '.')||' '||
RPAD(Type, 20)|| RPAD(Usage, 20)
IDENTIFIER_USAGE_CONTEXTS
FROM v
START WITH USAGE_CONTEXT_ID = 0
CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
ORDER SIBLINGS BY Line, Col
/
LINE IDENTIFIER_USAGE_CONTEXTS
---------- -------------------------------------------------------------
1 F_Calculate_Comm.... FUNCTION declaration
1 F_Calculate_Comm.. FUNCTION definition
1 P_Empno......... formal IN declaration
1 Number........ number datatype reference
2 Number.......... number datatype reference
4 Cur_Emp......... cursor declaration
7 P_Empno....... formal IN reference
9 L_Comm.......... variable declaration
9 Number........ number datatype reference
10 L_Sal........... variable declaration
10 Number........ number datatype reference
11 L_New_Sal....... variable declaration
11 Number........ number datatype reference
14 Cur_Emp......... cursor call
15 Cur_Emp......... cursor call
15 L_Sal......... variable assignment
15 L_Comm........ variable assignment
16 Cur_Emp......... cursor call
18 L_New_Sal....... variable assignment
18 L_Sal......... variable reference
18 L_Comm........ variable reference
20 L_New_Sal....... variable reference
22 rows selected.

The object level compilation parameter settings can be queried from USER_PLSQL_OBJECT_SETTINGS view.

Sample Code

SQL> SELECT PLSCOPE_SETTINGS
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'F_CALCULATE_COMM';
PLSCOPE_SETTINGS
-------------------------
IDENTIFIERS:ALL

The function can be re compiled with modified compilation settings. Note that the modified compilation parameters effect compilation of the current object only. The object level setting is preferable over the session or system level setting of PLSCOPE_SETTINGS.

Every time a program is compiled in Oracle without any compilation parameter, the server inherits the current session level compilation settings. If session level is not available, it searches for database level settings.

The ALTER FUNCTION statement below compiles the function F_CALCULATE_COMM with modified PLSCOPE_SETTINGS mode.

Sample Code

SQL> ALTER FUNCTION F_CALCULATE_COMM
COMPILE PLSCOPE_SETTINGS='IDENTIFIERS:NONE';

Function altered.

Querying the USER_PLSQL_OBJECT_SETTINGS again to check the value of PLSCOPE_SETTINGS for F_CALCULATE_COMM

Sample Code

SQL> SELECT PLSCOPE_SETTINGS
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'F_CALCULATE_COMM';
PLSCOPE_SETTINGS
-------------------------
IDENTIFIERS:NONE

PL/SQL Hierarchical Profiler: Build up the Execution Profile

Profiling is a method employed by the developers to analyze the performance of a blog. The profiler report shows the time taken at each stage in the program. In earlier releases of Oracle, DBMS_PROFILER served the purpose well until the need of dynamic execution program profiler was required.

Oracle 11g R1 introduced the PL/SQL hierarchical profiler to generate dynamic execution program profile. The new profiler can transparently distinguish between the time taken by SQL engine and PL/SQL engine separately. In addition, the hierarchical profiler can analyze the profiled data and generate several reports of different viewpoints. Report generation in HTML format was brought in action and more importantly, the profiled data storage within the database was also introduced. The profiled data would no more be the property of OS file system only, but can be retained in the database itself. This can be of great help while comparing the profiles of same program in different versions or schemas.

The DBMS_HPROF package

Oracle 11g also unpacks DBMS_HPROF utility package to handle all the features associated with the hierarchical profiling.

The PL/SQL hierarchical profiler consists of two subcomponents

Raw Data Collector – This component simply scans the program and prepares the execution profile in raw format. The profile is stored in an OS level text file.
Analyzer – The raw profiler data, once analyzed appears in readable format. The component loads the raw profiler data into database tables. The profiler uses the table data to prepare various types of reports.

The description of the DBMS_HPROF subprograms is as below.

Subprogram	Description
ANALYZE Function	Analyzes the raw profiler output and produces hierarchical profiler information in database tables
START_PROFILING Procedure	Starts hierarchical profiler data collection in the user's session
STOP_PROFILING Procedure	Stops profiler data collection in the user's session

The SYS user owns the DBMS_HPROF package. The SYSDBA must grant EXECUTE privilege on the package to the users seeking its services.

Sample Code

SYSDBA> GRANT EXECUTE ON DBMS_HPROF TO SCOTT;
GRANT succeeded.

The profiler tables are not created by default. The DBA must execute the script dbmshptab.sql from ORACLE_HOMERDBMSADMIN folder to create the tables and sequences. The script creates three tables dbmshp_function_info, dbmshp_parent_child_info, and dbmshp_runs.

The tables serve the below purposes

• DBMSHP_RUNS – Maintains flat information about each command executed during profiling.
• DBMSHP_FUNCTION_INFO – Contains information about the profiled function.
• DBMSHP_PARENT_CHILD_INFO – Contains parent child profiler information

The DBA must grant SELECT privilege on the above tables to the concerned user. In addition, the DBA must have a valid directory (here PROFILER_REP) pointing to a valid OS location on a workstation in the server network.

Now, we shall try to profile out last function i.e. F_CALCULATE_COMM.

Sample Code

DECLARE
L_NEW_SAL NUMBER;
BEGIN
DBMS_HPROF.START_PROFILING ('PROFILER_REP', 'F_CALCULATE_COMM.TXT');
FOR I IN (SELECT EMPNO FROM EMPLOYEES)
LOOP
L_NEW_SAL := F_CALCULATE_COMM (I.EMPNO);
DBMS_OUTPUT.PUT_LINE(I.EMPNO||':'||L_NEW_SAL);
END LOOP;
DBMS_HPROF.STOP_PROFILING;
END;
/

Sample Code

PL/SQL procedure successfully completed.

Now, a profiler text file is generated at the directory location PROFILER_REP. The raw profiler data looks as below

Sample Code

P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C SQL."".""."__sql_fetch_line5" #5
P#X 812
P#R
P#C PLSQL."SCOTT"."F_CALCULATE_COMM"::8."F_CALCULATE_COMM"#fac892c7867b54c6 #1
P#X 3
P#C PLSQL."SCOTT"."F_CALCULATE_COMM"::8."F_CALCULATE_COMM.CUR_EMP"#980980e97e42f8ec #4
P#X 12
P#C SQL."SCOTT"."F_CALCULATE_COMM"::8."__static_sql_exec_line5" #5
P#X 14944
...

The above profiler data is raw and difficult to understand but still some idea can be drawn out from it with the help of below guidelines and conventions.

P#! Denotes START, STOP or any other comment of the profiler
P#V shares the PLSHPROF banner with version number
P#C implies a call to a subprogram (call event)
P#R implies the return from a subprogram (return event)
P#X shows the elapsed time between preceding and following events

For demonstration purpose, I shall randomly pick up some lines from above raw profiler output, try to interpret them and frame them in a statement.

Sample Code

P#C SQL."".""."__sql_fetch_line5" #5
P#X 812
P#R

The above three lines from the profiler means that an SQL has been invoked at line 5. It took 812 microseconds for execution and then returned back.

Till now, we saw the efficiency of Raw Collector Component of Hierarchical profiler. Now, we shall see how Analyzer component of hierarchical profiler makes it different from other profiler tools.

Connect as SYSDBA and execute the PL/SQL block below to analyze the raw profiler data from a text file

Sample Code

Conn / AS SYSDBA
Connected.

Sample Code

DECLARE
l_runid NUMBER;
BEGIN
l_runid := DBMS_HPROF.analyze (
location => 'PROFILER_REP',
FILENAME => 'F_CALCULATE_COMM.TXT',
run_comment => 'Analyzing the execution of F_CALCULATE_COMM.');
DBMS_OUTPUT.put_line('l_runid=' || l_runid);
END;

Sample Code

PL/SQL procedure successfully completed.

Now, the SCOTT user can find the profiler output stored in database tables.

The SQL below queries DBMSHP_RUNS table to get the basic information about the profile analysis which includes the analyzer comment and total execution time.

Sample Code

SELECT * FROM SYS.DBMSHP_RUNS WHERE RUNID = 3
/

Sample Code

RUNID RUN_TIMESTAMP TOTAL_ELAPSED_TIME RUN_COMMENT
---------- ------------------------------ ------------------ ---------------------------------------------
3 01-NOV-11 11.54.33.359000 PM 27926 Analyzing the execution of F_CALCULATE_COMM.

The SQL below queries the DBMSHP_FUNCTION_INFO to get the line by line execution information of the program.

Sample Code

SELECT OWNER,
MODULE,
TYPE,
FUNCTION,
LINE#,
NAMESPACE,
CALLS,
FUNCTION_ELAPSED_TIME TIME
FROM SYS.DBMSHP_FUNCTION_INFO
WHERE RUNID = 3/

Generating the profiler reports in HTML formats

Now, I shall demonstrate the most distinguishing feature of hierarchical profiler i.e. Generation of HTML reports. This feature is a unique one in itself as it doesn’t requires any UI or development tool to generate the HTML reports. A single command line utility PLSHPROF efficiently scans the raw profiler data and creates the HTML links with proper navigational directions.

The plshprof utility generates the HTML reports for single profiler text file.

Example Syntax [3]

Sample Code

C:Profiler path> plshprof –output [HTML FILE] [RAW PROFILER DATA]

Example Code [1]

Change the directory to the one where raw profiler data has been generated

Sample Code

C:>cd c:profiler

Now run the plshprof utility and specify the output file.

Sample Code

C:profiler>plshprof -output F_CALCULATE_COMM F_CALCULATE_COMM.TXT
PLSHPROF: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
[7 symbols processed]
[Report written TO 'F_CALCULATE_COMM.html']

Once the generation of HTML reports has been successfully completed, I can find the 16 HTML files in the same OS location. The file with no suffix after given output name is the main Report Index Interface. It contains the navigational links to all other categories of reports.

Now, I shall open the report index interface as below. Notice the navigational links to all other reports.

The header lists all types of views generated out of the raw profiler data. It contains the summary information and navigational links to all the summary reports.

We shall discuss the important reports.

Sample Report 1

Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs)

The report provides the flat view of raw profiler data. It includes total call count, self time, subtree time and descendants of each function.

Sample Report 2

Function Elapsed Time (microsecs) Data sorted by Total Function Elapsed Time (microsecs)

Sample Report 3

Namespace Elapsed Time (microsecs) Data sorted by Namespace

The report provides the distribution of time spent by PL/SQL engine and SQL engine separately. The SQL and PLSQL are the two namespace categories available for a block. It is very useful in reducing the disk I/O and hence enhancing the block performance. The net sum of the distribution is always 100%.

Conclusion

Ads

The above two features are essential activity in a product development life cycle. While the profiler is majorly used by developers to observe the server control movement in their program block, the PL/Scope feature is used by the DBA to keep note of the identifiers in the application along with their usage. The hierarchical profiler can perform intelligent analysis and produces self explanatory reports out of a raw output.

Read Next: Oracle Partitioning Overview

This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial