Re: Strictly Enforcing Query Plan Stability

Another thought:
Using a Table API (packages that control insert, update, delete and selects) and a Custom API (layer on top of that - can be used to enforce business rules) you could probably archieve this. Rogue (or ad hoc) queries will either be: - stopped (CAPI/TAPI don't support it)
- handled in a standard way (by CAPI/TAPI)

Check out CDM ruleFrame - similar design. http://technet.oracle.com/products/headstart/

--
Frank
Greg Stark <greg-spare-1_at_mit.edu> schreef in berichtnieuws
87vguc12s7.fsf_at_HSE-MTL-ppp62507.qc.sympatico.ca...


> Mark D Powell <markp7832_at_my-deja.com> writes:

>

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

> > user, not the DBA.

>

> Obviously you wouldn't apply such a feature to a database where ad hoc

 queries


> were part of its intended use. But such a database would be a data

 warehouse


> type database or a development environment. In a mission critical OLTP

> database ad hoc queries are anathema.

>

> In such a database it's inappropriate to have some cowboy (usually me, I

> admit:) to be running ad hoc queries in the same database as the

 application


> performing 100s of updates per second. The batch queries should be run on

 a


> separate database where a sudden increase in i/o won't cripple the

 production


> application.

>

> Jonathan's solution is fairly interesting though. It's possible to do this

 at


> the application layer even without a proxy. All queries are executed

 through a


> driver interface, if that driver interface checks every query against a

> database of approved queries then it could alert admins to new queries.

 That


> database could even be stored in the Oracle database, but probably it

 would be


> more reasonable to store them in an in-memory hash. Hmm.

>

> --

> greg