How to use TK-Proof in Oracle

This article explains how to use the Tk-prof utility in Oracle. 


TK-Proof in Oracle

TK-Prof is a utility by Oracle database to trace the performance of query within PL/SQL block.


Steps to use TK-Prof:

alter session set tracefile_identifier =’cur_for_loop’;

alter session set timed_statistics=true;

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE);

set serveroutput on;


— ——————————————————————————

— Populating the data using FOR LOOP.

declare

 type bulk_collect_rec_typ is table of empl%rowtype;

 bulk_collect_type bulk_collect_rec_typ := bulk_collect_rec_typ();

begin

for cur_rec in (select * from empl)

loop

  bulk_collect_type.extend;

  bulk_collect_type(bulk_collect_type.last):=cur_rec;

end loop;

dbms_output.put_line(‘for_loop_count : ‘|| bulk_collect_type.count);

end;

/

— ——————————————————————————

EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;


— go to the path :

— (Windows)E:\virtual\diag\rdbms\orcl\orcl\trace     

— (Linux)  /u01/app/oracle/diag/rdbms/orcl/orcl/trace


trace> tkprof orcl_ora_2032_cur_for_loop.trc  orcl_ora_2032_cur_for_loop.prf

— check the file orcl_ora_2032_cur_for_loop.prf for the TK-prof report.

— ——————————————————————————

SQL ID: 25sdhpnggdwtr Plan Hash: 3277272027


SELECT * 

FROM

EMPL


call     count   cpu elapsed       disk query current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse        1 0.00     0.00 0       0 0 0

Execute      1 0.00     0.00 0       0 0 0

Fetch    14001   2.98 2.64          0 22224 0   1400056

——- ——  ——– ———- ———- ———- ———-  ———-

total    14003   2.98 2.64          0 22224 0   1400056

— ——————————————————————————

alter session set tracefile_identifier=’bulk_collect’;

alter session set timed_statistics=true;

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE);

set serveroutput on;


— ——————————————————————————

— Populating the data using BULK COLLECT.

declare

 type bulk_collect_rec_typ is table of empl%rowtype;

 bulk_collect_type bulk_collect_rec_typ := bulk_collect_rec_typ();

begin

  SELECT * BULK COLLECT INTO bulk_collect_type

  FROM empl;

  dbms_output.put_line(‘for_loop_count : ‘|| bulk_collect_type.count);

end;

/

— ——————————————————————————

EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;


— go to the path :

— (Windows)E:\virtual\diag\rdbms\orcl\orcl\trace     

— (Linux)  /u01/app/oracle/diag/rdbms/orcl/orcl/trace


trace> tkprof orcl_ora_2032_bulk_collect.trc orcl_ora_2032_bulk_collect.prf

— check the file orcl_ora_2032_bulk_collect.prf for the TK-prof report.


— ——————————————————————————

SQL ID: 25sdhpnggdwtr Plan Hash: 3277272027


SELECT * 

FROM

EMPL


call     count   cpu elapsed       disk query current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse        1 0.00     0.00 0       0 0 0

Execute      1 0.00     0.00 0       0 0 0

Fetch        1 3.57     3.58 0     8330 0 1400056

——- ——  ——– ———- ———- ———- ———-  ———-

total        3 3.57     3.58 0     8330 0 1400056

— ——————————————————————————


alter session set tracefile_identifier=’bulk_collect_limit’;

alter session set timed_statistics=true;

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE);

set serveroutput on;


— —————————————————————————–

— Populating the data using BULK COLLECT with LIMIT clause.

DECLARE

 type bulk_collect_rec_typ is table of empl%rowtype;

 bulk_collect_type bulk_collect_rec_typ := bulk_collect_rec_typ();

 v_cnt NUMBER := 0;

 CURSOR emp_cur IS SELECT * FROM empl;

BEGIN

 OPEN emp_cur;

  LOOP

    FETCH emp_cur

    BULK COLLECT INTO bulk_collect_type

  LIMIT 20000;

  v_cnt := v_cnt + bulk_collect_type.count;

  EXIT WHEN emp_cur%NOTFOUND;

  END LOOP;

  dbms_output.put_line(‘for_loop_count : ‘|| bulk_collect_type.count);

  CLOSE emp_cur;

END;

/

— ——————————————————————————

EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;


— go to the path :

— (Windows)E:\virtual\diag\rdbms\orcl\orcl\trace     

— (Linux)  /u01/app/oracle/diag/rdbms/orcl/orcl/trace


trace> tkprof orcl_ora_2032_bulk_collect_limit.trc orcl_ora_2032_bulk_collect_limit.prf

— check the file orcl_ora_2032_bulk_collect_limit.prf for the TK-prof report.

— ——————————————————————————

SQL ID: 25sdhpnggdwtr Plan Hash: 3277272027

SELECT * 

FROM

 EMPL


call     count   cpu elapsed       disk query current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse        1 0.00     0.00 0       0 0 0

Execute      1 0.00     0.00 0       0 0 0

Fetch       71 2.32       2.31 0   8399 0 1400056

——- ——  ——– ———- ———- ———- ———-  ———-

total       73 2.32       2.31 0   8399 0 1400056


Note: Here in the above observation, we can check the “Count” and “cpu”, for all the 3 scenarios. the second case “BULK COLLECT” is better as compared to “FOR LOOP”, but for huge data processing like huge ETL operations we should chose the “BULK COLLECT with LIMIT options” to parse the data in phase-by-phase with a provided LIMIT to release load from the optimiser.

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn