Oracle DBAs: Any way to see what queries my ASP.NET app is running?

Question

I have an ASP.NET application that uses Oracle for persistence and queries it via ADO.NET and stored procedures.

I have the full version of TOAD and administrative rights to the database.

Is there any way to view all queries issued by the application over the last 10 minutes?

Answer 1

I don't know about over the past 10 minutes, but if you use the Session Browser (off the toolbar, or Database Menu -> Monitor -> Session Browser) you can view the current statement of a connected user (expand w3wp.exe, then sort by machine name to find the correct connections), then just keep pressing the refresh button as you navigate through your app.

Breaking away from the confines of the TOAD GUI, you could try manually querying v$sqlarea:

  select * 
    from v$sqlarea
   where upper(module) = 'W3WP.EXE'
     and parsing_schema_name = 'MY_CONNECTING_SCHEMA'
order by last_active_time desc

Answer 2

Here is a query I use to examine slow queries: (Might get you started)

SELECT   username, sql_text, elapsed_time, executions, optimizer_cost, loads,
         fetches, rows_processed,
         DECODE (command_type,
                 2, 'Insert',
                 3, 'Select',
                 6, 'Update',
                 7, 'Delete',
                 26, 'Lock Table',
                 35, 'Alter Database',
                 42, 'Alter Session',
                 44, 'Commit',
                 45, 'Rollback',
                 46, 'Savepoint',
                 47, 'Begin/Declare',
                 command_type
                ) AS cmdtype,
         first_load_time, last_load_time, module
    FROM v$sql, v$session_longops
   --longops is a view of statements that took longer than 6 seconds
WHERE    sql_address(+) = address AND sql_hash_value(+) = hash_value
ORDER BY elapsed_time DESC, executions, address, hash_value, child_number;

Answer 3

You maybe don't know the SID of your applications user session. But you certainly know the schema owner of the objects, the applications is accessing.

To restrict your result by time you can use awr snapshots. (automatic workload repository)

More about AWR: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#PFGRF02601

--Create a snapshot
exec dbms_workload_repository.create_snapshot;

--Wait 10min and create another snapshot;
exec dbms_workload_repository.create_snapshot;

--This Statement does the following:
--1. Get the recent two Snapshot IDs
--2. Select all SQL IDs which are execute between the last two snapshots
--3. Filter the SQL IDs which are accessing objects of a certain owner (Substitute 'APP_OWNER' with the schema owner of your application)
--4. Select the SQL text of all these SQL IDs

SELECT sql_id,
       sql_text
FROM   dba_hist_sqltext
WHERE  sql_id IN (SELECT DISTINCT sql_id
                  FROM   dba_hist_sql_plan
                  WHERE  sql_id IN (SELECT sql_id
                                    FROM   dba_hist_sqlstat
                                    WHERE  snap_id BETWEEN (SELECT max(snap_id) - 1
                                                            FROM   dba_hist_snapshot) AND (SELECT max(snap_id)
                                                                                           FROM   dba_hist_snapshot))
                         AND object_owner = 'APP_OWNER')
/