Tool for tracing Oracle queries. Similar to SQL Profiler

  • Hi,

    I am looking for a free tool/software to monitor Oralce queries similar to SQL Profiler and how to see the trace. Does anybody know about this. I have installed PL/SQL Developer but its not showing the active queries.

    Thanks,

    ek

  • ek-822869 (7/16/2010)


    I am looking for a free tool/software to monitor Oralce queries similar to SQL Profiler and how to see the trace. Does anybody know about this. I have installed PL/SQL Developer but its not showing the active queries.

    Is it PL/SQL Developer or SQL Developer?

    Either way, welcome to the wonderful world of Oracle 🙂

    Use sqlplus - it got installed alongside Oracle client.

    Lets say you want to trace query: "select sysdate from dual;" - here is what you do:

    set your oracle_home and oracle_sid

    sqlplus user/pass@instance_name

    drop table PLAN_TABLE;

    @?/rdbms/admin/utlxplan

    alter session set timed_statistics = true;

    alter session set sql_trace = true;

    set autotrace on explain

    alter session set events='10046 trace name context forever, level 12';

    -- here you execute your query...

    select sysdate from dual;

    -- here you are on your way out

    alter session set sql_trace = false;

    alter session set timed_statistics = false;

    exit

    -- time to look for your trace

    go to user_dump_destination folder, check location on your init.ora file

    identify the last trace file... something.trc

    tkprof something.trc something.prf sys=no

    File something.prf containst your trace file.

    Piece of cake, isn't it? 😀

    If you want to trace a query already running on other session I would suggest to research ORADEBUG utility.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Hi Paul,

    Thanks for your reply. I am actually running a report from the UAT website which is connecting to Oracle database. So, i need to to know when i click on submit button on the report what exactly the queries are being fired. I have PL/SQL Developer installed on my machine, i think we can trace it through this tool but i am unable to do it. I tried set a Profile and Sessions etc., but nothing worked.

    Thanks,

    ek

  • Sorry. I'm not familiar with PL/SQL Developer.

    In general we do test/trace new queries in the way I suggested previously - all new queries, one by one, running in the host via sqlplus. This is the only way you can get a baseline of what to expect from those queries as they are.

    When tracing a query running in a different session we rely on oradebug - pretty straight forward.

    Both methods are designed to get a *.trc file dumped by Oracle then you have to tkprof it to get a easy to read version of it - I suggest to direct your attention to buffer_gets and wait events.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply