Capture all SQL run between two points in time

From : http://www.shutdownabort.com

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
    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 (+)
    /
  • 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
    /

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: