Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Oakies Blog Aggregator

AMIS Masterclass – Two Day Masterclass with Oracle ACE Director James Morle

AMIS is spending a lot of effort keeping our people up-to-date with the latest knowledge needed to help our customers the best way we can. Traditionally we also always try to share our knowledge with customers and others, via social media or conferences, and while abroad learning from others at the same time. It is …

Continue reading »

VirtualBox 4.2.10…

Were you thinking, “I’ve got nothing better to do this weekend than to download the latest version of VirtualBox and update the guest additions on all my VMs”? Well your luck is in!

Oracle have just released VirtualBox 4.2.10. The downloads and changelog are in the usual places.

Happy upgrading!

Cheers

Tim…


VirtualBox 4.2.10… was first posted on March 15, 2013 at 8:50 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Premature Optimization

Premature optimization, (probably) because of Donald Knuth’s famous line “premature optimization is the root of all evil,” (see Structured Programming with go to Statements) is, at the very least, a controversial topic. The misconception based on that particular quote is that a programmer, while writing code, should ignore optimization altogether. In my opinion this is wrong. To put the quote in context, let’s have a look to at the text that precedes and follows it:

There is no doubt that the grail of efficiency leads to abuse. Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%. A good programmer will not be lulled into complacency by such reasoning, he will be wise to look carefully at the critical code; but only after that code has been identified. It is often a mistake to make a priori judgments about what parts of a program are really critical, since the universal experience of programmers who have been using measurement tools has been that their intuitive guesses fail.

My take on Knuth’s paper is that programmers, when writing code, should not care about micro optimization that has local impact only. Instead, they should care about optimizations that have global impact, like the design of a system, the algorithms used to implement the required functionality, or in which layer (SQL, PL/SQL, application language) and with which features a specific processing should be performed. Local optimizations are deferred till a measurement tool points out that a specific part of the code is spending too much time executing. And, because the optimization is local, there is no impact on the overall design of the system.

The problem with free and RSS…

The internet has been awash with people bemoaning the decision by Google to close Google Reader. Probably the next biggest talking point has been people asking what they can use to replace it when it’s gone. I’m planning on giving TheOldReader.com a test-drive, once I can get my feeds imported. :)

The problem with free

This highlights one of the problems with free stuff. It’s not (always) really free. Google spent a few years building stuff, some of which was pretty cool, and most of which was free, but sooner or later they needed to find a way to monetize this stuff. That’s one thing that never happened with Reader, so not surprisingly it joined the growing list of things that have been cleaned away to make room for the more profitable stuff. It’s a pity Google didn’t first ask people if they were willing to pay a fee to keep the service. Like many others, I would have been willing to pay for the privilege of retaining it.

The problem with RSS

It also suggests that RSS was never really that popular. It’s easy when you are a tech-blogger to think the whole world reads blogs and cares about RSS, but the truth is most people just don’t give a crap. I use RSS to keep on top of things going on in the industry. If I relied on Twitter, Google+ or Facebook I would miss out on loads of things because the UK timezone doesn’t really fit with the vast majority of publishing in my chosen profession. Also, the signal:noise ratio of my RSS feeds is much better than that of most social media channels I subscribe to. So being one of the vast minority of people that actually do care about RSS has left me in a bad situation.

I’m interested to hear any thoughts on ways of ridding myself of RSS, or any other tools you’ve seen that might help me out of my current predicament. :)

Cheers

Tim…

Update: I’ve decided on feedly.com for the moment.

 

 

 

 


The problem with free and RSS… was first posted on March 14, 2013 at 9:47 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

UltraEdit v19 for Windows

For all those folks who are forced to use Windows, you might be interested to know UltraEdit v19 has just been released.

You can check out the new features here.

The latest version for Mac/Linux is still 3.3, so maybe we are due a new version there too?

Cheers

Tim…


UltraEdit v19 for Windows was first posted on March 14, 2013 at 4:00 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Enkitec’s Exadata Smart Flash Cache Hacking Session by Tanel Poder! (free stuff!!!)

