Past queries

[THoey]

I had an application manager come to me this morning saying that about a half hour ago, that Oracle bogged down for about five minutes and he suspected that someone had run a long, resource eating query. I asked if it was still slow and he said no, it was fine now. I tried looking in DBA Studio for anyone with any long queries, but didn't see any.

Is there a tool, view, or a specific table that I could run a script against, to see the SQL that was recently run against the database?

Thanks in advance... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Try querying v$sqlarea.

select sql_text, disk_reads, buffer_gets, executions, first_load_time from v$sqlarea
where buffer_gets > 100000
order by first_load_time;

You should be aware that disk_reads and buffer_gets are cumulative. If the query has executed ten times, disk_reads and buffer_gets will both contain cumulative totals from all ten executions. A more realistic measurement of the cost of the query would be disk_reads/executions and buffer_gets/execution.

Disk_reads reflects disk i/o - the number of times that Oracle needed a block that wasn't already in the db buffer cache. Buffer_gets is more like logical reads - the number of times that Oracle fetched data from the db buffer cache, regardless of whether a disk read was actually needed

 

[THoey]

Thanks again Karluk. But, I am going to take a guess that this query would require DBA access or other appropriate permission, as I get "table or view does not exist". Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[sem]

This view as well as all v$ views belongs to SYS, so you have to obtain dba or select any table role. You may also try to use sys.v$sqlarea.