Re: Strictly Enforcing Query Plan Stability

Uhm, both of you have completely failed to understand the issue. The issue is that anyone with create session ability can run arbitrary SQL on any table they have access to. Moreover any developer can introduce queries into the application and they can slip by the QA process without being analyzed.

All I want is for Oracle to not allow arbitrary SQL from the user the application connects as. It should *only* allow SQL to be executed if it has a stored outline. If there's no outline the optimizer should immediately throw an error.

This seems like a minimum requirement for a stable production system. Anything less means that the operations people can never guarantee that an error in judgement by developers releasing new code won't take downt the entire production system by trying to run a full table scan a thousand times a second on a million record table.

Without this type of feature from Oracle everything depends on the developers diligently running explain plan and testing every query in the application. If they miss one it should be exactly like if they accidentally update a table they're not supposed to update (permission denied) or delete a file they're not supposed to delete etc. Oracle should not just make up new query plans on the fly on the production database. Frankly that's insane.

"Dave A" <dave_and_vanna_at_hotmail.com> writes:

> It is. Control access to the database via passwords. Application passwords
> should not be given out to anyone, just the application.
>
> --
> Dave A
>
>
> "Greg Stark" <greg-spare-1_at_mit.edu> wrote in message
> news:873di6lrrt.fsf_at_HSE-MTL-ppp62507.qc.sympatico.ca...
> >
> > 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
>
>
 

-- 
greg