OraFAQ Forum: Client Tools » DBMS_PROFILER not showing any data

Home » Other » Client Tools » DBMS_PROFILER not showing any data (Oracle 10G, Toad)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_PROFILER not showing any data [message #484279]

Mon, 29 November 2010 05:53

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

Report message to a moderator

Re: DBMS_PROFILER not showing any data [message #484280 is a reply to message #484279]

Mon, 29 November 2010 06:04

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: DBMS_PROFILER not showing any data [message #484293 is a reply to message #484279]

Mon, 29 November 2010 07:34

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

Report message to a moderator

Re: DBMS_PROFILER not showing any data [message #484352 is a reply to message #484280]

Mon, 29 November 2010 22:52

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

Report message to a moderator

Re: DBMS_PROFILER not showing any data [message #484358 is a reply to message #484293]

Mon, 29 November 2010 23:50

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

Report message to a moderator

Re: DBMS_PROFILER not showing any data [message #484434 is a reply to message #484358]

Tue, 30 November 2010 09:07

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?

Report message to a moderator

Previous Topic:	Can connect to SqlPlus but not through Toad
Next Topic:	Oracle 8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 06:46:36 CST 2024