Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_PROFILER (Oracle 10g,WinXP)
DBMS_PROFILER [message #389412] Sun, 01 March 2009 23:17 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I am using DBMS_PROFILER Package for Performance Tuning, I don't know much about it so googled to get the following SQL,

select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from   all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
                             d.total_occur, d.total_time/1000000 total_time
                      from   plsql_profiler_data d, plsql_profiler_units u
                      where  u.runid = &&runid
                      and    u.runid = d.runid
                      and    u.unit_number = d.unit_number) p
where  s.owner = p.unit_owner (+)
and    s.name = p.unit_name (+)
and    s.type = p.unit_type (+)
and    s.line = p.line# (+)
and    s.name = upper('&&name')
and    s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from  (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')) total,
     (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')
      and d.total_occur > 0) exec;


But when i want to run this Query ( Actually to see whether the tables present or not) the tables like PLSQL_PROFILER_DATA ,PLSQL_PROFILER_UNITS doesn't exist in my Oracle, do i have to download it or it will be installed by default ? Please response.

Regards,
Ashoka BL
Bengaluru
Re: DBMS_PROFILER [message #389415 is a reply to message #389412] Sun, 01 March 2009 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE can be your friend.

http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php
Re: DBMS_PROFILER [message #389416 is a reply to message #389412] Sun, 01 March 2009 23:45 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Thanks for the reply, Can anybody please let me know what sort of Tuning Mechanisms you use for PL/SQL Procedures,Functions and Packages, and if any Tools available for it.

Regards,
Ashoka BL
Re: DBMS_PROFILER [message #389417 is a reply to message #389412] Sun, 01 March 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can anybody please let me know what sort of Tuning Mechanisms you use for PL/SQL Procedures,Functions and Packages, and if any Tools available for it.

You do not tune PL/SQL.
Applications are tuned one SQL statement at a time.


PL/SQL is a language executed inside Oracle RBMS.
The vast, vast, vast amount of elapsed time is spent waiting for SQL statements to complete.
PL/SQL runs at CPU speed while much of SQL runs at disk speed; 100-1000 times slower.

Re: DBMS_PROFILER [message #389421 is a reply to message #389412] Mon, 02 March 2009 00:27 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

BlackSwan,

Thanks for your Valuable Suggestions, I have gone through the link that you sent and understood the concepts, Thanks.

The queries/Blocks that i used to get the results,

1.

DECLARE
   l_result   BINARY_INTEGER;
BEGIN
   l_result :=
      DBMS_PROFILER.start_profiler (run_comment      =>    'do_something: '
                                                        || SYSDATE
                                   );
   DBMS_OUTPUT.put_line (l_result);
   do_something (p_times => 10000);
   l_result := DBMS_PROFILER.stop_profiler;
END;
/



SELECT   s.line "Line", p.total_occur "Occur", p.total_time "Msec",
         s.text "Text"
    FROM all_source s,
         (SELECT u.unit_owner, u.unit_name, u.unit_type, d.line#,
                 d.total_occur, d.total_time / 1000000 total_time
            FROM plsql_profiler_data d, plsql_profiler_units u
           WHERE u.runid = 4
             AND u.runid = d.runid
             AND u.unit_number = d.unit_number) p
   WHERE s.owner = p.unit_owner(+)
     AND s.NAME = p.unit_name(+)
     AND s.TYPE = p.unit_type(+)
     AND s.line = p.line#(+)
     AND s.NAME = UPPER ('Do_Something')
     AND s.owner = UPPER ('Ashok')
ORDER BY s.line;


Quote:

AND s.NAME = UPPER ('Do_Something')
AND s.owner = UPPER ('Ashok')


Refers to the Procedure Name and the Schema Owner.

I got the following output,

/forum/fa/5830/0/
Re: DBMS_PROFILER [message #389424 is a reply to message #389421] Mon, 02 March 2009 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is there any question in your post?

Regards
Michel
Re: DBMS_PROFILER [message #389427 is a reply to message #389412] Mon, 02 March 2009 00:36 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi Michel,

The question was right at the start, I wanted to know about DBMS_PROFILER and Some other related tables like PLSQL_PROFILER_DATA ,PLSQL_PROFILER_UNITS

Quote:

But when i want to run this Query ( Actually to see whether the tables present or not) the tables like PLSQL_PROFILER_DATA ,PLSQL_PROFILER_UNITS doesn't exist in my Oracle, do i have to download it or it will be installed by default ? Please response.



I got the answer from BlackSwan, so thought of sharing here.

Regards,
Ashoka BL
Previous Topic: -SQL -Not starting
Next Topic: Index on number and varchar (merged)
Goto Forum:
  


Current Time: Wed Feb 12 06:56:30 CST 2025