Ask the Expert

Getting a full history of all SQL queries run on the database

My co-workers are asking me to keep a full history of all SQL queries run against our production database (Oracle 9.0.1.3.1). What would be the best solution in your opinion? SQL Trace? Trigger?

    Requires Free Membership to View

You can get the information you want from the database itself and don't need to write or do anything "special". You can get this information in at least the two ways described below.

First, there are several V$ views (dynamic performance views) that Oracle maintains which can provide you with the SQL statements that have been run against your database such as V$SQL, V$SQLAREA, V$SQLTEXT. Using these views, you can get performance statistics about the SQL that has run, how often a statment has been executed and many other performance metrics. Here's a query that would show you the most resource intensive queries that have been run against your database:

col stmtid      heading 'Stmt Id'               format    9999999999
col dr          heading 'PIO blks'              format   999,999,999
col bg          heading 'LIOs'                  format   999,999,999
col sr          heading 'Sorts'                 format       999,999
col exe         heading 'Runs'                  format   999,999,999
col rp          heading 'Rows'                  format 9,999,999,999
col rpr         heading 'LIOs|per Row'          format   999,999,999
col rpe         heading 'LIOs|per Run'          format   999,999,999
col sqltxt	heading 'SQL Statement'		format A25 word_wrapped

set pause     on
set pagesize  30
set pause     'More: '
set linesize  120

select  s.hash_value stmtid
       ,t.sql_text sqltxt
       ,sum(s.disk_reads) dr
       ,sum(s.buffer_gets) bg
       ,sum(s.rows_processed) rp
       ,sum(s.buffer_gets)/greatest(sum(s.rows_processed),1) rpr
       ,sum(s.executions) exe
       ,sum(s.buffer_gets)/greatest(sum(s.executions),1) rpe
 from v$sql s, v$sqltext t
where s.command_type in ( 2,3,6,7 )
  and s.hash_value = t.hash_value
group by s.hash_value, t.sql_text
order by 5 desc
/
Second, you can use Log Miner to "mine" the redo logs to get every SQL statement (all DML...not SELECT statements) that has been executed against your database. The Log Miner utility allows you to review the actual contents of the redo logs which contain exactly what has been processed on your database (all INSERTs, UPDATEs, DELETEs). I don't have enough room here to go into details about Log Miner, but will refer you to the Oracle Supplied Packages Reference guide under DBMS_LOGMNR package for more info.

The bottom-line is that you should be able to use information already stored and available to you through Oracle's own "built-in" tracking mechanisms and not have to write anything on your own.... YEAH!!!

For More Information


This was first published in August 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: