Results 1 to 4 of 4

Thread: SQL profiler

  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: SQL profiler

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - 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.

    Andy

  2. #2
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    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...

    regards,

    Andy

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    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
    Posts
    1,074
    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

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

    Code:
    begin
      dbms_monitor.session_trace_disable(session_id=>249);
    end;
    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>):

    Code:
    [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.

    --=Chuck

Posting Permissions

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