We recently received our 3rd Exadata machine into Enkitec’s exalab. Now we have a V2, X2 and X3 there, in addition to ODA, Big Data Appliance (which comes with a beer-holder built in!) and an Exalytics box! So you understand why Karl Arao is so excited about it :-)

This occasion demands that we hack the hell out of all this kit soon! So, let’s have another (super-secret) hacking session!

This time, let’s see how the Exadata Smart Flash Cache works! (both for reads and writes). Note that we won’t cover Smart Flash Logging in this session (otherwise we’ll end up spending half a day on it :)

This is another 1-hour hacking session, so save at least 2 hours in your calendar!

See you soon!

P.S. I uploaded the Part 1 of my previous hacking session about Oracle SQL plan execution to enkitec.tv (The part 2 will follow hopefully today)


Parallel unfriendly

Take a look at the following Parallel section of a SQL Monitor report:

Any query which produces such a report won't care about how much parallel you're running because virtually all the time is spent by the query coordinator (which is a serial process) being busy.

In this case the query in question is quite simple:



select /*+ parallel(t,8) */ median(basket_amount) from whs.fact_sale t

The reason it behaves the way it does has everything to do with how Oracle executes it:

Execution Plan
----------------------------------------------------------
Plan hash value: 712547042

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 110K (3)| 00:00:03 | | | | | |
| 1 | SORT GROUP BY | | 1 | 4 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 724M| 2763M| 110K (3)| 00:00:03 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 724M| 2763M| 110K (3)| 00:00:03 | 1 |1048575| Q1,00 | PCWC | |
| 5 | TABLE ACCESS STORAGE FULL| FACT_SALE | 724M| 2763M| 110K (3)| 00:00:03 | 1 |1048575| Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------

Each parallel query slave will gets it's own chunk of the table to read from and then simply send data back to the coordinator. The coordinator will then have to deal with all this data by sorting more than 700M rows which, of course, won't be particularly fast. In this sense median poses an interesting problem since Oracle can't calculate (or, rather, discover) it without having access to the entire data set and query coordinator is the only process which can do it.

So what do you do when you get impacted by a particular choice of algorithm implemented by Oracle? One way to deal with it is to see whether you can trade one set of problem for another, in case the alternative can be executed in a better way. In this particular case the fact table contains the sale transactions for a particular store chain. While there are many different ways to spent money, the number of distinct spending amounts should be relatively low compared to the number of rows we have in the table and in such a case we can calculate the median in a different way.

What we can do instead is count how many occurrences of each spending we have and, when sorted by the spending amount, that will give us a compressed form of the raw data which still retains all the information required to find a median. Let's say you have a table with the following data:



SQL> select n from z_t;

N
----------
1
1
2
3
3
5
7

7 rows selected

The first step is to find how many occurrences of each value do we have:

SQL> select n, count(*) cnt
2 from z_t
3 group by n;

N CNT
---------- ----------
1 2
2 1
5 1
3 2
7 1

If the number of distinct values is relatively low, the group by will be able to collapse the result set enough as to make subsequent work to be not very significant as well as do it in a very parallel friendly way. The next step is to calculate the cardinality of the data set, at which places we have each distinct value as well as how many values are there:

SQL> select n, lag(running_sum, 1, 0) over (order by n) prev_running_sum, running_sum, total_row_count
2 from (
3 select n,
4 sum(cnt) over (order by n) running_sum,
5 sum(cnt) over () total_row_count
6 from (
7 select n, count(*) cnt
8 from z_t
9 group by n
10 ));

N PREV_RUNNING_SUM RUNNING_SUM TOTAL_ROW_COUNT
---------- ---------------- ----------- ---------------
1 0 2 7
2 2 3 7
3 3 5 7
5 5 6 7
7 6 7 7

So what the above tells us is we have two 1s, followed by a single 2, followed by two 3s and so on. Because we have seven elements in our data set, we know that the median will be the item number four which we can now easily discover:

SQL> select avg(n) from (
2 select n, lag(value_begin, 1, 0) over (order by n) prev_value_begin, value_begin, total_row_count
3 from (
4 select n,
5 sum(cnt) over (order by n) value_begin,
6 sum(cnt) over () total_row_count
7 from (
8 select n, count(*) cnt
9 from z_t
10 group by n
11 ))) where total_row_count/2 between prev_value_begin and value_begin;

