Thanks for the question, Ravikanth.
Asked: September 09, 2020 - 6:20 pm UTC
Last updated: September 16, 2020 - 1:54 am UTC
Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Viewed 1000+ times
You Asked
Hello,
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
Network MB/sec
Logons per sec
Logical Reads Mb/sec
File Sync(Avg/ms)
RMAN IO mb/ms
Waits
Locks
Top SQL’s
Stale statistics on objects
Top Objects by Size, growth, Avg growth per day, month
Space growth (total vs used), Avg per day, month
Thanks,
Ravi B
and Connor 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.
Is this answer out of date? If it is, please let us know via a Comment