Code forces First submission

I was little late for the Div 2 contest today. This would have been my first div 2 contest. but i missed. Started practising. Came across 9C hexadecimal's number problem. It was a simple problem. It asked for counting the numbers within <= given number which are a vaild binay number. I arrived by taking the binary numbers from 1 and comparing representation in decimal with the given number. 

UVA 10954

This problem uva 10954 can be easily solved using Priority queues in java. This is the first time i wrote code using a priority queue. It automatically sorts the elements added.

Here is my code

Sangam 12

A post after a very long time. This year i presented in Sangam12 , AIOUG's Annual Conference held in Bangalore in Nov 2 and 3rd. My presentation is about how to use profiling tools to profile PLSQL codes. Presentation went well though this was the first time i spoke for about an hour. Last year when i attended Sangam 11,  i kept a goal for myself to present in such a conference. Goal achieved :)

A new book on Performance tuning

Though the scope for performance tuning is limited in my current assignment, i am inquisitive about what performance tuning is and  how as a developer i can write efficient programs, queries. I recently  bought Oracle Database performance tuning tips and Techniques book by Richard Niemiec. I was lucky to attend a session on Introduction to Exadata by Rich Niemiec, Thanks to AIOUG for bringing him to bangalore last year for Sangam 11.

   The presentation itself was very informative. His presentation style was awesome and as stylish as Rajnikanth. The man inspired me. So enhance my performance tuning knowledge,  as a starting place would be none other than Rich's updated version of performance tuning book. Flipkart delivered the book on monday. Here is the newest collection to my books.

 This is gonna keep me busy.

Regards,
Vijay

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