#Sql scratchpad driver#
#Sql scratchpad full#

There is also a parameter you can set during statistics gathering that is supposed to invalidate any SQL plan using the table (no_invalidate => FALSE), but it comes with caveats and is generally ignored for at least a while. You would also have to do so on every instance of a RAC. DBMS_SHARED_POOLĪlthough there is a package named DBMS_SHARED_POOL that provides fine grained control over invalidating individual sql plans, I have yet to meet a DBA willing to grant execute on the package and I do not blame them one bit. We could do something to invalidate the plan forcing Oracle to evaluate the SQL again with the changed statistics.

#Sql scratchpad driver#

Oracle has a good plan now for the small driver table footprint but when we load it up for the nightly batch, Oracle continues to use an unscalable execution plan with index access that runs all night. Booooooooo!Įven worse would be the behavior that night. By now though we are fielding calls from the business that stuff isn’t working right. By some later run (perhaps hours later) the adaptive plan technology may figure out something isn’t right and substitute a better plan.

#Sql scratchpad full#

It does full table scans and hash joins to obtain what we need for our tiny subset of accounts, wasting resources and taking forever on the first near real-time run. Nope, the idiot savant has a perfectly good plan already, and it is going to use it. We re-gather statistics on our driver table, but Oracle does not bother to re-evaluate our execution plan. Works great for the nightly batch which runs for thirty minutes (or hours). Imagine that we start with the nightly batch case where the driver table statistics would tip off the optimizer to use FTS and Hash join plans. The adaptive plan mechanism does not help sufficiently with this use case. Adaptive plans may be a great idea and it may solve many issues in many environments,but I have not personally experienced that. As the table fills during the day, the adaptive plan logic may determine an Index Access Path is lower cost and switch to it. For example at the start of the day one of the source tables may be tiny and a Full Table Scan (FTS) access path is chosen. That is a good idea, and I am sure it is helpful in situations where the data changes slowly over time. Modern Oracle databases have dynamic execution plans where the database attempts to re-plan after finding that the statistics used to calculate the original plan do not match reality. (In practice I have third and forth use cases of nightly “correction” batches that touch what can be either middling sized sets of the data or really huge, almost full sized subsets.) An incremental, near real-time batch that touches a very small subset of the data Multiple Use Case SQL Statements.A nightly batch that touches a full set of data.I will present two use cases for this article. The problem to be solved is that we have a single SQL statement with multiple use cases, each of which require a different execution plan.

The subject is so massive and fluid, I do not even pretend to get it all.

I bashed my head against the rocks of Jonathan Lewis’s book Cost Based Oracle Fundamentals for several years. There are reams of books and articles about it. The Oracle optimizer is good at achieving an optimal execution plan ( access path, join order, join mechanism), defined as minimal cost, if it has sufficient statistics about the data. The best answer usually achieves both, but occasionally you sacrifice some of one for the other. The primary goal of tuning an Oracle SQL statement is to obtain the best performance (shortest response time and minimal resourse utilization) for the intended task.