Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aidan.

Asked: May 01, 2020 - 8:13 am UTC

Last updated: May 07, 2020 - 4:16 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

I'm building Grafana dashboards to present out Oracle RAC database statistics we care about for senior management. I need a breakdown of the REDO generated by database service per hour? Can you provide assistance on how I can get this information from the data dictionary. I'm also looking at UNDO stats. I want a breakdown by database service of consumption of undo segments. I'm looking to spot trends in the data so we can tune our applications, identify what processes is chewing up undo and redo quickly. I know there are convoluted ways of getting this but I'm seeking out your expert advice.

and Connor said...

If you look at v$service_stats you can see the stats we collect at the service level automatically for you

SQL> select distinct stat_name from v$service_stats order by 1;

STAT_NAME
----------------------------------------------------------------
DB CPU
DB time
Main function call count
application wait time
cluster wait time
concurrency wait time
db block changes
execute count
gc cr block receive time
gc cr blocks received
gc current block receive time
gc current blocks received
logons cumulative
opened cursors cumulative
parse count (total)
parse time elapsed
physical reads
physical writes
redo size
session cursor cache hits
session logical reads
sql execute elapsed time
user I/O wait time
user calls
user commits
user rollbacks
workarea executions - multipass
workarea executions - onepass
workarea executions - optimal


and hence, you'll automated collection over intervals of that with DBA_HIST_SERVICE_STAT

If you can limit yourself to that, that will make thing a lot easier because you don't need to "roll your own".

You can see from the list above, you'll get redo directly via "redo size" but we don't collect undo, but I'd contend that "db block changes" and perhaps "user commits" should be sufficient in terms of things to keep an eye on.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 06, 2020 - 6:57 am UTC

Thanks Conor, I will certainly check this out
Connor McDonald
May 07, 2020 - 4:16 am UTC

glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.