My co-workers are asking me to keep a full history of all SQL queries run against our production database (Oracle...
126.96.36.199.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 /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
- 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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.