2 Replies Latest reply: Feb 21, 2013 8:44 AM by IvanBlanarik RSS

    SQL Monitoring using SQL Queries

    Arun Kumar Gupta
      I am using Oracle 10 g DB. I have an application which has Oracle as DB. Its a multiuser application. During the testing of one of the page we found that saving couple of records takes huge time (50-60 seconds). I dont have access to the logic written in application code to know which all queries gets executed when user click on save button.

      I know that there is SQL Monitoring available in OEM why which you can find out the queries which got executed and also the execution time for those queries.

      However I dont have OEM access.

      Can you please let me know teh set of queries which probably will use few of teh V$ data dictionary views and provide me the same details as generated in the Monitoting report generated by OEM ?

      I want to see all the queries which got executed in last defined time (say 10 mins, 1 hr etc) , same way as it is done in OEM and not just the last sql.


        • 1. Re: SQL Monitoring using SQL Queries
          You can see SQL that has been recently executed using the v$SQL view - if you have permission to see it
          • 2. Re: SQL Monitoring using SQL Queries
            I recommend you to install STATSPACK - it's a free tool that can be used instead of the Automatic Workload Repository (that is used in OEM).
            This tool creates snapshots of V$ views in defined time period, so it enables you to see long runing queries in history.

            If I were you I would run SQL trace just before the problematic part and then stop it just as it has finished. It would be better to run trace only for the problematic session or module - not for the whole database. SQL trace generates a file with all the executed queries, explain plans, stats, ...

            In extreme case just create copy of v$sql before and after the problematic part and compare them (new sql_id, elapsed_time delta, ...)