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.
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 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.
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.
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.
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.
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.
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.
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.
Now, I shall generate the above output in a report format.
The object level compilation parameter settings can be queried from USER_PLSQL_OBJECT_SETTINGS view.
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.
Querying the USER_PLSQL_OBJECT_SETTINGS again to check the value of PLSCOPE_SETTINGS for F_CALCULATE_COMM
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.
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
The description of the DBMS_HPROF subprograms is as below.
Analyzes the raw profiler output and produces hierarchical profiler information in database tables
Starts hierarchical profiler data collection in the user's session
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.
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.
Now, a profiler text file is generated at the directory location PROFILER_REP. The raw profiler data looks as below
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.
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
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.
The SQL below queries the DBMSHP_FUNCTION_INFO to get the line by line execution information of the program.
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.
Change the directory to the one where raw profiler data has been generated
Now run the plshprof utility and specify the output file.
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.
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.
Function Elapsed Time (microsecs) Data sorted by Total Function Elapsed Time (microsecs)
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%.
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.
Oracle 11g Tutorial