Thanks for the question, Ravikanth.
Asked: September 09, 2020 - 6:20 pm UTC
Answered by: Connor McDonald - Last updated: September 16, 2020 - 1:54 am UTC
Category: Database Administration - Version: Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production
Viewed 100+ times
We have an application that monitors applications, detects anomalies, does correlation between metrics, performs Root Cause Analysis based on a few machine learning algorithms.
We are planning onboard oracle monitoring for this application with a few metrics like below. Could you please suggest where we could get some baseline monitoring SQL's to plugin to our application, especially the SQLs that are used to generate ASH/AWR reports. We want to start small and expand over a period of time.
Redo (Mb per second)
Transactions per second
Latency: Log file Sync, Log file parallel write, single block read all in Avg Ms
IO MB/per sec
Physical Reads MB/sec
Physical writes MB/sec
DB CPU % usage
Logons per sec
Logical Reads Mb/sec
RMAN IO mb/ms
Stale statistics on objects
Top Objects by Size, growth, Avg growth per day, month
Space growth (total vs used), Avg per day, month
and we said...
There is no real "secret" to the SQL's that are used to derive ASH/AWR reports.
Simply throw a sql trace on them and they'll be there right there in the trace file.
The list you've put together seems a like a pretty thorough set already, but I stress, the best monitoring tools are those most aligned to *business* requirements, eg
"A sale of a mobile phone takes between 25 and 32ms, so alert me when I get one over 50ms"
That is much more valuable than "My log file sync is climbing to more than 30ms". Those kind of metrics are useful for *analysis* but only *after* you have an business issue.