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

In my previous post, I tried to find out the performance impact of adaptive execution plans (AEP). During the test, I used OPTIMIZER_ADAPTIVE_REPORTING_ONLY parameter to switch on and off adaptive execution plans(AEP). It should be noted that the parameter does not control the generation of AEP, but rather the executtion of the “adaptive” part. I came to a rather surprising result that turning off adaptive execution plans actually made the query run slower. This behavior seems to be a side -effect of frequent shared pool flushing. The original test, i.e. the one that showed tuning on adaptive execution plans speeds up a query, is reproducible, however! I ran it at least three times and I got similar results.

To confirm the peculiar results, I ran a new test that avoids SQL reuse by generating unique SQL statements. Here is the new table/view setup script. Here is the new test procedure. After running it, we can see the expected outcome – adaptive execution plans do cost something.

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

avg(r) = 2.05312594     avg(a) = 2.07863544     STATS_T_TEST_PAIRED (R,A) /p/ = 0

The difference is quite small – approximately 2% of the overall time or a fraction of a millisecond.
The following query allows us drill down and see 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 statistically different are:
CCursor + sql area evicted            – the SQL with AEP on consumed less that the SQL with AEP off
CPU used by this session               – the SQL with AEP on consumed more (marginally) that the SQL with AEP off
parse time elapsed                          – the SQL with AEP on consumed more that the SQL with AEP off
sql area evicted                                – the SQL with AEP on consumed less that the SQL with AEP off

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: