TIP#57 : How to use PL/SQL profiler

In tip#45, I introduced dbms_profiler which is a handy tool to tune PL/SQL code.In this post, steps to use this code is briely mentioned.
Here, I try to explain some practical notes when I use this profiler mostly for a client to tune code.

1. The following SQL statement shows the elapsed time of each piece of code and in addition to line number, also SQL text is shown which make finding bottleneck easier.


select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &runid) p,
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;



2. Anonymous block in result are parts of code which are not owned by the user who ran dbms_profiler.It is better to run profiler as user who owns PL/SQL code.

TIP 52# : Tablespace map

Sometime for solving tablespace fragmentation issue, it is better off to have a better picture of tablespace allocated and free area.
In this post, I wrote a PL/SQL script which shows mapping of free space and used space in tablespace.This script helped me out to resolve tablespace fragmentation issue for a client.Analyzing the result of script would end up to detecting offending objects and relocating them.

Script details
------------------

CREATE OR REPLACE PROCEDURE mapts (target_ts VARCHAR2,min_extents NUMBER DEFAULT 8) IS
 cur_block_id NUMBER;
 prev_block_id NUMBER;
 cur_blocks NUMBER;
 prev_blocks NUMBER;
 map_str CLOB;
 alloc_str CLOB;
 tmp NUMBER;
 file_id_v NUMBER;
 CURSOR mycur IS select segment_name,blocks,block_id,EXTENT_ID from dba_extents where TABLESPACE_NAME=Upper(target_ts) AND file_id=file_id_v order by block_id,EXTENT_ID;
 myvar mycur%ROWTYPE;
BEGIN
 FOR rec IN (SELECT file_id FROM dba_data_files WHERE tablespace_name=Upper(target_ts)) LOOP
  file_id_v:=rec.file_id;
  cur_block_id:=1;
  prev_block_id:=1;
  prev_blocks:=0;
  OPEN mycur;
  LOOP
   FETCH mycur INTO myvar;
   EXIT WHEN mycur%NOTFOUND;
   cur_block_id:=myvar.block_id;
   cur_blocks:=myvar.blocks;
   IF(prev_block_id+ prev_blocks= cur_block_id) THEN
    FOR i IN 1..round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/min_extents);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   ELSE
    FOR i IN 1..Round((cur_block_id-prev_block_id-prev_blocks)/min_extents) LOOP
     map_str:=map_str||'-'|| '*';
     alloc_str:=alloc_str||'-'|| '*';
    END LOOP;
    FOR i IN 1..Round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/8);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   END IF;
   --Dbms_Output.put_line(map_str);
  END LOOP;
  INSERT INTO object_place_in_ts values(target_ts,file_id_v,alloc_str,map_str,sysdate);
  COMMIT;
  alloc_str:=NULL;
  map_str:=NULL;
 END LOOP;
 CLOSE mycur;
END;
/

Script needs the following table to be existed in database.

Table : object_place_in_ts
Table description:

Name Null? Type
----------------------------------------
TABLESPACE_NAME VARCHAR2(30)
ALLOC_PATTERN CLOB
OBJ_PATTERN CLOB
CRE_TIME DATE

Alloc_pattern : String pattern which allocated extents are shown with + and free extents are shown with *
Obj_patten : String pattern which allocated extents have name of object and free extents are shown with *.

Example :

tablespace_name allocated_pattern object_pattern
------------------------------------------------------------------------------
users ++*+++ -obj1(2)-*-objx(1)-objy(1)-objx(1)

In this example, two first extents have been allocated, third extent is free and fourth and fifth and sixth extents have been allocated . (Check allocated_pattern).
First two extents have been allocated by obj1, fourth and sixth extents have been allocated by objx.fifth extent has been allocated by objy.

I found this result and analysis very handy to resolve tablespace fragmentation.

TIP #45 : Performance tuning PL/SQL with profiler.

Oracle provides dbms_profiler for tracing PL/SQL code and finding any performace bottlenecks.
This post shows how to use it.
For using dbms_profiler, first it should be installed.

Install dbms_profile and populate tables.

- @?/rdbms/admin/profload.sql (Install dbms_profiler)
- @?/rdbms/admin/proftab.sql (Populate profiler tables).

How to trace :

- exec dbms_profiler.start_profiler('Sample1');
- Run PL/SQL code (it would be better to be proc/func/package instead of anonymous code.
- exec dbms_profiler.stop_profiler;
- exec DBMS_PROFILER.FLUSH_DATA;

How to see the result :

- Which runid is for current trace
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;

- Total elapsed time of each PL/SQL code.


SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
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#;

FYI : Also putting dbms_utility.get_time(); in PL/SQL code would show exact run time execution of each PL/SQL pieces.

TIP # 44 : Performance tips for PL/SQL code.

Small handy guidelines for improving performance of PL/SQL code.

1. In condition put least expensive condition first. (It stops when evalutes it)
2. Minimize implicit conversion. Try not to use mix of datatype . (using number and pls_integer)
3. Use pls_integer when you can. (Best performance)
4. Use binary_float or binary_double for floatingpoint
5. Mostly allocate varchar2 of size >4000, because Oracle waits for allocating it in memory at runtime (Saving memory).However for varchar2<4000,>
6. Put relevant subprogram into a package, because calling a function/procedure of it will load whole code in memory which can be referenced later.
7. Pin frequent used pacage to prevent it from aging out.(dbms_shared)
8. If using out parameter, it adds some performance overhead to make sure that in the case of any unhandled exception out parameter keep its original value after back to original program. If it is not important, user out ncopy to reduce overhead. (true for funtion with big out collection or LOB out).
9. If you are running SQL statements inside a PL/SQL loop, FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.
10. If you are looping through the result set of a query, BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.
11. Prevent unnecessary function call
    for item in (select distinct(sqlrt(empno)) from emp) ===> change to ===>
    for item in (select sqlrt(empno) from (select distinct empno from emp))
    

Example :

=== Poor performance
begin
For myvar in (select empno from emp) loop
if (myvar.empno>1000) then
delete from emp2 where empno = myvar.empno;
end if;
end loop;
end;
=== Good performance
declare
type mytype is table of number index by pls_integer;
myvar mytype;
i pls_integer:=1;
begin
select empno bulk collect into myvar from scott.emp where empno>1000;
forall i in myvar.first..myvar.last delete from scott.emp2 where empno=myvar(i);
end;
==== Forall with exception %BULK_ROWCOUNT(i), SQL%BULK_EXCEPTIONS.COUNT <==== Only works with forall

declare
type mytype is table of number(10) index by pls_integer;
myvar mytype;
i pls_integer:=1;
errors number;
begin
select empno bulk collect into myvar from scott.emp where empno>1000;
forall i in myvar.first..myvar.last save exceptions delete from scott.emp2 where empno=myvar(i);
exception WHEN others THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' i ' occurred during ''iteration #' SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;