Re: Strictly Enforcing Query Plan Stability

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