Monday, March 14, 2011

Oracle: How to find last query executed

One more sql script to remember - How to find the last query executed:
select sql_text from v$sql 
where first_load_time=(select max(first_load_time) from v$sql)

Or may be in a bit more useful way:
select * from v$sql order by first_load_time desc

And even more useful, show only queries that contain MY_TABLE:
select * from v$sql where sql_text like '%MY_TABLE%' order by first_load_time desc

Update: Pay attention that the solution is not quite correct if you use the prepared statements. For prepared statements there will be one row added to the table per statement's parsing. So if you reused the same statement (which is a good practice from performance point of view), you won't get a new record in a table.


Recommended Reading

1. Oracle Essentials: Oracle Database 11g
2. Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g
3. Oracle SQL By Example (4th Edition)

3 comments:

Kent said...

Hello, thanks for the block! I like it!! If I want to know how to get the last 25 queries...do you know how to get it? can you help me?
Thank you!

Kent said...

Hello, thanks for the block! I like it!! If I want to know how to get the last 25 queries...do you know how to get it? can you help me?
Thank you!

Tarlog said...

Basically, if you don't use the prepared statements, so your queries are parsed each time, you can write something like:

select * from v$sql order by first_load_time desc where rownum < 25

But if you use the prepared statements, there are not going to be new rows in the v$sql table for each execution.