July 16, 2010 at 1:11 pm
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
July 16, 2010 at 2:09 pm
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.July 16, 2010 at 2:48 pm
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
July 17, 2010 at 7:17 am
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.Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy