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?

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
/

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

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.