October 8, 2008, 10:41 pm
One of the most frustrating things about the Cost Based Optimizer in Oracle is the sometimes erratic performance. It can seem almost random at times. Resolving these issues can also be challenging. It’s a little like taking your car to the mechanic when it makes a funny noise. It never seems to make the noise when the mechanic is there. Fortunately we have ASH/AWR which captures a ton of information about what our databases are doing.
Here’s a couple of scripts that I find useful.
The first one can be used to show statements that have experienced significant variances in execution time (it can be modified to look for variances in the amount of logical i/o, but I’ll leave it as an exercise for the reader). I called the script unstable_plans.sql. It uses an analytic function to calculate a standard deviation on the average elapsed time by plan. So the statements that have multiple plans with wild variations in the response time between plans will be returned by the script. The script prompts for a couple of values. The first is minimum number of standard deviations. The second is the minimum elapsed time (I usually don’t care if a statement executes sometimes in .005 seconds and sometimes in .02 seconds, even though this is a large swing statistically). Both these inputs are defaulted by the way.
Continue reading ‘Unstable Plans (Oracle Plan Stability/Instability)’ »
October 7, 2008, 5:37 pm
Well I guess I have officially joined the cool crowd since I am now the proud poppa of a bouncing baby MAC.
The best things about it so far are:
- It runs windows so I still have a safety net if I get to feeling totally lost. I’m using VMWare which allows me to jump back and forth.
- It runs some flavor of unix as it’s operating system. (so I can run top, ps, sar, etc… – it’ BSD but it beats the crap out of DOS)
- It’s about a million pounds lighter and half the size of my old Sony laptop.
- It’s faster than my old laptop.
- At 4G, it’s got 4 times the memory my old laptop had.
- The video iChat thing is pretty cool.
- It’s the worlds biggest iPod.
It is pretty cool but I just hate the process of going from one machine to another. Even without the frustration of the keyboard changes that the Mac inflicts, changing just makes for some amount of unproductive time. I have to do a presentation next week in Fort Worth at an Oracle Technology Luncheon thing (here’s a link to the presentation: 11g Advanced Features) So anyway, I am working on the Power Point slides using the MAC and it makes me feel like I’m typing with mittens on. I’m sure that will get better over time. I am finding that there are a lot of options with this setup though. Almost everything I am doing can be done in more than one way. Like I can do my calendar stuff in MS Office or in iCal (or on my phone for that matter).
I have to admit that old laptop was starting to have a lot of problems. Wireless was flakey, it would freeze every other day or so, and last week the keyboard quit working. Kind of reminded me of the PC/MAC commercial where the PC was sick.
Anyway, so far so good. Now if I can just figure out how to make the F11 key work in windows instead of turning on the speaker I should be in good shape.
