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

November 26, 2013

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

Finally! Something on this blog that can really save you money.

April 13, 2012

No, I am not kidding!

As you may have heard, News America Marketing just launched a free couponing app for the iPad called SmartSource Xpress. We’re very excited about our revolutionary new product and wanted to share the news with you. SmartSource Xpress not only offers the great consumer savings SmartSource is known for, it’s also green (no paper!) and digital (very 21st century!).

 SmartSource Xpress is a user-friendly, elegant and fun way to clip coupons that saves you time both at home and at check out.

 Imagine a digital coupon insert with enhancements including video, recipes and free samples! Then imagine getting coupon savings without having to clip, file or carry your coupons to the store. With SmartSource Xpress, coupons are “clipped” with a tap of the screen, then uploaded to your shopper loyalty cards to be redeemed automatically at check out. Never worry about leaving your coupons at home again!

 So many great brands are participating, we couldn’t possibly list them all here, but we’ve got over 30 offers in our first book – and there will be a new book every Sunday, 50 weeks of the year.

 So far, nearly 4000 stores are participating in the program, and that number is growing all the time. Check here for participating retailers.

 SmartSource Xpress makes it simple to save money (and it’s pretty to look at, too)!

 You can download SmartSource Xpress for free from the App Store.

 I would love for you to be one of the first people to use our new app, and if you like it, hope that you’ll give it a positive review in the App Store for others to see.

 

 


Is controlling parallelism with an Oracle resource plan ever a good option?

September 30, 2011

Ever wanted to be able to run some code in parallel, while keeping the rest execute sequentially? Oracle Resource Groups allows us to dispatch sessions into groups and impose restrictions about the resource usage of those groups. Parallelism is one of the resources that can be managed by Resource Groups, so things look promising.

 It works too. I was fortunate enough to avoid hitting any bugs.

 So here the problem: All plans are generated as if all parallel processes are available for the query. A query that is to be executed serially can get a parallel execution plan, which often evolves full table scans. When it comes to actually running the query, Oracle will abide by restriction of the Resource Plan and run it sequentially. This is major structural problem causing suboptimal plans. It is like planning your move based on the assumption that would get a large U-Haul truck, and coming move day all you can get is a compact car… Not pretty.