Oracle Scratchpad

Public Appearances


27th March 2020 Warsaw, Poland: POUG Workshop 2020: I’ll be doing a wrap-up presentationat the end of the beginners’ stream on using the AWR (or Statspack) report as a starting point for trouble shooting. This will be a 90 minute lecture – with a little pause for thought and refreshment in the middle.

Beginners’ Guide to using the AWR

In this session we will draw pull together the strands from the three previous sessions as we get to grips with the information that’s available in the Automatica Workload Repository (AWR) or (for those without the diagnostic and performance licences, or running Standard Edition) the free Statspack utility.

If a user can tell you exactly where they are having a problem – which report is taking too long to run, which screen is responding very slowly, which drop-down menu is oozing like honey instead of dropping like a stone – then there are high precision strategies you can use to identify the cause of the problem. The AWR is there to help on those occasions when the database appears to be generally “unhealthy”, or pushing the limits of the hardware, or showing random variations in performance.

The three previous sessions of the stream will have examined:

    • How the core RDBMS engine works
    • Wait Events and DB time
    • Execution Plans

In this session we learn how the AWR can help us build a picture of what the engine is doing and give us some clues about how we can reduce the core activity cut back on basic resource consumption; we learn to recognise the relationships between the wait events and instance activity stats that tell us why the database is working harder than we expect; and we find easy ways to track down expensive activity and understand why the underlying execution plans may need to be addressed.

The bottom line on using the AWR is that it allows you to find the worst examples of excessive resource  usage caused by one of two key problems

    • You’re doing something the hard way
    • You’re doing something too often

The numbers will tell you how to reduce the workload – but you still have to remember that that might not make any visible (response time) difference to the end user.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by