Oracle LEFT JOIN View performance

Question

I have two tables, they aren't large tables. I have created a View based on this tables

select 
  tab_a.id as id, 
  tab_a.name as name 
from tableA as tab_a

UNION ALL

select 
  tab_b.id as id, 
  tab_b.name as name 
from tableB as tab_b

After all, I have a third table, lets call it tableMain with fields:

tableMain.id, tableMain.status, tableMain.viewId

viewId exists to join view

Final select look like

SELECT tableMain.id
  FROM tableMain
  LEFT OUTER JOIN VIEW ON tableMain.viewId=view.id

and join is very slow on a VIEW.

its fast if I join directly tableA or tableB, but not when using view.

It could be fast if I use view.name in select

SELECT tableMain.id, VIEW.name
  FROM tableMain
  LEFT OUTER JOIN VIEW ON tableMain.viewId=view.id

Not sure why VIEW JOIN working fast if I use VIEW field in select,

and how make VIEW JOIN fast without it.

Posting plans:

Good Plan (using VIEW.name in SELECT)

SELECT tableMain.id, VIEW.name
  FROM tableMain
  LEFT OUTER JOIN VIEW ON tableMain.viewId=view.id



| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT     |                |   220K|   440M|    50   (4)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |                |   220K|   440M|    50   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | **tableMain**  | 19796 |  1527K|    42   (0)| 00:00:01 |
|   3 |   VIEW               | ***VIEW***     |  1115 |  2194K|     6   (0)| 00:00:01 |
|   4 |    UNION-ALL         |                |       |       |            |          |
|   5 |     TABLE ACCESS FULL| **tableA**     |   818 |  1609K|     3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| **tableB**     |   297 |  5346 |     3   (0)| 00:00:01 |

Bad Plan (no view.name in select)

SELECT tableMain.id
  FROM tableMain
  LEFT OUTER JOIN VIEW ON tableMain.viewId=view.id

| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
|   0 | SELECT STATEMENT              |                 |   220K|    19M|    51   (6)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR               |                 |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10003        |   220K|    19M|    51   (6)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN RIGHT OUTER      |                 |   220K|    19M|    51   (6)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                |                 |  1115 | 14495 |     6   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH             | :TQ10002        |  1115 | 14495 |     6   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       BUFFER SORT             |                 |   220K|    19M|            |          |  Q1,02 | PCWP |            |
|   7 |        VIEW                   | ***VIEW***      |  1115 | 14495 |     6   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         UNION-ALL             |                 |       |       |            |          |  Q1,02 | PCWP |            |
|   9 |          PX BLOCK ITERATOR    |                 |   818 | 10634 |     3   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|  10 |           INDEX FAST FULL SCAN| ***tableA_PK*** |   818 | 10634 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |          BUFFER SORT          |                 |       |       |            |          |  Q1,02 | PCWC |            |
|  12 |           PX RECEIVE          |                 |   297 |  2079 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  13 |            PX SEND ROUND-ROBIN| :TQ10000        |   297 |  2079 |     3   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|* 14 |             TABLE ACCESS FULL | **tableB**      |   297 |  2079 |     3   (0)| 00:00:01 |        |      |            |
|  15 |     BUFFER SORT               |                 |       |       |            |          |  Q1,03 | PCWC |            |
|  16 |      PX RECEIVE               |                 | 19796 |  1527K|    42   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  17 |       PX SEND HASH            | :TQ10001        | 19796 |  1527K|    42   (0)| 00:00:01 |        | S->P | HASH       |
|  18 |        TABLE ACCESS FULL      | **tableMain**   | 19796 |  1527K|    42   (0)| 00:00:01 |        |      |            |

Why so big difference?

This sounds like an interesting optimizer issue. But before spending too much time on a performance problem it's important to do at least two things: 1) gather statistics on all relevant objects, 2) generate the execution plans. For statistics, a simple `exec dbms_stats.gather_table_stats(user, 'tableA'); ...` is good enough. For the execution plans, generate a good and bad plan with `explain plan for select ...` and then `select * from table(dbms_xplan.display);`. Then post the results here. — Jon Heller, Apr 09 '14 at 17:38

score 0 · Answer 1 · answered Apr 10 '14 at 03:46

0

Something is forcing parallelism. Does the view have any hints? Is there some type of plan management happening with this query? For example, is there an outline, SQL Plan Management, or profile setup only on the bad query? You may be able to find out by adding the Note section of the explain plan. If I'm right, there will be something like this in only one of the execution plans:

Note
-----
   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

Also it would help to define "very slow". If the good query runs in 0.01 seconds and the bad query runs in 2 seconds, the difference may be all because of the overhead of parallelism. But if the query was tuned for an environment with much larger data you may want to keep that the bad plan anyway - it may run better in production.

answered Apr 10 '14 at 03:46

Jon Heller

34,999
6
74
132

no, I do not think this query has any hint or something special, just simple query based on standard tables. Yes, i tried join regular table (tableB) instead of view and it runs 0.006, with view it takes between 2 and 2.5 seconds. – antohoho Apr 10 '14 at 04:52
There must be something weird going on, parallelism generally does not happen unless it is requested. Can you post the `Note` section of the bad plan? To fix the bad plan, on 11gR2, you can add a hint like `select /*+ no_parallel */ ...`. Although it's usually better to get to the bottom of a bad plan and not just throw hints at it. You may need to look at [this long list of reasons for parallelism](http://stackoverflow.com/a/21132027/409172). – Jon Heller Apr 10 '14 at 13:19
jonearles, I am sorry but I do not see Note in generated plan. I use EXPLAIN PLAN FOR and after select * from table(dbms_xplan.display);. /*+ no_parallel */ hint helps but I would like to know what causes it as well. – antohoho Apr 10 '14 at 17:23
Weird. I guess check the `DEGREE` of all the objects. And also look at `select * from V$SQL where sql_id = '...';` for both the good and bad SQL_ID and look for something "unusual" that is different between the two. – Jon Heller Apr 10 '14 at 18:32
all my tables have DEGREE equal to one. If I add hint select /*+ parallel */ - result the same as bad plan I see a Note section - automatic DOP: skipped because of IO calibrate statistics are missing – antohoho Apr 10 '14 at 22:01
Check the degree of the indexes too. – Jon Heller Apr 11 '14 at 01:23
yes, both tables in union has indexes, and both have degree equals to 4. But when I created index I did not mention this option. How come its 4 – antohoho Apr 11 '14 at 16:15
Someone must have manually changed it, by default it is 1. You may be able to find out some details about how and when it happened in DBA_AUDIT_TRAIL. Sometimes this happens when an index is rebuilt in parallel, to improve rebuild time, but someone forgets to reset the degree back to the original value. – Jon Heller Apr 11 '14 at 17:50

Oracle LEFT JOIN View performance

1 Answers1