on 11g R2 on Win 2008.
We can not use AWR report.
Windows System administrator told that on 23/04/13 at 22:39 oracle.exe process used 62% of CPU.
I wanted to know what was doing my database. Then I ran the following :
SQL> select count(*) from V$SQLAREA where last_load_time
3 to_date('23.04.2013 22:00:00', 'dd.mm.yyyy HH24:MI:SS')
4 AND to_date('23.04.2013 23:00:00', 'dd.mm.yyyy HH24:MI:SS')
And nothing special in alertlog.
Does it mean that database was doing nothing ? Where else should I look to see what was happening ?
Thanks for help.
You can install and configure Perfstat (see %ORACLE_HOME%/rdbms/admin/spdoc.txt) Perfstat is free and doesn't require a licence option (as AWR does)
Of course, this can't help you identify the issue that occured on 23-April.
Hemant K Chitale
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.
LogMiner won't show queries and it is likely that queries accounted for the CPU usage.
As for scheduling statspack, Oracle supplies a script -- spjob.sql, I think (or a similar script file name).
Hemant K Chitale
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.