Oracle is there a tool to trace queries like Profiler for sql server

Question

i work with sql server  but i must migrate to an application with Oracle DB  for trace my application queries  in Sql Server i use wonderful Profiler tool  is there something of equivalent for Oracle

User · Answer

Apparently there is no small simple cheap utility that would help performing this task. There is however 101 way to do it in a complicated and inconvenient manner.

Following article describes several. There are probably dozens more... http://www.petefinnigan.com/ramblings/how_to_set_trace.htm

User · Answer

alter system set timed statistics true   --or   alter session set timed statistics true --if want to trace your own session   -- must be big enough   select value from v parameter p where name  max dump file size     -- Find out sid and serial  of session you interested in    select sid  serial  from v session  where    your search params      --you can begin tracing with 10046 event  the fourth parameter sets the trace level 12 is the biggest     begin     sys dbms system set ev sid  serial   10046  12        end    --turn off tracing with setting zero level   begin    sys dbms system set ev sid  serial   10046  0       end      possible levels  0 - turned off 1 - minimal level  Much like set sql trace true 4 - bind variables values are added to trace file 8 - waits are added 12 - both bind variable values and wait events are added     --same if you want to trace your own session with bigger level   alter session set events  10046 trace name context forever  level 12     --turn off   alter session set events  10046 trace name context off     --file with raw trace information will be located    select value from v parameter p  where name  user dump dest    --name of the file   trc  will contain spid    select p spid from v session s  v process p  where s paddr p addr  and    your search params      --also you can set the name by yourself   alter session set tracefile identifier  UniqueString      --finally  use TKPROF to make trace file more readable   C  ORACLE admin databaseSID udump gt  C  ORACLE admin databaseSID udump gt tkprof my trace file trc output my file prf TKPROF  Release 9 2 0 1 0 - Production on Wed Sep 22 18 05 00 2004 Copyright  c  1982  2002  Oracle Corporation  All rights reserved  C  ORACLE admin databaseSID udump gt    --to view state of trace file use   set serveroutput on size 30000  declare   ALevel binary integer  begin   SYS DBMS SYSTEM Read Ev 10046  ALevel     if ALevel   0 then     DBMS OUTPUT Put Line  sql trace is off      else     DBMS OUTPUT Put Line  sql trace is on      end if  end      Just kind of translated http   www sql ru faq faq topic aspx fid 389 Original is fuller  but anyway this is better than what others posted IMHO

User · Answer

Oracle  along with other databases  analyzes a given query to create an execution plan   This plan is the most efficient way of retrieving the data   Oracle provides the  explain plan  statement which analyzes the query but doesn t run it  instead populating a special table that you can query  the plan table    The syntax  simple version  there are other options such as to mark the rows in the plan table with a special ID  or use a different plan table  is   explain plan for  lt sql query gt    The analysis of that data is left for another question  or your further research

User · Answer

GI Oracle Profiler v1 2    It s a Tools for Oracle to capture queries executed similar to the SQL Server Profiler     Indispensable tool for the maintenance of applications that use this database server    you can download it from the official site iacosoft com

User · Answer

The Catch is Capture all SQL run between two points in time  Like the way SQL Server also does   There are situations where it is useful to capture the SQL that a particular user is running in the database  Usually you would simply enable session tracing for that user  but there are two potential problems with that approach    The first is that many web based applications maintain a pool of persistent database connections which are shared amongst multiple users   The second is that some applications connect  run some SQL and disconnect very quickly  making it tricky to enable session tracing at all  you could of course use a logon trigger to enable session tracing in this case     A quick and dirty solution to the problem is to capture all SQL statements that are run between two points in time   The following procedure will create two tables  each containing a snapshot of the database at a particular point  The tables will then be queried to produce a list of all SQL run during that period   If possible  you should do this on a quiet development system - otherwise you risk getting way too much data back    Take the first snapshot Run the following sql to create the first snapshot   create table sql exec before as select executions hash value from v sqlarea    Get the user to perform their task within the application  Take the second snapshot   create table sql exec after as select executions  hash value from v sqlarea    Check the results Now that you have captured the SQL it is time to query the results    This first query will list all query hashes that have been executed   select  aft hash value from sql exec after aft left outer join sql exec before bef   on aft hash value     bef hash value  where aft executions  gt  bef executions    or bef executions is null      This one will display the hash and the SQL itself  set pages 999 lines 100 break on hash value  select  hash value  sql text from    v sqltext where   hash value in       select  aft hash value     from sql exec after aft     left outer join sql exec before bef       on aft hash value     bef hash value     where aft executions  gt  bef executions        or bef executions is null    order by     hash value  piece     5  Tidy up Don t forget to remove the snapshot tables once you ve finished   drop table sql exec before    drop table sql exec after

User · Answer

Try PL SQL Developer it has a nice user friendly GUI interface to the profiler  It s pretty nice give the trial a try   I swear by this tool when working on Oracle databases   http   www allroundautomations com plsqldev html gclid CM6pz8e04p0CFQjyDAodNXqPDw

User · Answer

You can use The Oracle Enterprise Manager to monitor the active sessions  with the query that is being executed  its execution plan  locks  some statistics and even a progress bar for the longer tasks   See  http   download oracle com docs cd B10501 01 em 920 a96674 db admin htm 1013955  Go to Instance -  sessions and watch the SQL Tab of each session    There are other ways  Enterprise manager just puts with pretty colors what is already available in specials views like those documented here   http   www oracle com pls db92 db92 catalog views remark homepage  And  of course you can also use Explain PLAN FOR  TRACE tool and tons of other ways of instrumentalization  There are some reports in the enterprise manager for the top most expensive SQL Queries  You can also search recent queries kept on the cache

User · Answer

This is an Oracle doc explaining how to trace SQL queries  including a couple of tools  SQL Trace and tkprof   link

User · Answer

There is a commercial tool FlexTracer which can be used to trace Oracle SQL queries

User · Answer

I found an easy solution  Step1  connect to DB with an admin user using PLSQL or sqldeveloper or any other query interface  Step2  run the script bellow  in the S SQL TEXT column  you will see the executed queries  SELECT              S LAST ACTIVE TIME        S MODULE   S SQL FULLTEXT    S SQL PROFILE   S EXECUTIONS   S LAST LOAD TIME   S PARSING USER ID   S SERVICE                                                                        FROM  SYS V  SQL S    SYS ALL USERS U WHERE  S PARSING USER ID U USER ID   AND UPPER U USERNAME  IN   oracle user name here      ORDER BY TO DATE S LAST LOAD TIME   YYYY-MM-DD HH24 MI SS   desc    The only issue with this is that I can t find a way to show the input parameters values for function calls   but at least we can see what is ran in Oracle and the order of it without using a specific tool

User · Answer

Seeing as I ve just voted a recent question as a duplicate and pointed in this direction        A couple more - in SQL Plus - SET AUTOTRACE ON - will give explain plan and statistics for each statement executed   TOAD also allows for client side profiling   The disadvantage of both of these is that they only tell you the execution plan for the statement  but not how the optimiser arrived at that plan - for that you will need lower level server side tracing   Another important one to understand is Statspack snapshots - they are a good way for looking at the performance of the database as a whole  Explain plan  etc  are good at finding individual SQL statements that are bottlenecks  Statspack is good at identifying the fact your problem is that a simple statement with a good execution plan is being called 1 million times in a minute

[sql-server] Oracle: is there a tool to trace queries, like Profiler for sql server?

Examples related to sql-server

Examples related to oracle

Examples related to profiler