Home » SQL & PL/SQL » Client Tools » how to enable dbms profiler (10g)
how to enable dbms profiler [message #445886] Thu, 04 March 2010 08:49 Go to next message
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 #445888 is a reply to message #445886] Thu, 04 March 2010 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
CAn you run it through sqlplus?
Re: how to enable dbms profiler [message #445905 is a reply to message #445888] Thu, 04 March 2010 11:31 Go to previous messageGo to next message
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 #445907 is a reply to message #445905] Thu, 04 March 2010 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste your session.

Regards
Michel
Re: how to enable dbms profiler [message #445908 is a reply to message #445905] Thu, 04 March 2010 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>but it says identifier do_something doesnt exist.

All Points Bulletin:

Anyone who finds the missing DO_SOMETHING procedure, please post it here immediately.
Re: how to enable dbms profiler [message #445911 is a reply to message #445908] Thu, 04 March 2010 12:14 Go to previous messageGo to next message
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 #445912 is a reply to message #445911] Thu, 04 March 2010 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
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;
/


try above instead
Re: how to enable dbms profiler [message #445915 is a reply to message #445911] Thu, 04 March 2010 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
should i assume this as a JOKE ?

What could be the answer as you didn't post what is asked:
A STRICT COPY AND PASTE OF YOUR SQL*PLUS SESSION.
Of the WHOLE session we can reproduce.

Regards
Michel
Re: how to enable dbms profiler [message #445917 is a reply to message #445915] Thu, 04 March 2010 12:25 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
What could be the answer as you didn't post what is asked:
i posted it as soon as i saw your question Copy and paste your session.
Re: how to enable dbms profiler [message #445921 is a reply to message #445912] Thu, 04 March 2010 12:37 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Quote:
try above instead


this is same as mine
Re: how to enable dbms profiler [message #445923 is a reply to message #445921] Thu, 04 March 2010 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It works for me.

  1  DECLARE
  2    l_result  BINARY_INTEGER;
  3  BEGIN
  4    l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
  5    do_something(p_times => 100);
  6    l_result := DBMS_PROFILER.stop_profiler;
  7* END;
10:46:42 SQL> /

PL/SQL procedure successfully completed.

Re: how to enable dbms profiler [message #445926 is a reply to message #445917] Thu, 04 March 2010 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nastyjillu wrote on Thu, 04 March 2010 19:25
What could be the answer as you didn't post what is asked:
i posted it as soon as i saw your question Copy and paste your session.

No you didn't.
See Blackswan anser, this is a copy and paste of SQL*Plus session, your posts are not.

Regards
Michel
Re: how to enable dbms profiler [message #445931 is a reply to message #445926] Thu, 04 March 2010 14:25 Go to previous messageGo to next message
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 #445932 is a reply to message #445931] Thu, 04 March 2010 14:34 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>then how should i find out the bottlenecks in my procedure??

More often, than not, bad performance is due to poor SQL; not PL/SQL

To find poor SQL do as follows:

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke suspect code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here
Re: how to enable dbms profiler [message #445937 is a reply to message #445931] Thu, 04 March 2010 15:36 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nastyjillu wrote on Thu, 04 March 2010 21:25
is there any way to enable profiler in my TOAD??

Can't tell for sure, but it might depend on TOAD version you own (i.e. base version might not have this option, while more advanced (and more expensive) ones do).
Re: how to enable dbms profiler [message #445940 is a reply to message #445937] Thu, 04 March 2010 16:13 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
mine is TOAD 9.6.1
Re: how to enable dbms profiler [message #446162 is a reply to message #445940] Fri, 05 March 2010 14:44 Go to previous messageGo to next message
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
Re: how to enable dbms profiler [message #446176 is a reply to message #445886] Fri, 05 March 2010 17:13 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thanks for the feedback.
Previous Topic: create subfolder using SPOOL
Next Topic: Delimited output from sqlplus (merged by CM)
Goto Forum:
  


Current Time: Wed Sep 28 22:47:41 CDT 2016

Total time taken to generate the page: 0.11992 seconds