|
Capture all SQL run between two points in time
tnsManager - Distribute tnsnames the easy way and for free!
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.
- 1. 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
/
- 2. Get the user to perform their task within the application
- 3. Take the second snapshot
create table sql_exec_after as
select executions
, hash_value
from v$sqlarea
/
- 4. 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_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/
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_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
)
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
/
|
|
|
