Re: Strictly Enforcing Query Plan Stability

  From a developer's viewpoint, I've watched this whole discussion in both shock and amusement. If the developers aren't skilled enough to write decent queries, either train them or fire them. If you have to go through every inch of their code, either they are incompetent or you are overbearing. If you don't have a little (just a little) trust in the Oracle parser, then you're fighting yourself.

  Again, from a developer's viewpoint, I would recommend the following:

1. Let the Oracle parser do it's job. It's fairly good, and can be overridden with hints.
2. Ask (don't demand) the developers to learn and help to train them in SQL tuning and testing practices. I'd recommend the book "Oracle SQL High Performance Tuning" by Guy Harrison - It's written for developers. Teach them to run traces and tkprof it. Give them plug and play explain plan scripts. If you'd like the scripts I use then send an Email. Basically, give them the work and they'll eventually thank you for it.
3. If the developers don't have a Development database that matches Production in both size and data, they can't tune. Period, end of story. If you want to do their job, then fire them and start coding.
4. No database is 100%. No code is 100%. Whether it's a patch, data entry, or untested little piece of code, nothing is perfect. Of course, striding for 100% is still the goal as long as it doesn't have a severe impact on getting things into production to begin with. If you don't allow programs to get into Production, how long do you think you'll have a job?
5. Start hammering Oracle with requests for a plan stability option (although that goes against the Cost based optimizer they are pushing). I've seen several situations where plan stability would have caused Production to fail, so be careful on what you wish for.

-- 
---------------------------------------------------------------------
Chad Thompson, 
Programmer Analyst: VB, VC++, PLSQL, Oracle HRMS, Security Apps
                            Home: thomp901_at_micron.net

Thou shalt not tick off the dragon.... for thou art crunchy and 
taste good with ketchup.
---------------------------------------------------------------------


frank wrote:


> 

> As you say: bugs can occur. The optimizer will do a fts when and if that is more

> efficient to do than an index scan, followed by ta by rowid.

> If that is not the case, then that's a bug in the optimizer.

> And: index reads, followed by table access by rowid is *not* always the

> best way to get your results!

> I keep demonstrating that to developers, and the result is always the same:

> astonishment and shock.

> 

> Greg Stark wrote:

> 

> > frank <fbortel_at_home.nl> writes:

> >

> > > I have never ever seen that happen, or -probably more important- heard about

> > > such an event.

> > >

> > > > I'm very serious. This seems like a major flaw in Oracle that makes it

> > > > fundamentally unsuitable for mission critical OLTP applications. It means the

> > > > operations people cannot guarantee that they know what Oracle is doing at all

> > > > time and cannot guarantee that the application will continue to work as the

> > > > data changes.

> > >

> > > Well, who come there are some many of them around? All of these designers

> > > and users must have less high standard than you have.

> >

> > This sounds like a typical scenario where Oracle is being used for purposes

> > that it hasn't in the past. Perhaps you've been working exclusively on

> > "traditional" database applications like data warehouses and environments with

> > very tightly controlled code and very rigorous testing regimen. However the

> > world has changed and Oracle is scrambling to catch up.

> >

> > > > You make a fundamental assumption that the optimizer is infallible. Have you

> > > > actually tried to use the damned thing?

> > >

> > > No, I'm not. That's why hints were invented. And yes, I have used it.

> > >

> > > > It's very helpful for initially finding good query plans in development

> > > > but it's not appropriate to be using it at run-time.

> > >

> > > Why would that be?

> >

> > So you don't think the plan stability features introduced in 8i are useful for

> > anything? If the optimizer changes behaviour it's infallibly because the new

> > plan is better than the old one? If a new query is introduced and it's checked

> > with one data set then it's undoubtedly going produce the same plan or better

> > on the production system?

> >

> > Perhaps some concrete examples would be clearer:

> >

> > I write a web site involving basic queries involving some joins etc. I test it

> > out on a sample database, I test it out on a QA system, including checking the

> > plans and disk i/o etc. It goes into production.

> >

> > It gradually slows down as more users sign on, that's expected, but it's

> > basically satisfactory. I'm monitoring the response time planning to do some

> > tuning well before the response time becomes too slow. All of the sudden

> > everything grinds to a complete halt, Oracle has decided to start using full

> > table scans because the tables have passed some arbitrary size that the

> > optimizer decided was worth doing full table scans. It turns out Oracle was

> > overeager in switching to full table scans and they were actually slightly

> > slower. Had it stuck with the existing plans at least I could have continued

> > to monitor the response time and react in plenty of time to deal with the

> > performance degradation.

> >

> > That scenario is handled by the plan stability features in 8i. However the

> > next scenario is what I'm actually complaining about:

> >

> > Now I have one of the developers implement a new web page. I check the code

> > and test it on the QA system. We establish stored outlines for every query

> > with an acceptable execution plan with acceptable response times. However

> > either due to a fault in my testing procedure or perhaps due to some obscure

> > corner case that wasn't anticipated at all, there's a query in the new code

> > that I don't verify. It turns out that this query does a full table scan on a

> > large table. When this code is placed into production and receives hundreds of

> > hits per second that full table scan completely hammers Oracle saturating the

> > disk i/o and blocking every process doing the same query. The entire

> > application is killed by an execution plan that Oracle devised ad hoc without

> > having been approved.

> >

> > This is the scenario I would like to guarantee won't occur. The development

> > people can do their job with testing but they can't guarantee that bugs will

> > never occur. But from an operations point of view we should be able to

> > guarantee that a bug causes an immediate error rather than having Oracle just

> > try to do its best to come up with an ad hoc execution plan and possibly kill

> > the entire application.

> >

> > --

> > greg

 
-- 
---------------------------------------------------------------------
Chad Thompson, 
Programmer Analyst: VB, VC++, PLSQL, Oracle HRMS, Security Apps
                            Home: thomp901_at_micron.net

Thou shalt not tick off the dragon.... for thou art crunchy and 
taste good with ketchup.
---------------------------------------------------------------------
--