AVG(N)
----------
3

The avg is there for a case where we have an even number of elements since the median in this case equals to a mean value of two values in the middle.

Our new real query will look like this:

select avg(n) from (
select n, lag(value_begin, 1, 0) over (order by n) prev_value_begin, value_begin, total_row_count
from (
select n,
sum(cnt) over (order by n) value_begin,
sum(cnt) over () total_row_count
from (
select /*+ parallel(t,8) */ basket_amount n, count(*) cnt
from whs.fact_sale t
group by basket_amount
))) where total_row_count/2 between prev_value_begin and value_begin;

So what does a group by and a set of analytic functions is able to bring to a table? Let's take a look:

The total execution time has dropped from almost 26 minutes down to 28 seconds. Moreover, the workload is now much more skewed towards parallel query slaves, which is exactly what we want to see. Of course, the trick only works if the group by is able to collapse the data sufficiently enough.

Complete SAP projects in half the time : webinar

March 20, 2013, Wednesday, 10:00 am-11:30 am PST

Speaker:   Rick Caccia, Vice President Strategy at Delphix

To Register:  Click Here

Delphix accelerates SAP project delivery by eliminating redundant infrastructure and slow processes.

Delivering application projects quickly can immediately impacts revenues and earnings.  Most IT organizations can only fund a small number of projects in a given year which limits the ability of businesses to capture these market opportunities and operational efficiencies.

The enormous effort it takes to provision database environments for SAP projects delays project completion. By virtualizing databases with Delphix dramatically reduces the time and budget required to complete an SAP project.

Attend the webcast & learn how to:

  • Accelerate time to market for SAP enterprise application projects by 20-50%
  • Improve application testing while also shortening test cycles
  • Complete 3x – 5x more projects, with existing headcount
  • Cut data growth and storage requirements by 90%
  • Recover from application outages quickly, limiting impact to operations

 

KLA-Tencor – an SAP & Delphix Customer Best Practice:

Learn how KLA-Tencor accelerated project results by virtualizing databases for SAP architectures and achieving the following benefits:

  • Enabled faster self‐service data provisioning & refresh capabilities
  • Reduced storage requirements by 94%
  • Created an agile, virtual infrastructure allowing faster innovation at lower cost

Delphix is a proven solution among Fortune 500 companies and allows SAP Best-Run Businesses to complete SAP projects faster, innovate with higher quality and while also reducing infrastructure investments.

 

To Register:  Click Here

For Help:  Contact Andy Nguyen at andy.nguyen@sap.com ; (408) 839-0794

 

"Cost-free" joins - 2

In the previous post I've demonstrated an unexpected Nested Loop Join caused by an extreme data distribution. Although unexpected at first sight, the performance of the execution plan selected by the optimizer is decent - provided the estimates are in the right ballpark.Here is another case of an unexpected execution plan, this time about Merge Joins.

Merge Joins

In order to appreciate why the execution plan encountered is unexpected, first a quick summary about how Merge Joins work:A Merge Join is essentially a Nested Loop operation from one sorted row source into another sorted row source. In contrast to a Nested Loop the join condition is not used for a possible index-driven lookup from the driving, outer row source into the inner row source, simply because Oracle usually first needs to run separate operations on each rowsource for sorting.This means that in most cases the Merge Join requires to sort both row sources and therefore a Hash Join is usually preferred where possible (for example, Hash Joins are only suitable for Equi-Joins, whereas a Merge Join also supports non-Equi Joins), because it only needs to "prepare" one row source for building the hash table, and can then process the second row source as it is without any further start-up cost / preparation steps.Let's have a look at some common execution plans using Merge Joins. Consider this simple setup:


create table t1
as
select
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e3
;

exec dbms_stats.gather_table_stats(null, 't1')

create unique index t1_idx on t1 (id);

create table t2
as
select
rownum as id
, 1 as fk
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e6
;

exec dbms_stats.gather_table_stats(null, 't2')

