I have Oracle 11g attached to a Performance Test environment. A performance test runs for two hours. After the run I want to programmatically extract a variety of in key indicators from the database to store with the results.
What commands would I run before and after the test to collect key indicators, ideally grouped in 5 minute totals.
- CPU
- Memory
- Total SQL requests
- Logical IO
- Physical IO
And bottleneck indicators, (various waits for locks and latches).
I will accept a handy link to an article or a collection of useful SQL commands to explore.
Best Answer
This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page which requires the diagnostics package. There is a certain irony to running this script through SQL Server reporting services which is beyond the scope of this question.
This query is not as good as what Grid displays but it's close.