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) BerzukovAuthor 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) BerzukovAuthor 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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply