Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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 12.2.0.1.0 - 64bit Production

Viewed 100+ 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 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.