So this is what a Merge Join usually looks like:


select /*+ use_merge(t1 t2) */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id (+) = t2.fk
;

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 199M| | 28075 (1)| 00:05:37 |
| 1 | MERGE JOIN OUTER | | 1000K| 199M| | 28075 (1)| 00:05:37 |
| 2 | SORT JOIN | | 1000K| 99M| 217M| 28067 (1)| 00:05:37 |
| 3 | TABLE ACCESS FULL| T2 | 1000K| 99M| | 4333 (1)| 00:00:52 |
|* 4 | SORT JOIN | | 1000 | 102K| | 7 (15)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 1000 | 102K| | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."ID"(+)="T2"."FK")
filter("T1"."ID"(+)="T2"."FK")

As usual I had to force the Merge Join via a hint, since in my (default 11.2.0.1) setup a Hash Join would be preferred. Notice the two SORT JOIN operations that first create two (ideally in-memory) sorted/indexed tables from the two row sources to be joined and how the SORT JOIN on the larger row source basically determines the overall cost of this MERGE JOIN.A corresponding Hash Join could use the smaller row source as hash table and therefore very likely would be much more efficient.Since the MERGE JOIN usually needs to SORT both row sources it doesn't make such a big difference which of the two row sources comes first, but it is interesting to note that the MERGE JOIN is not able to "swap" the join inputs as the HASH JOIN is able to, which, in particular for outer joins, makes the MERGE JOIN less flexible.Here is a variation of a MERGE JOIN that avoids a SORT JOIN operation. This is only supported for the "driving" row source:


select /*+ use_merge(t1 t2) */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id = t2.fk
and t1.id between 1 and 10
;

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909K| 181M| | 28081 (1)| 00:05:37 |
| 1 | MERGE JOIN | | 909K| 181M| | 28081 (1)| 00:05:37 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1050 | | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 10 | | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1000K| 99M| 217M| 28078 (1)| 00:05:37 |
|* 5 | TABLE ACCESS FULL | T2 | 1000K| 99M| | 4344 (2)| 00:00:53 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."ID">=1 AND "T1"."ID"<=10)
4 - access("T1"."ID"="T2"."FK")
filter("T1"."ID"="T2"."FK")
5 - filter("T2"."FK">=1 AND "T2"."FK"<=10)

The MERGE JOIN knows that the driving row source will be accessed in sorted order due to the suitable INDEX RANGE SCAN operation and therefore doesn't add a SORT operation on top.If we now run the same statement using Parallel Execution (note that the statement level PARALLEL hint used in the example is only supported from 11g on), we'll see the following:


select /*+ use_merge(t1 t2) parallel */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id = t2.fk
and t1.id between 1 and 10
;

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909K| 181M| | 15594 (1)| 00:03:08 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 909K| 181M| | 15594 (1)| 00:03:08 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE JOIN | | 909K| 181M| | 15594 (1)| 00:03:08 | Q1,01 | PCWP | |
| 4 | SORT JOIN | | 10 | 1050 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | BUFFER SORT | | | | | | | Q1,01 | PCWC | |
| 6 | PX RECEIVE | | 10 | 1050 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 10 | 1050 | | 3 (0)| 00:00:01 | | S->P | BROADCAST |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1050 | | 3 (0)| 00:00:01 | | | |
|* 9 | INDEX RANGE SCAN | T1_IDX | 10 | | | 2 (0)| 00:00:01 | | | |
|* 10 | SORT JOIN | | 1000K| 99M| 217M| 15591 (1)| 00:03:08 | Q1,01 | PCWP | |
| 11 | PX BLOCK ITERATOR | | 1000K| 99M| | 2407 (1)| 00:00:29 | Q1,01 | PCWC | |
|* 12 | TABLE ACCESS FULL | T2 | 1000K| 99M| | 2407 (1)| 00:00:29 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

9 - access("T1"."ID">=1 AND "T1"."ID"<=10)
10 - access("T1"."ID"="T2"."FK")
filter("T1"."ID"="T2"."FK")
12 - filter("T2"."FK">=1 AND "T2"."FK"<=10)

