Re: Strictly Enforcing Query Plan Stability

Yes, I had lost track of the fact the poster had mentioned adhoc while reading the responses. Still I think the request is unreasonable. By its very nature adhoc is unknown. It is an unnecessary hinderance to the users to make them predefine and clear their data retrieval needs before they know them. The database exists for the needs of the enduser,  not the DBA.

The IO pigs can be monitored and query plans added where missing or necessary.

Still you posted an interesting solution.

In article <971721831.333.2.nnrp-13.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> But Greg points out that ad-hoc is particularly the thing he wants
> to stop - any ad-hoc query which therefore does not have a pre-
> computed plan should fail at parse time.
>
> If this were my aim, I think I would go for a 3-tier architecture,
> using the 'trusted proxy user', so that end users never knew
> their own Oracle passwords, and certainly never learnt the
> password of the proxy (8.1 / OCI only feature though).
>
> You could try creating a view on the outln$ table that
> forced a once-per-execute call to a function that failed
> if no rows were returned. Then the recursive SQL to
> parse the query would crash if no plan existed.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Mark D Powell wrote in message <8sevp8$bn9$1_at_nnrp1.deja.com>...
> >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 --
> >
> >

--
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.