Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strictly Enforcing Query Plan Stability

Re: Strictly Enforcing Query Plan Stability

From: frank <fbortel_at_home.nl>
Date: Wed, 11 Oct 2000 21:10:21 GMT
Message-ID: <39E4D794.85508C52@home.nl>

1st of all - it is exactly what the optimizer can do - rewrite queries. 2nd - you cannot be serious in developing applications like that:   data changes over time, making assumptions, explain plans and whatever you have tested useless.

Yuo are a sales company: how would you optimize the customer vs products queries (e.g. orders, order lines)?

The simple answer is: you cannot, unless you *know* your data. If I'm on the internet, I might have millions of customers, with only 30 products (example? Think Pokemon!).
If I were "traditional", I could have thousands of products, and only a few customers
(example? Think producing raw material; input for other processes).

In both examples, I would like the othe table to be the driving table... So how to

resolve that? You cannot. The optimizer can (well, should...) because it knows data.

BTW, The above is exactly why most apps, inclusing Oracle apps, perform badly.

my 2.5C

Greg Stark wrote:

> 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
  Received on Wed Oct 11 2000 - 16:10:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US