So usually, due to the way things run in parallel, Oracle assumes it cannot guarantee the order of the row source and includes a SORT operation for both row sources joined.Although there are special cases where this could be avoided even for Parallel Execution, it looks like the code adds this SORT operation unconditionally in case of Parallel Execution. We'll see how this can become a threat in a moment.

The Special Case

Now back to the special case I want to demonstrate here. Let's have a look at the following query:


select
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id (+) = t2.fk
and t2.fk = 1

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 199M| 4342 (1)| 00:00:53 |
| 1 | MERGE JOIN OUTER | | 1000K| 199M| 4342 (1)| 00:00:53 |
|* 2 | TABLE ACCESS FULL | T2 | 1000K| 99M| 4339 (1)| 00:00:53 |
|* 3 | SORT JOIN | | 1 | 105 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 105 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T2"."FK"=1)
3 - access("T1"."ID"(+)="T2"."FK")
filter("T1"."ID"(+)="T2"."FK")
5 - access("T1"."ID"(+)=1)

Notice that I now got a MERGE JOIN although I haven't provided any hints to do so, so this execution plan was automatically favored by optimizer. Why?This is a special case, because the optimizer understands that the join key is actually a single value, due to the predicate on T2.FK. So for a serial execution it doesn't bother to SORT the large row source (since it knows there will only be the value "1") and hence the MERGE JOIN comes out with a (slightly) lower cost estimate than a corresponding HASH JOIN.It's interesting to note that in this particular case here it could even be avoided to SORT the second row source, since it, too, can only return a single value. But obviously the MERGE JOIN always runs a SORT JOIN operation on the second row source, as already outlined above.Due to the way the data is designed and the direction of the outer join a NESTED LOOP join isn't a reasonable alternative either here.Note that at runtime a HASH JOIN seems to be slightly more efficient in this particular case here, so this is already an indication that the cost estimates do not reflect the efficiency at runtime very well, in particular the CPU overhead of the actual join operation seems to be underestimated for the MERGE JOIN.Now let's see what happens if we run this query using Parallel Execution:


select /*+ parallel */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id (+) = t2.fk
and t2.fk = 1

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 199M| 2406 (1)| 00:00:29 | | | |
| 1 | MERGE JOIN OUTER | | 1000K| 199M| 2406 (1)| 00:00:29 | | | |
| 2 | SORT JOIN | | 1000K| 99M| 2403 (1)| 00:00:29 | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T2 | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,00 | PCWP | |
|* 7 | SORT JOIN | | 1 | 105 | 3 (34)| 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 105 | 2 (0)| 00:00:01 | | | |
|* 9 | INDEX UNIQUE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter("T2"."FK"=1)
7 - access("T1"."ID"(+)="T2"."FK")
filter("T1"."ID"(+)="T2"."FK")
9 - access("T1"."ID"(+)=1)

Look very carefully at the order of the operations, and what part of the execution plan runs in parallel and what is executed serially.This is where things become pretty weird and threatening: The TABLE ACCESS to the large row source T2 runs in parallel (with the corresponding lower cost), but the data is then handed over to the Query Coordinator for a SORT JOIN operation - which wasn't there in serial execution and is in fact unnecessary since we still have a single value in the join key.After sorting the large row source, the MERGE JOIN operation itself is performed by the Query Coordinator, so no Parallel Execution is involved here either.Both the serial SORT JOIN of the large row source and the MERGE JOIN operation itself are literally free of cost here, which is clearly unreasonable, in particular if the row source is very large.Although the SORT JOIN will basically turn into a simple "BUFFER SORT" operation, since there is effectively nothing to sort, it still means that a potentially very big volume of data will have to be handed over from the Parallel Worker processes scanning the row source to the Query Coordinator - in this particular case by definition an inefficient operation, because a large data volume has to be passed from multiple Parallel Processes to the single Query Coordinator - and then this potentially very big volume of data will have to be SORTED by the Query Coordinator, which very likely means that this operation won't fit into PGA memory of that single process, hence spill to TEMP causing potentially significant additional (and unnecessary) read and write I/O, all to be done serially by the Query Coordinator.This is a textbook example of a Parallel Execution plan that is deemed to take longer than the corresponding serial execution plan, and it is the execution plan that is preferred by the optimizer when left unhinted.Let's have a look at the Parallel Execution plan when using a HASH JOIN:


