How to run a trace in oracle which can capture reads, writes, cpu and duration of queries

In SQL SERVER jargon, it is the PROFILER.

What is that in ORACLE jargon (Title is the question) ?

Or any third party tool(free) is开发者_如何学Python there that does the job?

Kindly guide me as I am new to ORACLE

Thanks

In Oracle you would use SQL Trace and TKPROF. There is too much to go into in an answer on SO, but the essense is that you "turn on" SQL Trace at the session level (usually), which results in a trace file being written to the server containing the sort of information you are looking for. You then use the TKPROF program to convert the trace file into a readable report.

To turn SQL Trace on in a SQL Plus session:

SQL> alter session set sql_trace=true;

Everything after that is then logged to the trace file until you issue:

SQL> alter session set sql_trace=false;

You then have to find the generated .trc file on the server and convert it to a report using TKPROF something like this:

OS> tkprof xxxx.trc mytrace.txt

You cna then view mytrace.txt with a text editor. The tkprof command has many options that are documented in the link above.

You can also turn tracing on and off from within PL/SQL code by calling the DBMS_SESSION.SET_SQL_TRACE procedure.