PLSQL Performance Tuning DBMS_PROFILER


DBMS_PROFILER - DBMS_PROFILER package is Oracle supplied package to tune PLSQL code.

Steps :- 1) Environment Setup (One Time Setup)
              2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER
              3) Analyze Profiler data
              4) Optimize PLSQL code then again repeat step 2)

1) Environment Setup :- a) Check DBMS_PROFILER Package available in SYS schema.

select object_name,object_type from all_objects where object_name like '%PROF%';

b) check that you are able to execute package DBMS_PROFILER from your user. if you are not able to excute then grant execute priviledge
on package DBMS_PROFILER from sys user to your user.

grant execute on DBMS_PROFILER to scott;

c) Create rquired table through 'proftab.sql'


d) check profile tables creates

select * from user_tables where table_name like '%PROF%';


2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER :-

exec procedure;

3) Analyze Profiler data :-

select * from PLSQL_PROFILER_RUNS;
select * from PLSQL_PROFILER_DATA;

SELECT u.runid,
FROM   plsql_profiler_units u
       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE  u.runid = 1
ORDER BY u.unit_number, d.line#;

-- Top 10 slow statements

select trim(decode(unit_type,'PACKAGE SPEC','PACKAGE',unit_type)||
 ' '||trim(pu.unit_owner)||'.'||trim(pu.unit_name))||
' (line '|| pd.line#||')' object_name
, pd.total_occur
, pd.total_time
, pd.min_time
, pd.max_time
, src.text
, rownum sequencenr
from plsql_profiler_units pu
,    plsql_profiler_data pd
,    all_source src
where pu.unit_owner = user
and pu.runid = &1
and pu.runid=pd.runid
and pu.unit_number = pd.unit_number
and src.owner = pu.unit_owner
and src.type = pu.unit_type
and = pu.unit_name
and src.line = pd.line#
) where sequencenr <=10;

4) Optimize PLSQL code :-

PLSQL code need to modified with help of following  :-

Bulk Collection
Global Temporary Table
Inbuilt Functions
Nocopy Compiler Hint

Truncate sequence for Profiler tables data :-

truncate table PLSQL_PROFILER_DATA;
truncate table PLSQL_PROFILER_UNITS;
truncate table PLSQL_PROFILER_RUNS;

Installing Oracle Database 12c on Windows

Steps to Install Oracle 12c on Windows Server 2012

1) Go to database folder (Where Setup placed) . Right click on setup icon and click on "Run as Administrator"

2) You will get below window , click on "Yes"

3) On below window do un-check for  Received security updates 

4) Click on "Yes" for acceptance that you have not provided email address

5) Select "Skip for software updated"

6) Select First option for Installation of database software plus database creation

7) Select "Desktop class"

8) Choose option for Oracle Database owner

9) Provide the details for Oracle base ..etc

10) Click on "install"

11) Click on Password Management and change the password for required users

12) Click on "Close" Installation completed