select /*+ parallel use_hash(t1 t2) */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id (+) = t2.fk
and t2.fk = 1

---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 199M| 2411 (1)| 00:00:29 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1000K| 199M| 2411 (1)| 00:00:29 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN RIGHT OUTER | | 1000K| 199M| 2411 (1)| 00:00:29 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | 105 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 1 | 105 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 105 | 2 (0)| 00:00:01 | | | |
|* 8 | INDEX UNIQUE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 | | | |
| 9 | PX BLOCK ITERATOR | | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | T2 | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."ID"(+)="T2"."FK")
8 - access("T1"."ID"(+)=1)
10 - filter("T2"."FK"=1)

Looking at the child operations' cost estimates of the HASH JOIN it becomes obvious that it is the costing of the HASH JOIN itself that makes the whole operation more costly than the MERGE JOIN, which is clearly questionable.So the strange thing about the MERGE JOIN Parallel Execution plan is that the join operation itself is done serially, whereas the HASH JOIN execution plan, although it uses the same access to the row sources (INDEX UNIQUE SCAN and FULL TABLE SCAN), happily runs in parallel.What causes this strange execution plan shape? Obviously it is the UNIQUE index on the other, smaller row source. Somehow the MERGE JOIN code is mislead by the UNIQUE index scan, which causes the join operation to run serially.Replacing the UNIQUE index with a NON-UNIQUE index (and using a UNIQUE constraint on top to achieve the same uniqueness) gives this execution plan:


drop index t1_idx;

create index t1_idx on t1 (id);

alter table t1 add constraint t1_uq unique (id) using index t1_idx;

select /*+ parallel */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id (+) = t2.fk
and t2.fk = 1

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 199M| 2406 (1)| 00:00:29 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1000K| 199M| 2406 (1)| 00:00:29 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE JOIN OUTER | | 1000K| 199M| 2406 (1)| 00:00:29 | Q1,01 | PCWP | |
| 4 | SORT JOIN | | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,01 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,01 | PCWC | |
|* 6 | TABLE ACCESS FULL | T2 | 1000K| 99M| 2403 (1)| 00:00:29 | Q1,01 | PCWP | |
|* 7 | SORT JOIN | | 1 | 105 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | 1 | 105 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 1 | 105 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 105 | 2 (0)| 00:00:01 | | | |
|* 12 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter("T2"."FK"=1)
7 - access("T1"."ID"(+)="T2"."FK")
filter("T1"."ID"(+)="T2"."FK")
12 - access("T1"."ID"(+)=1)

So now we still have the unnecessary SORT JOIN operation of the large row source, but at least the SORT JOIN and MERGE JOIN operations are now executed in parallel, which should make it far less threatening.Of course, a corresponding HASH JOIN will still be much more efficient for larger row sources, but needs to be hinted in this special case here.

Summary

For MERGE JOINs there are some special cases where the current costing model doesn't properly reflect the actual work - together with some strange behaviour of the MERGE JOIN code when using Parallel Execution this can lead to questionable execution plans preferred by the optimizer.Carefully check the resulting execution plans when using Parallel Execution and MERGE JOINs get preferred by the optimizer.

Is your good plan and initial implementation being properly maintained?

2013-03-05_08-32-06_412
Whether urban planning or Information Technology Systems, an outside pair of eyes might alert you to something you have gradually become blind to as it accrued. Consider these pictures: Some landscape architect planned ahead for the growth of these trees. Those curbed and slotted sections spread the load to prevent soil compaction, prevent wheeled machinery from coming too close to the tree trunk, and are easy to remove and replace. Unfortunately some time after the design and implementation, the planned replacement as the tree grew was forgotten. An at least annual review of your system implementation documents or just a look by someone from outside your shop might help you from going from the first picture to the second. We (Rightsizing, Inc.) do this sort of thing for Oracle Technology and Business Processes.
Image