The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 3

My previous two posts, The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 1 and The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 2,  focused on estimating the impact of OPTIMIZER_ADAPTIVE_REPORTING_ONLY parameter in Oracle 12c. The parameter controls if the adaptive plan would be acted upon. The adaptive plan, however, is always generated.

The post will try to estimate the impact of adaptive execution plans, while taking into account the resources needed for generating the adaptive execution plans. Since I was not able to find a way to suppress the generation of adaptive plans in Oracle 12c, I decided to just use an older version of the optimizer. I did that by utilizing “optimizer_features_enable” parameter.

Here is the table/view setup script, and here is the test script.

We can see the difference in elapsed time using this query:

select avg(r), avg(a) , STATS_T_TEST_PAIRED (R,A) FROM track_v

avg(r) = 1.5880118 avg(a) = 2.5002121 STATS_T_TEST_PAIRED (R,A) /p/ = 0

The adaptive (12c)  plan takes almost 1 millisecond more than the non-adaptive, “old-style” 11gR2 plan.

This query can show where the difference comes from:

select c.name , avg (a.val) a , avg (r.val) r , STATS_T_TEST_PAIRED (r.val,A.val)
from stats_a a , stats_r r , v$statname c
where a.id = r.id
and a.stat = r.stat
and a.stat = c.statistic#
group by c.name

The only statistics that are difference are:
CCursor + sql area evicted – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
CPU used by this session – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
parse time cpu – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
parse time elapsed – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
recursive cpu usage – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
sql area evicted – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2

In conclusion, “adaptive execution plans” is a wonderful feature. The resource it needs are typically negligible, except for very light (fast) OLTP type queries.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: