Vishal desai’s Oracle Blog

April 5, 2013

Outer Join, Ordered hint and SWAP_JOIN_INPUTS

Filed under: Performance Tuning, SQL — vishaldesai @ 6:33 pm

Outer joins access tables in following order:

from t1, t2 where t1.col1(+)=t2.col1 –> table will be accessed in order t2, t1

from t1, t2 where t1.col1=t2.col1(+) –> table will be accessed in order t1,t2

One of my developer had to do some data cleanup so he quickly staged data in table t1, t2 was our large fact table and ran following query (test case). Query ran for hours on Exadata and did not finish. Smile

select /*+ ordered full(t1) full(t2) parallel(t1,4) parallel(t2,4) */ t1.*, t2.* from t1,t2 where t1.col1(+)=t2.col1;

When I looked at SQL monitoring report driving table was t2 (large fact table). Below is the output from test case:

SQL> create table t1(c1, c2)
  2  as
  3  select level, rpad('x',10)
  4  from dual
  5  connect by level <= 200000
  6  ;

Table created.

SQL>
SQL> create table t2(c1, c2)
  2  as
  3  select level, rpad('x',10)
  4  from dual
  5  connect by level <= 100
  6  ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T2');

PL/SQL procedure successfully completed.


SQL> explain plan for
  2  select /*+ ordered */ t2.*,t1.*
  3  from t2, t1
  4  where t1.c1 = t2.c1(+);

Explained.

SQL>
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1823443478

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   200K|  5859K|       |   436   (1)| 00:00:06 |
|*  1 |  HASH JOIN OUTER           |      |   200K|  5859K|  5472K|   436   (1)| 00:00:06 |
|   2 |   TABLE ACCESS STORAGE FULL| T1   |   200K|  3125K|       |   165   (1)| 00:00:02 |
|   3 |   TABLE ACCESS STORAGE FULL| T2   |   100 |  1400 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1"(+))

22 rows selected.

SQL> explain plan for
  2  select t2.*,t1.*
  3  from t2, t1
  4  where t1.c1 = t2.c1(+);

Explained.

SQL>
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 312430291

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   200K|  5859K|   169   (2)| 00:00:03 |
|*  1 |  HASH JOIN RIGHT OUTER     |      |   200K|  5859K|   169   (2)| 00:00:03 |
|   2 |   TABLE ACCESS STORAGE FULL| T2   |   100 |  1400 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T1   |   200K|  3125K|   165   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1"(+))

When ORDERED hint was added it used t1 (large table as driving table) with hash join outer and when ORDERED hint was NOT used it used t2 (small table as driving table) with hash join right outer.

Output on left is without ORDERED hint and on right is with ORDERED hint.

image

image

image

 

So based on above output looks like that when ORDERED hint was used with OUTER JOIN, Oracle optimizer does not go through join permutations (in this particular case swapping join inputs) and hence choose T1 (larger table) as driving table. When ORDERED hint was removed, Oracle optimizer computes cost of swapping join inputs and choose T2 (smaller table) as driving table. Why Oracle considered join order as t1, t2 with ordered hint? I think because of outer join.

For my production issue, I asked ETL/development team to remove ORDERED hint and query ran in 12 minutes.

Advertisements

2 Comments »

  1. Hi, what tool You’ve used for comparing different plans ?
    Greg

    Comment by goryszewskiggreg — May 25, 2013 @ 6:25 am

    • Try diff_plan_awr or diff_plan.

      Thanks,
      Vishal

      Comment by vishaldesai — May 25, 2013 @ 12:28 pm


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: