• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1367
  • Last Modified:

Question related to TKPROF/EXPLAIN PLAN

I have a sqlstatment which has nested loops and so on. When I do explain plan/TKPROF. I only get the information on sort options CPU Time, Elapsed time, execution time on summary level.

Is there any possible way of getting the information even in detail level at Nested loops.

Eg: Nested Loop1 takes ... this much time..

 Nested Loop2 takes .. this much of time.

Thanks in advance
Ganta
0
sganta
Asked:
sganta
1 Solution
 
sgantaAuthor Commented:
Hi Experts

Even if I can identify the  most timecausing Nested Loop
other than CBO Cost. would be fine.

Regards,
Ganta
0
 
FayyazCommented:
You can use DBMS_PROFILER for these kind of taks.

Here are the steps to configure and use DBMS_PROFILER

*************** steps starts ***************
If you are not already configured DBMS_PROFILE package look the following script in Oracle Home->rdbms->admin


SCRIPT :  PROFLOAD.SQL and PROFTAB.SQL
   

SETUP :

U:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 14 13:26:03 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> connect sys/*****  as sysdba
Connected.
SQL> @D:\oracle\rdbms\admin\profload.sql
Package created.
Grant succeeded.
Synonym created.
Library created.
Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

Create the profiler tables

SQL> connect hr/hr
Connected.
SQL> @D:\oracle\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber              *
ERROR at line 1:
ORA-02289: sequence does not exist

Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.


The above script will create the following table
PLSQL_PROFILER_DATA            TABLE
PLSQL_PROFILER_RUNS            TABLE
PLSQL_PROFILER_UNITS           TABLE


Let us create a sample table and a procedure for demonstration

SQL> create table testemp(emp number);

Table created.

CREATE OR REPLACE PROCEDURE hr.testproc
IS
   i        NUMBER := 0;
   vempno   NUMBER;
BEGIN
   WHILE i < 100000
   LOOP
      INSERT INTO hr.testemp
                  (emp
                  )
           VALUES (i
                  );

      COMMIT;
      i := i + 1;
   END LOOP;

   SELECT COUNT (*)
     INTO vempno
     FROM hr.testemp;

   DBMS_OUTPUT.put_line ('No of record' || vempno);

   DELETE FROM hr.testemp;

   COMMIT;

   FOR i IN 1 .. 100000
   LOOP
      INSERT INTO hr.testemp
                  (emp
                  )
           VALUES (i
                  );

      COMMIT;
   END LOOP;
END testproc;
/

The following PL/SQL will call the profile

SQL> Set serveroutput on;
SQL>
SQL>
DECLARE
   vrun   NUMBER;
BEGIN
   vrun := SYS.DBMS_PROFILER.start_profiler ('TESTRUN1');
   DBMS_OUTPUT.put_line ('START PROFILER STATUS ' || vrun);
   hr.testproc; -- calling procedure
   vrun := SYS.DBMS_PROFILER.stop_profiler;
   DBMS_OUTPUT.put_line ('STOP PROFILER STATUS ' || vrun);
   DBMS_OUTPUT.put_line ('0     successful');
   DBMS_OUTPUT.put_line ('1     incorrect parameter');
   DBMS_OUTPUT.put_line ('2     data flush operation failed');
   DBMS_OUTPUT.put_line ('-1     version mismatch between package and tables');
END;
/
   

Expected Output
START PROFILER STATUS 0
No of record100000
STOP PROFILER STATUS 0
0     successful
1     incorrect parameter
2     data flush operation failed
-1     version mismatch between package and tables

PL/SQL procedure successfully completed.


Now run the sql to find out the most extensive part of the plsql

SET linesize 132
COL unit format a20
COL tc format 9999999 heading "Exection"
COL stext format a40
SELECT   u.unit_owner || '.' || u.unit_name unit, line#,
         ROUND (d.total_time / 1000000000) total_time_s, d.total_occur tc,
         SUBSTR (s.text, 1, 40) stext
    FROM plsql_profiler_runs r,
         plsql_profiler_units u,
         plsql_profiler_data d,
         all_source s
   WHERE r.run_comment = 'TESTRUN1'
     AND r.runid = u.runid
     AND d.runid = u.runid
     AND u.unit_number = d.unit_number
     AND s.owner = u.unit_owner
     AND s.TYPE = u.unit_type
     AND s.NAME = u.unit_name
     AND s.line = d.line#
ORDER BY line#
/

Expected Output

UNIT             LINE#      TIME[s] Exection STEXT
--------------- ---------- ------------ -------- ----------------------------------------
HR.TEST            3        0.000        1 i number := 0;
HR.TESTPROC        6       58.000   100001 while i < 100000
HR.TESTPROC        8    4,951.000   100000 insert into hr.testemp(emp ) values (i);
HR.TESTPROC        9    2,901.000   100000 commit;
HR.TESTPROC       10      131.000   100000 i := i + 1;
HR.TESTPROC       12        6.000        1 select count(*) into vempno from hr.test
HR.TESTPROC       13        0.000        2 dbms_output.put_line('No of record'||vem
HR.TESTPROC       14    1,461.000        1 delete from hr.testemp;
HR.TESTPROC       15        0.000        1 commit;
HR.TESTPROC       16       70.000   100001 for i in 1..100000
HR.TESTPROC       18    5,371.000   100000 insert into hr.testemp(emp ) values (i);
HR.TESTPROC       19    2,955.000   100000 commit;


Use Time [s] value to determine only those areas where performance is slow. It may not be equal to the elapsed time.

Hope the above information will help …


******************* Steps End ****************
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now