Hierarchical Profiler - DBMS_HPROF

Dbms_hprof package reports dynamic execution profile of the plsql program organised by subprogram calls. This seperately accounts plsql and sql calls. Also provides subprogram level execution summary. It has two components - data collection and  analyzer. Data collection has apis to turn hierarchical profiling on and off. It produces raw profiler output. Analyzer processes raw profiler output and store the data to database tables. Hierarchical profiler is aided by plshprof utility which takes the raw profiler output as input and creates html reports.

Initial setup:

By default dbms_hprof is installed in oracle 11g. One can check if it is installed by running

Desc dbms_hprof

If it is not installed, run the script dbmshptab.sql  (inside rdbms/admin folder in oracle home path) in the schema one need to profile the units.

conn user/password

@/home/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/dbmshptab.sql

This script will create three tables :

- dbmshp_function_info

- dbmshp_parent_child_info

- dbmshp_runs

To do hierarchical profiling using dbms_hprof, the schema need to have execute privilege on DBMS_HPROF package and write access to a directory object referred in start_profiling procedure.

Eg: Giving access to HR schema:

GRANT EXECUTE ON dbms_hprof  TO HR;

CREATE DIRECTORY PLSQL_HPROF AS '/home/oracle/plsql_hprof';

GRANT ALL ON DIRECTORY PLSQL_HPROF TO HR;

Process of Hierarchical profiling involves starting the profiler, executing plsql program code and  stopping profiler. To start profiler, dbms_hprof offers start_profiling procedure. To stop stop_profiling procedure is used.

Example

Create or replace procedure hprof1 is

Begin

Calc_days(sysdate - 1000);

End;

begin

dbms_hprof.start_profiling( location => 'PLSQL_HPROF', filename=>'Run1.trc');

hprof1;

dbms_hprof.stop_profiling;

end;

This will generate raw profiler data in file Run1.trc in the directory referred by PLSQL_HPROF directory object. Raw profiler output is like:

P#V PLSHPROF Internal Version 1.0

P#! PL/SQL Timer Started

P#C PLSQL."HR"."CALC_DAYS"::7."CALC_DAYS"#c0e1380d4054eb50 #1

P#X 191

P#C PLSQL."SYS"."DBMS_OUTPUT"::11."PUT_LINE"#5892e4d73b579470 #109

P#X 2

P#R

P#X 6

P#C PLSQL."SYS"."DBMS_OUTPUT"::11."PUT_LINE"#5892e4d73b579470 #109

P#X 1

P#R

P#X 5

P#C PLSQL."SYS"."DBMS_OUTPUT"::11."PUT_LINE"#5892e4d73b579470 #109

P#X 1

Since the raw profiler data is hard to decipher, one needs to analyze it using analyze function. It takes trace file location and name as input and returns runid. It writes the profiled data into the database tables.

Example:

 declare

 runid number;

 begin

 runid := dbms_hprof.analyze(location=> 'PLSQL_HPROF' , filename=> 'Run1.trc');

 dbms_output.put_line(' Run ID = ' ||runid);

 end;

One can query the database tables for finding information on profiling data using below queries:

Below sql gives runid:

SELECT runid,  run_timestamp,total_elapsed_time,run_comment FROM   dbmshp_runs ORDER BY runid;

From below sql we can find symbolid of our top level procedure call.

SELECT symbolid, owner, module, type, function FROM   dbmshp_function_info WHERE  runid = 1  ORDER BY symbolid;

Using symbolid, we can get the hierarchical information using below:

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name, fi.function, pci.subtree_elapsed_time, pci.function_elapsed_time, pci.calls FROM   dbmshp_parent_child_info pci  JOIN dbmshp_function_info fi ON pci.runid = fi.runid AND pci.childsymid = fi.symbolid  WHERE  pci.runid = 1

CONNECT BY PRIOR childsymid = parentsymid

START WITH pci.parentsymid = 2;

PLSHPROF utility:

plshprof utility takes a trace file and generates various HTML reports.

Example:

$plshprof -output run1 run1.trc

This generates a set of files with name starting with run1. Also one can generate difference between two runs of hierarchical profiling by supplying raw profiler outputs to plshprof utility.

Example:

$plshprof –output diff <Trace_file1> <Trace_File2>

Reference

1. Oracle 11g Advanced application developers guide
2. Oracle 11g PLSQL packages and types reference.
3. Www.oracle-base.com

Virtual columns in 11 G Release 2

Virtual Columns is a new feature introduced in Oracle 11g. As the name indicates, it is virtual. It do not occupy any memory space. This article features how to create a table with virtual column, how to add one to an existing table, creating Indexes on virtual column and the usage and restriction in Virtual columns.

Syntax

Syntax to create a virtual column when creating a table:

CREATE TABLE TABLE_NAME

(COLUMN_1 DATA_TYPE,

VIRTUAL_COLUMN  [DATATYPE] [GENERATED ALWAYS] AS  EXPRESSION [VIRTUAL])

Explanation 

While defining a virtual column datatype of this column is optional, if it is not mentioned oracle derives it from the expression. GENERATED ALWAYS is added for clarity and this in optional as well as the keyword VIRTUAL is also optional.

Example 

Lets create a table with virtual column:

CREATE TABLE VIRTUAL_EG

(SALARY NUMBER,

ANUUAL_SALARY  NUMBER GENERATED ALWAYS AS (12*SALARY)  VIRTUAL);


Lets insert rows into the table

sh@ORCL> insert into virtual_eg 
select rownum * 100
from all_objects where rownum <=1000  2    3  
  4  /
insert into virtual_eg
            *
ERROR at line 1:
ORA-00947: not enough values

We cannot insert values directly into a virtual column. Also We need to mention the column list while inserting into a table containing Virtual column otherwise error 'not enought values' would be shown.

So let me modify my insert statement:


SH@ORCL> INSERT INTO VIRTUAL_EG (SALARY)
SELECT ROWNUM * 100 FROM ALL_OBJECTS WHERE ROWNUM <=1000;

1000 rows created.


Let me select from the table:

select * from virtual_eg where rownum <=10;


    SALARY ANUUAL_SALARY
---------- -------------
       100          1200
       200          2400
       300          3600
       400          4800
       500          6000
       600          7200
       700          8400
       800          9600
       900         10800
      1000         12000



We can create integrity constraints and indexes on the virtual columns:

CREATE INDEX IX_ANNUAL_SALARY ON VIRTUAL_EG(ANUUAL_SALARY)


One can add a virtual column to an existing table by using an alter statement:
ALTER TABLE VIRTUAL_EG ADD 
( COMMISION AS (CASE  WHEN SALARY < 5000 THEN  0.1 * SALARY
 WHEN SALARY < 10000 THEN 0.15* SALARY 
 WHEN SALARY >= 10000 THEN 0.2 * SALARY 
END ))


Data dictionary would show there are two virtual columns in this table

SELECT COLUMN_NAME, VIRTUAL_COLUMN FROM
USER_TAB_COLS WHERE TABLE_NAME = 'VIRTUAL_EG'


COLUMN_NAME                    VIR
------------------------------ ---
SALARY                         NO
ANUUAL_SALARY                  YES
COMMISION                      YES

Points to note for using virtual columns

1. An index defined using a virtual column is equalent to a function based index.

2. We cannot update a virtual column directly

3. Similarly we cannot use a virtual column in where clause of a delete statement

4. Query which refers a virtual column is eligible for result caching

Restrictions on using Virtual Columns

1. Virtual Columns can only be created in Relational Heap table

2. Virtual columns cannot refer to another virtual column by name

3. Any columns refered in column definition must be defined in the same table

4. It can refer to a deterministic user-defined function, but if it does, then you cannot use the virtual column as a partitioning key column.

5. The output of column_expression must be a scalar value.

6. Virtual columns cannot be used in materialised views or materialised view logs.

References:

For more information on Virtual columns check the below links:

1. Oracle-base.com

2. Oracle-developer.net

3. Oracle 11g release documentation