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
LVL 4
sgantaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.