Re: v$sqlarea does not give latest SQL statements!

qazmlp1209_at_rediffmail.com wrote:
> How exactly we can find the last 'N' number of SQL statements that were
> executed in the Oracle 9i(9.2.0.4.0)?
>
> I tried to do the following, but it did not help:
> $select SQL_TEXT,FIRST_LOAD_TIME from v$sqlarea order by
> FIRST_LOAD_TIME;
>
> I know that the application which is using the Oracle DB does some
> updates in the Database. But, the above query did not show these SQL
> statements at all. Wherelse I can check this?

Hmmm the SQL for recent updates really should be in the V$SQLAREA. Did you really scan every single query?

Try filtering the text: for UPDATEs only, for the user of interest, and also ordering by most RECENT first load:

SELECT SA.sql_text, SA.first_load_time
FROM v$sqlarea SA, all_users AU
WHERE UPPER(SA.sql_text ) LIKE '%UPDATE%'   AND SA.parsing_schema_id = AU.user_id
  AND AU.username = 'MYUSER'
ORDER BY SA.first_load_time DESC;

This ought to weed out a lot of queries. If you get no rows, remove the filter on UPPER(sql_text), then the filter on your schema of interest.

I believe there are new columns LAST_LOAD_TIME and LAST_ACTIVE_TIME that will give even better view of what has actually been executed (as opposed to first parsed), but these are available only in versions beyond your 9.2.0.4.0

--JH Received on Tue Jan 23 2007 - 11:45:50 CST