Re: Strictly Enforcing Query Plan Stability

The thread is on Greg's desire for the optimizer to reject all queries that do not have plan stability defined.

I would like to point out that request is not reasonable or well considered because of something called adhoc! If the end-user has access to end-user reporting tools there is no way to antiscipate all possible queries nor should you have to. The CBO was invented to handle adhoc queries. For production code you should lock the path in using hints or the new query plan statbility feature, but even here we have found the CBO is usually good enough and we only tune what we have to.

In article <39E1A92F.1A5D_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> Greg Stark wrote:
> >
> > I have a wishlist feature I would love Oracle to support. In fact
 I'm
> > surprised Oracle doesn't support it already. Perhaps there's a
 trick to
> > accomplish what I want and I just don't know it?
> >
> > What I would like is to be able to set privileges such that my
 application
> > cannot run _any_ query that doesn't have an outline already stored
 for it.
> > Essentially what I want is to be able to guarantee that no code
 could possibly
> > go live without _every_ SQL query being analyzed and the plan
 approved by a
> > DBA. Any unapproved query should immediately get an error, not be
 run with
> > some ad hoc query plan that could very well bring the whole
 application down.
> >
> > It seems to me that anything less is simply inadequate for a
 production
> > mission critical system. Given the types of applications that run
 on Oracle
> > I'm surprised this isn't a fundamental feature of the system since
 day 1.
> >
> > --
> > greg
>
> Easy enough to do...
>
> In its simplest form, users have 'create session' and a role which
 lets
> them do whats needed in the applications. The role is only enabled
 when
> they launch the app (as protected by a role password).
>
> Then outside the app, the only thing that they can do is connect. All
> you need do then to ensure that nothing is 'grant select to public'
 and
> you're protected..
>
> HTH
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk
> (faster/mirrored at http://www.oradba.freeserve.co.uk)
>

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.