Skip to Main Content
  • Questions

  • performance stats for all entries in query log records (audit trail)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aditya.

Asked: December 20, 2018 - 8:02 am UTC

Last updated: December 24, 2018 - 1:56 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

I need to find these stats :
cpu time
execution time
io count
app id
proc id of procedure used in query

for all queries which are being logged dba audit trail (XML)

I have looked into V$SQL and dba_hist_sql stat but these contain only snapshots and the whole history


can u pls suggest how i can achieve my goals here

and Connor said...

We don't keep the details for *every* execution, the overheads are too high.

Places to look for more detail:

DBA_HIST_SQLSTAT
DBA_HIST_ACTIVE_SESS_HISTORY

I wrote a little routine to capture all ASH data rather than just the sampling of historical data here

https://connor-mcdonald.com/2017/12/12/iash-my-infinite-ash-routine/

Hope this helps

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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