Implementing CANCEL_SQL with the resource manager

introduction

this article is more a recipe on how to get CANCEL_SQL working for a dedicated use case than a discussion of oracle resource manager concepts and technology. but, however, as long as CANCEL_SQL can not be implemented without having at least a basic understanding of resource manager, be adviced to study the relevant oracle documentation (from 10g onwards) before running or even reintegrating the code below. It is important to learn that activating a resource manager plan is a system wide effect because there can only be one resource manager plan active at one defined point in time. additionally, others may also be granted to activate or switch resource manager plans. sounds like the big wheel and side effects en masse? no, not really, you just need to know where to plug in your resource requirements and how to adapt your application code accordingly.

Your eye into the buffer cache

ever asked yourself what is going on in the buffer cache? e.g., does oracle honour my settings for the different buffer cache types, that is, the default, keep and recycle cache? or, does oracle tune the buffer cache nicely, such that the application important database objects are in fact cached?

the latter is, however, not only oracle’s duty. imagine an application where there is an index missing on a large table. on every unindexed select, oracle will have to full scan the table, flooding the buffer cache with unwanted blocks. but how to trap this scenario?

the solution key is v$bh or, internally, x$bh, a system view that allows a blockwise inspection of the buffer cache. some pretend that v$bh has been introduced with oracle parallel server (ops), others relate it to the real application cluster (rac) environment.

The performance trap of acclaimed applications

stumbled upon the following extract lately. it originates from the german only book Andrea Held, Oracle 10g Hochverfügbarkeit (High Availability). have a try at it, this is real truth.

there exists an ever reoccuring problem that an application has only been sized for a medium amount of data, but, due to an ongoing acceptance on the users side, grows in terms of functionality, load and complexity. it is only a matter of time until defined maintainance windows become to small to load and process all the data. likewise, selects and reports cumulatively offer worse performance. if there is no early time effort to customize the whole application architecture according to the increasing requirements, sooner or later a massive performance crash will have to be experienced.

regards