FAQ : What is SQL Server Profiler equivalent in Oracle

What is SQL Server Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. Basically this tool is a wrapper which internally calls SQL Trace system stored procedures. Generally, running profiler is not recommended in production if you need to trace events, then in production it is better to go for SQL Trace system Stored procedure. Refer this link for more info.

Oracle Equivalent . (please note that this is based on my little experience in Oracle and If anyone feels that If I am going wrong please feel free to correct me)

Automatic Workload Repository (AWR)

Oracle offers many ways to trace events which again later version may have new tools and features. In 10 G, Oracle offers Automatic Workload Repository (AWR) which is used to collect statistics including
• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Read more about AWR here

SQL trace files and TKPROF

If you are still in Oracle 9, you can go for SQL trace files which gives you statement by statement log of SQL and PL/SQL executed by Database Client(s). The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server. SQL Trace files entries can be classified in to mainly four categories.
• Database calls (parse, execute, and fetch)
• Wait events
• Bind variable values
• Miscellaneous events like timestamps, instance service name, session, module, action, and client
identifier

TKPROF

You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
• Determine the execution plans of SQL statements
• Create a SQL script that stores the statistics in the database
Read more about SQL Trace files and TKPROF here