bootom rows in a

[deswal_aja]

to isolate errors i want to query v$sqlarea for the query that is fired as soon as an application user takes a particular action.
as v$sqlarea is a view iam unable to use rowid and rownum to get to the last few rows.
iam unable to make out the table from where v$sqlarea derives the sql_text.

how to handle?

[bmycroft]

This query is the one I use to get the top ten queries ordered by executions.

SELECT /*+ RULE */
substr(rownum,1,2) as "No",
sql_text,
executions,
buffer_gets,
disk_reads,
rows_processed
FROM v$sql st, dual
WHERE -1*st.executions = DECODE(dual.dummy(+), 'X',NULL, NULL)
AND rownum <= 10
ORDER BY st.executions DESC

The decode and join to dual forces the order of the rows returned and gets around the problem of restricting by rownum with an order by. You should be able to modify this to pull out the sql just fired. Incedentally, every sql your user fires will appear in the v$sqlarea so how do you know how many rows you want to restrict to?

-Bob