Oracle Scratchpad


Back to main Tutorial Page


Trouble Shooting and Tuning – Agenda

The users are complaining right now ! How do I find the problem – what’s the short term fix – what’s the long term fix? The overnight batch ran on until 9:30 this morning when it’s supposed to complete at 5:00 a.m. – what went wrong?

Good performance starts with good design – which means you have to know how much data you have, where to put it, and how you’re going to use it. Working out the best strategies is called tuning. After go-live you don’t do tuning, you do trouble-shooting, and the strategies have to change to suit the circumstances.

In this one-day course, we learn how to think around design issues (the tuning) as well as looking at strategies – and some fixes – for addressing implementation problems (the trouble-shooting). The final session of the course examines some of the dynamic performance views, what you can do with them, and how their contents are reported in the Statspack and AWR reports.

Note: There is an alternative version of this day covering sessions one, two and four, and closing with a session where the attendees are invited to supply production reports from Statspack or the AWR for public analysis and review. The reports should be the text option generated by spreport.sql or awrrpt.sql, ideally based on a snapshot interval of one hour, running (for statspack) at level seven.

Session 1
1.5 hours

Trouble-shooting or Tuning

What’s the difference? What are the strategies. Why tuning is hard, trouble-shooting is easy and design is important. Key targets for producing a well-tuned system on day one. How to approach design, and plan for trouble-shooting, and how to be on the alert for activities where the resource utilisation is unreasonably high.

Break – coffee and informal discussion: 30 minutes
Session 2

1.5 hours

Looking for Problems

If you know the sorts of problems your design is going to face, then you There are three major classes of problem, but just one source of (Oracle) information. It’s important to recognize what class of problem you’re attacking and pick the best way of using the available information to identify the source of your problem. We outline examples of the three classes and the corresponding approaches, and end with a detailed example of problem-solving.

Break – Lunch and informal discussion: 1 hour
Session 3

1.5 hours

Tactical Fixes

Methods, workarounds, dirty tricks and parameters for dealing with classic performance problems when the system is in production. Some of the examples we cover are specific to particular production issues, some are generic quick fixes that can be applied across the board. All fixes need careful examination of costs, risk, and benefits. In this session we consider examples of strategies that are most likely to be worthwhile.

Break – coffee and informal discussion: 30 minutes
Session 4

1.5 hours

V$, X$ and Statspack/AWR

It’s a good idea to be familiar with just a few of the dynamic performance views – and there are a couple of items in the still hidden away in the X$ objects that can add be useful in special cases. This session will present some ideas on how to take advantage of the dynamic performance objects directly, and then move on to some of the more useful information they supply. In particular, we review the Statspack and AWR interfaces to the dynamic performance views – closing with a case study of using Statspack to solve a performance problem.



Back to main Tutorial Page

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

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.

Website Powered by