how to enable dbms profiler [message #445886] |
Thu, 04 March 2010 08:49 |
nastyjillu
Messages: 211 Registered: February 2009
|
Senior Member |
|
|
i want to tune my plsql code using dbms_profiler.
but dbms_profiler is disabled in my TOAD.
i have GRANT on this pkg. even though i have it disabled. how should i enable it??
thanks
jillu
|
|
|
|
Re: how to enable dbms profiler [message #445905 is a reply to message #445888] |
Thu, 04 March 2010 11:31 |
nastyjillu
Messages: 211 Registered: February 2009
|
Senior Member |
|
|
i tried to run the pkg in sqlplus,
i ran below code after creating a procedure named do_something
DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
do_something(p_times => 100);
l_result := DBMS_PROFILER.stop_profiler;
END;
but it says identifier do_something doesnt exist.
|
|
|
|
|
Re: how to enable dbms profiler [message #445911 is a reply to message #445908] |
Thu, 04 March 2010 12:14 |
nastyjillu
Messages: 211 Registered: February 2009
|
Senior Member |
|
|
i followed whats given in below site
http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php
i created procedure
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
then
DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
do_something(p_times => 100);
l_result := DBMS_PROFILER.stop_profiler;
END;
but it says , Identifier 'do_something' must be declared.
Anyone who finds the missing DO_SOMETHING procedure, please post it here immediately.
should i assume this as a JOKE ?
|
|
|
|
|
|
|
|
|
Re: how to enable dbms profiler [message #445931 is a reply to message #445926] |
Thu, 04 March 2010 14:25 |
nastyjillu
Messages: 211 Registered: February 2009
|
Senior Member |
|
|
to michel,
you are right.
to swan,
actually i created the procedure in other schema and was running the profiler code in other schema, that is why it was throwing error.
now i created the procedure in the same schema where iam running profiler code. it ran correclty now. but it didnot create records in below given tables :
plsql_profiler_runs;
plsql_profiler_units;
plsql_profiler_data;
then how should i findout the bottlenecks in my procedure??
Quote:CAn you run it through sqlplus?
as i can run the dbms_profiler in sqlplus now, is there any way to enable profiler in my TOAD??
THANKS
jillu
|
|
|
|
|
|
Re: how to enable dbms profiler [message #446162 is a reply to message #445940] |
Fri, 05 March 2010 14:44 |
nastyjillu
Messages: 211 Registered: February 2009
|
Senior Member |
|
|
Got it .
To enable the Dbms Profler, we have to run the server side object wizard first.
Also Make sure that GRANT EXECUTE on the DBMS_PROFILER package has been granted to PUBLIC or to the users that will use the profiling feature.
You can do this by selecting Database | Administer | Server Side Objects in Toad's menu bar. When the server side objects window appears click next, fill in the information for a dba user, and click next again. In the Toad Profiler section, check the Add or Recompile TOAD_PROFILER box, depending on which one is available to you. Then finish through the wizard.
thanks
jillu
|
|
|
|