DBMS_PROFILER [message #389412] |
Sun, 01 March 2009 23:17  |
|
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 #389416 is a reply to message #389412] |
Sun, 01 March 2009 23:45   |
|
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 #389421 is a reply to message #389412] |
Mon, 02 March 2009 00:27   |
|
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,

|
|
|
|
Re: DBMS_PROFILER [message #389427 is a reply to message #389412] |
Mon, 02 March 2009 00:36  |
|
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
|
|
|