Results 1 to 4 of 4

Thread: SQL profiler

  1. #1
    Join Date
    Dec 2003
    Nottingham, England

    Unanswered: SQL profiler

    Oracle Database 11g Enterprise Edition Release - 64bit Production
    PL/SQL Release - Production
    CORE Production
    TNS for 64-bit Windows: Version - Production
    NLSRTL Version - Production
    Morning all.

    I’m trying to find a way to set up an SQL trace (similar to SQL Profiler under SQL Server) through the Oracle Enterprise Manager.

    I’ve managed to do this manually, outputting to a file with the following:

    To get the process I want I execute:

    select sid, serial#, username, status, process from sys.v_$session where username = 'MYUSER'

    Then at the command line:

    > sqlplus
    > / as sysdba;
    > execute dbms_system.set_sql_trace_in_session(94, 10, true);

    Alternatively I can also do it 'globally' with the following but this produces lots of trace files...

    > alter session set sql_trace = true;

    These successfully produce trace file(s) in my C:\oracle\diag\rdbms\...\trace directory.

    I'm sure there is a way though of setting this up and viewing it graphically in real time (just as you can with SQL Profiler under SQL Server) through the Oracle Enterprise manager but I am having no luck.

    If I select the Performance tab, scroll down to Additional Monitoring Links and choose SQL Monitoring, this looks like what I am looking for but how do I get it to show the SQL actioned against the database/schema? -
    i.e. I run a SELECT statement in SQL Developer and want to see this in the SQL Monitor?, when I do this though it doesn't pick it up.

    I'm tearing my hair out here, looked(am looking) at all the docs I can find but I can't get it working.

    Thanks greatly for any help on this.


  2. #2
    Join Date
    Dec 2003
    Nottingham, England
    Wow - 80 views and still no expert has commented on this.

    This could mean 1 of 2 things:

    1 - What I am asking is so simple and easy to do that its not even worth replying - i.e RTM (I would - please point me to the part of the manual that deals with GUI real time tracing - I can't find it!)

    2 - This isn't easy at all and no one has successfully done what I want either.

    Surely what I ask is possible in Oracle isn't it?
    Anyone who has used SQL Profiler in SQL Server knows what a valuable tool it is.

    Come on you Oracle people, help this SQL Server convert out...



  3. #3
    Join Date
    Jun 2004
    Provided Answers: 1
    Have you tried "Search Sessions" under the Performance tab, then clicking on the 'Activity' tab for the selected session? If you then want a trace file using OEM then in the top right hand corner click on "Enable SQL Trace", but remember to disable the trace when you've finished (otherwise your trace file will keep growing!).

    Or if you want an overview of everything that's going on, with the option to 'dive in' on specific transactions/users, try clicking on the 'Top Activity' link (under 'Additional Monitoring').

    If you want something more detailed than SQL Trace then have a look at SQL Trace Analyzer (needs to be downloaded & installed as it's not part of the Oracle build).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Dec 2003
    If you're looking for ways to enable tracing, and a solid methodology for interpreting performance problems, I recommend "Optimizing Oracle Performance" by Millsap and Holt.

    Otherwise, in 10g once you locate the SID for the database session that you want to trace

      dbms_monitor.session_trace_enable(session_id=>249,waits=>TRUE, binds=>TRUE);
    and to disable

    Then you look in the UDUMP directory for the tracefile. Then you have to run tkprof against that file to format it (tkprof <input file> <output file>):

    [oracle@oradev455 udump]$ tkprof dvb455_ora_11257.trc dvb455_ora_11257.tkprof
    In that book mentioned above, they have created a tool (which costs $) called the Hotsos Profiler which can also parse a tracefile, which has a superior output to tkprof, IMO.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts