Welcome, Guest
Username: Password: Remember me

TOPIC: Can A Query Plan Change Based On Throughput?

Can A Query Plan Change Based On Throughput? 3 years 3 weeks ago #281

Can a query plan change based on the amount of throughput in the system? Meaning, after the particulars of items related specifically to the query (statistics, selectivity of predicates, indexing, etc.) are considered, does the chaos of a production system influence plan generation? Let’s say Query “A” is a dog and is consuming a ton of CPU and also taxing the I/O subsystem by pulling a ton of data and Query “B” would normally construct Plan “A” but when Query “A” is running and consuming resources then the Optimizer could instead create Plan “B” for Query “B”. Hope that made sense.

In essence, can the varying throughput of the system influence plan generation and if so, how? Does the Optimizer take into account fluctuating O/S level statistics related to CPU, Memory or DASD (or any others) when considering an optimal plan?
The administrator has disabled public write access.

Re:Can A Query Plan Change Based On Throughput? 3 years 3 weeks ago #282

I think I just found the answer and it’s “Yes” through “Dynamic Runtime Optimization:

www.oracle.com/technetwork/database/focu...tion-10gr-130948.pdf

Here is the snippet about dynamic runtime optimization:

DYNAMIC RUNTIME OPTIMIZATIONS

The workload on every database fluctuates, sometimes greatly, from hour to
hour, from daytime workloads to evening workloads, from weekday workloads to
weekend workloads, and from normal workloads to end-of-quarter and end-ofyear
workloads. No set of static optimizer statistics and fixed optimizer models
can cover all of the dynamic aspects of these ever-changing systems. Dynamic
adjustments to the execution strategies are mandatory for achieving good
performance.

For this reason, Oracle’s query optimization extends beyond just access path
selection. Oracle has a very robust set of capabilities which allow for adjustments
to the execution strategies for each query based not only on the SQL statement
and the database objects, but also the current state of the entire system at the
point in time when the query is executing.

The key consideration for dynamic optimization is the appropriate management
of the hardware resources, such as CPU and memory. The hallmark of dynamic
optimization is the dynamic adjustments of execution strategies for each query so
that the hardware resources are utilized to maximize the throughput of all
queries. While most other aspects of query optimization focus on optimizing only
a single SQL statement, dynamic optimization focuses on optimizing each SQL
statement in the context of all of the other SQL statements that are currently
executing.
The administrator has disabled public write access.

Re:Can A Query Plan Change Based On Throughput? 2 years 11 months ago #286

No, I don't think till now this is possible but further might be some development may be happening on this by the developers.
The administrator has disabled public write access.