This content has been marked as final. Show 6 replies
without AWR/ASH your options for retrospective troubleshooting are very limited. You can use statspack -- but unlike AWR, it requires the DBA to either launch it manually, or manually schedule a database job for this purpose.
V$ views won't help you because they either represent statistics accumulated since the last instance startup, or the current state of the database (like V$SQL which externalizes the current contents of the library cache -- so if a cursor is aged out, you won't find it there). What you need is differential stats -- i.e. differences between snapshot (e.g. before and after a problem), there are no default mechanisms in the database for that (other than AWR or analogs).
You query is meaningless because:
- cursors may have been aged out
- cursors may have later last_load_time (because after being executed in the specified interval, they may have been active at a later time)
- even if you managed to get an accurate number of cursors accessed during a certain interval, what would you do with it?
Pretty much your only option is to do log mining, if you have archived logs for the period of interest. It's time consuming, but it will give you the entire history of all SQL run, if you really need it.
I agree with the other posters and recommend Statspack as well. It's free, and the provided scripts do the work of scheduling the job that creates Statspack snapshots.
I also agree with Hemant in that Log Miner is not a suitable tool for this. Log Miner is more useful when you need to track down certain changes that were made to the database, and generally requires minimal supplemental logging to be enabled.