Home » SQL & PL/SQL » Client Tools » DBMS_PROFILER not showing any data (Oracle 10G, Toad)
DBMS_PROFILER not showing any data [message #484279] Mon, 29 November 2010 05:53 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi All

I am using Oracle 10G with Toad & want to use DBMS_PROFILER to identify which part of procedure is taking long time.

I created PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS,PLSQL_PROFILER_DATA tables.

and I am running DBMS_PROFILER as below.


DECLARE
x INTEGER;
BEGIN
x := DBMS_PROFILER.start_profiler ('Test Profiler');
Test_procedure(100);
x := DBMS_PROFILER.flush_data;
x := DBMS_PROFILER.stop_profiler;
END;
/

The procedure run is successfully completed.

I gave the following command to see the data,


SELECT runid, run_date, run_total_time, run_comment
FROM plsql_profiler_runs;


SELECT runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp,
total_time
FROM plsql_profiler_units

SELECT runid, unit_number, line#, total_occur, total_time, min_time, max_time
FROM plsql_profiler_data


No data is displayed.

Then I created these tables in SYS and gave grant to public.

Yet It's same. No data is coming into the tables.

Where I am doing wrong?

Thanks
HK
Re: DBMS_PROFILER not showing any data [message #484280 is a reply to message #484279] Mon, 29 November 2010 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Then I created these tables in SYS and gave grant to public.

Very very very bad idea, the worst you can have in Oracle.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Regards
Michel
Re: DBMS_PROFILER not showing any data [message #484293 is a reply to message #484279] Mon, 29 November 2010 07:34 Go to previous messageGo to next message
wvu1999
Messages: 40
Registered: April 2007
Member

You don't need to add all of that extra code if you're using Toad. Toad has integrated support for the Profiler.

http://www.toadworld.com/BLOGS/tabid/67/EntryId/76/Toad-for-Oracle-9-0-How-do-I-Detect-Performance-Bottlenecks-in-my-PL-SQL-Code.aspx


Re: DBMS_PROFILER not showing any data [message #484352 is a reply to message #484280] Mon, 29 November 2010 22:52 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Michel

I dropped the three tables that I created in SYS and created in the schema that I am working in.

Then run the DBMS_PROFILER procedure as below from my schema.


DECLARE
x INTEGER;
BEGIN
x := DBMS_PROFILER.start_profiler ('Test Profiler');
Test_procedure(100);
x := DBMS_PROFILER.flush_data;
x := DBMS_PROFILER.stop_profiler;
END;
/

Procedure is successfully completed.

Yet no data is coming into the three tables.

What could be the reason?

Thanks

Re: DBMS_PROFILER not showing any data [message #484358 is a reply to message #484293] Mon, 29 November 2010 23:50 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
wvu1999 wrote on Mon, 29 November 2010 19:04
You don't need to add all of that extra code if you're using Toad. Toad has integrated support for the Profiler.



Thanks for very useful link. I am able to see the data from tables. However I am struggling with navigation to view the graphs and code with comments.

Thanks again.

HK




Re: DBMS_PROFILER not showing any data [message #484434 is a reply to message #484358] Tue, 30 November 2010 09:07 Go to previous message
wvu1999
Messages: 40
Registered: April 2007
Member

" However I am struggling with navigation to view the graphs and code with comments"

Database > Optimize > Profiler Analysis


That will bring up your profiler runs and display the graphical results. Was that your question?
Previous Topic: Can connect to SqlPlus but not through Toad
Next Topic: Oracle 8i
Goto Forum:
  


Current Time: Thu Dec 09 01:51:41 CST 2021