Skip to Main Content
  • Questions

  • Unable to understand the Explain plan for a query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mayank.

Asked: June 15, 2017 - 7:34 pm UTC

Last updated: June 17, 2017 - 1:18 am UTC

Version: Oracle 11g Exadata

Viewed 10K+ times! This question is

You Asked 

Hi Tom,

I recently started going into the performance tuning stuff, and that's when i got stuck at one of the explain plan.
Below is the Explain plan:

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                            |    11P|    15E|       |    35G (89)|999:59:59 |        |      |
|*  1 |  TABLE ACCESS STORAGE FULL FIRST ROWS  | X_SAP_N                    |     1 |    56 |       |    92   (0)| 00:00:02 |        |      |
|*  2 |  TABLE ACCESS STORAGE FULL FIRST ROWS  | X_SAP_N                    |     1 |    56 |       |    92   (0)| 00:00:02 |        |      |
|   3 |  MERGE JOIN OUTER                      |                            |    11P|    15E|       |    35G (89)|999:59:59 |        |      |
|   4 |   SORT JOIN                            |                            |  1160G|  8442T|    16P|  4115M  (2)|999:59:59 |        |      |
|*  5 |    HASH JOIN RIGHT OUTER               |                            |  1160G|  8442T|       |   112M  (5)|376:30:16 |        |      |
|*  6 |     TABLE ACCESS STORAGE FULL          | X_SAP_T0                   |   259 |  3885 |       |     8   (0)| 00:00:01 |        |      |
|*  7 |     HASH JOIN RIGHT OUTER              |                            |   767G|  5573T|    54M|   109M  (3)|365:28:41 |        |      |
|   8 |      MAT_VIEW ACCESS STORAGE FULL      | MVW_D_VEN                  |   267K|    51M|       |  1376   (1)| 00:00:17 |        |      |
|*  9 |      HASH JOIN RIGHT OUTER             |                            |   286M|  2079G|       |  1749K  (1)| 05:49:56 |        |      |
|  10 |       VIEW                             | VW_D_MAT                   |     1 |    64 |       |     2   (0)| 00:00:01 |        |      |
|  11 |        UNION-ALL                       |                            |       |       |       |            |          |        |      |
|  12 |         REMOTE                         |                            |       |       |       |            |          | LNK_B~ | R->S |
|  13 |         FAST DUAL                      |                            |     1 |       |       |     2   (0)| 00:00:01 |        |      |
|* 14 |       HASH JOIN RIGHT OUTER            |                            |   286M|  2062G|    23M|  1748K  (1)| 05:49:46 |        |      |
|  15 |        MAT_VIEW ACCESS STORAGE FULL    | MVW_D_GEO                  | 79350 |    22M|       |   653   (1)| 00:00:08 |        |      |
|* 16 |        TABLE ACCESS STORAGE FULL       | F_SPEND_ANALYTIC           |  4662K|    32G|       |   107K  (1)| 00:21:36 |        |      |
|* 17 |   SORT JOIN                            |                            |  2283K|   202M|   495M|   337K  (1)| 01:07:28 |        |      |
|  18 |    VIEW                                | VW_MATERIAL                |  2283K|   202M|       |   291K  (1)| 00:58:22 |        |      |
|* 19 |     HASH JOIN RIGHT OUTER              |                            |  2283K|   442M|       |   291K  (1)| 00:58:22 |        |      |
|  20 |      VIEW                              | VW_PROD                    | 25712 |   577K|       |   171   (1)| 00:00:03 |        |      |
|* 21 |       HASH JOIN                        |                            | 25712 |  1079K|       |   171   (1)| 00:00:03 |        |      |
|  22 |        TABLE ACCESS STORAGE FULL       | X_BW_CUBE_S                | 27485 |   402K|       |    34   (0)| 00:00:01 |        |      |
|* 23 |        TABLE ACCESS STORAGE FULL       | X_BW_CUBE_T             | 25712 |   703K|       |   137   (1)| 00:00:02 |        |      |
|* 24 |      HASH JOIN RIGHT OUTER             |                            |  2283K|   392M|       |   291K  (1)| 00:58:20 |        |      |
|  25 |       VIEW                             | VW_PROD                    | 25712 |   577K|       |   171   (1)| 00:00:03 |        |      |
|* 26 |        HASH JOIN                       |                            | 25712 |  1079K|       |   171   (1)| 00:00:03 |        |      |
|  27 |         TABLE ACCESS STORAGE FULL      | X_BW_CUBE_S                | 27485 |   402K|       |    34   (0)| 00:00:01 |        |      |
|* 28 |         TABLE ACCESS STORAGE FULL      | X_BW_CUBE_T                | 25712 |   703K|       |   137   (1)| 00:00:02 |        |      |
|* 29 |       HASH JOIN RIGHT OUTER            |                            |  2283K|   341M|       |   291K  (1)| 00:58:18 |        |      |
|  30 |        VIEW                            | VW_PROD             | 25712 |   577K|       |   171   (1)| 00:00:03 |        |      |
|* 31 |         HASH JOIN                      |                            | 25712 |  1079K|       |   171   (1)| 00:00:03 |        |      |
|  32 |          TABLE ACCESS STORAGE FULL     | X_SPROD                    | 27485 |   402K|       |    34   (0)| 00:00:01 |        |      |
|* 33 |          TABLE ACCESS STORAGE FULL     | X_TPROD                    | 25712 |   703K|       |   137   (1)| 00:00:02 |        |      |
|* 34 |        HASH JOIN RIGHT OUTER           |                            |  2283K|   291M|       |   291K  (1)| 00:58:16 |        |      |
|  35 |         VIEW                           | VW_PROD                    | 25712 |   577K|       |   171   (1)| 00:00:03 |        |      |
|* 36 |          HASH JOIN                     |                            | 25712 |  1079K|       |   171   (1)| 00:00:03 |        |      |
|  37 |           TABLE ACCESS STORAGE FULL    | X_SPROD                    | 27485 |   402K|       |    34   (0)| 00:00:01 |        |      |
|* 38 |           TABLE ACCESS STORAGE FULL    | X_TPROD                    | 25712 |   703K|       |   137   (1)| 00:00:02 |        |      |
|* 39 |         HASH JOIN RIGHT OUTER          |                            |  2283K|   241M|       |   291K  (1)| 00:58:14 |        |      |
|  40 |          VIEW                          | VW_PROD                    | 25712 |   577K|       |   171   (1)| 00:00:03 |        |      |
|* 41 |           HASH JOIN                    |                            | 25712 |  1079K|       |   171   (1)| 00:00:03 |        |      |
|  42 |            TABLE ACCESS STORAGE FULL   | X_SPROD                    | 27485 |   402K|       |    34   (0)| 00:00:01 |        |      |
|* 43 |            TABLE ACCESS STORAGE FULL   | X_TPROD                    | 25712 |   703K|       |   137   (1)| 00:00:02 |        |      |
|  44 |          VIEW                          | VM_NWVW_0                  |  2283K|   191M|       |   290K  (1)| 00:58:12 |        |      |
|  45 |           HASH UNIQUE                  |                            |  2283K|   831M|  1372M|   290K  (1)| 00:58:12 |        |      |
|* 46 |            HASH JOIN                   |                            |  2283K|   831M|   230M|   117K  (1)| 00:23:31 |        |      |
|* 47 |             HASH JOIN                  |                            |  2283K|   204M|   119M| 50594   (1)| 00:10:08 |        |      |
|* 48 |              TABLE ACCESS STORAGE FULL | X_TMATERIAL                |  2283K|    93M|       |  4277   (1)| 00:00:52 |        |      |
|* 49 |              HASH JOIN                 |                            |  2282K|   111M|    78M| 33559   (1)| 00:06:43 |        |      |
|  50 |               TABLE ACCESS STORAGE FULL| X_SMATERIAL                |  2283K|    52M|       |  2473   (1)| 00:00:30 |        |      |
|* 51 |               TABLE ACCESS STORAGE FULL| X_PMATERIAL                |  2282K|    58M|       | 22968   (1)| 00:04:36 |        |      |
|* 52 |             TABLE ACCESS STORAGE FULL  | X_MMATERIAL                |  2282K|   626M|       | 23150   (1)| 00:04:38 |        |      |

Here i am unable to understand how the step 14 is showing 286M rows. Is there any calculation happening in the background?

and Connor said...

A lot depends on what the optimizer thinks is the selectivity of the columns in the join. Here's a simple example:

SQL> create table t as select * from dba_objects;

Table created.

SQL> create table t1 as select * from t;

Table created.


OBJECT_ID is pretty much unique for these tables, so a join between them is a 1-to-1 relationship. And the execution plan reflects that.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select *
  2  from t, t1
  3  where t.object_id = t1.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 2281690008

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 77974 |    19M|       |  1912   (1)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      | 77974 |    19M|    10M|  1912   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 77974 |     9M|       |   424   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 77974 |     9M|       |   424   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID"(+))


But what if I joined on OWNER. 

SQL> select count(distinct owner) from t;

COUNT(DISTINCTOWNER)
--------------------
                  33


There's only 33 distinct owners, which means about 2500 rows per owner. So *each* row in T, when joined to T1 is going to give me back around 2500 rows. Hence that is a dramatic multiplying effect, and the optimizer reflects this:

SQL>
SQL> select *
  2  from t, t1
  3  where t.owner = t1.owner(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 2281690008

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   184M|    45G|       |  2802  (33)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |   184M|    45G|    10M|  2802  (33)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 77974 |     9M|       |   424   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 77974 |     9M|       |   424   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("T"."OWNER"="T1"."OWNER"(+))


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Query

Mayank Jain, June 16, 2017 - 3:01 am UTC

Hi Connor,

Thanks for the response. I am a bit confused here, My F_SPEND_ANALYTIC table has 32M records and MVW_D_GEO has 79k records (with all distinct records).
In this case why is the record count increasing so much?

Regards,
Mayank Jain

on cardinality

Rajeshwaran, Jeyabal, June 16, 2017 - 6:19 am UTC

....
. I am a bit confused here, My F_SPEND_ANALYTIC table has 32M records and MVW_D_GEO has 79k records (with all distinct records).
In this case why is the record count increasing so much?
....

The final cardinality of a set is not dependent on the volume of data in the table, it depends on the "Selectivity" of the set.

give this

|* 14 |       HASH JOIN RIGHT OUTER            |                            |   286M|  2062G|    23M|  1748K  (1)| 05:49:46 |        |      |
|  15 |        MAT_VIEW ACCESS STORAGE FULL    | MVW_D_GEO                  | 79350 |    22M|       |   653   (1)| 00:00:08 |        |      |
|* 16 |        TABLE ACCESS STORAGE FULL       | F_SPEND_ANALYTIC           |  4662K|    32G|       |   107K  (1)| 00:21:36 |        |      |


Table "F_SPEND_ANALYTIC" may have 32M records, but after the filter criteria, only 4662K records matches and that is been considered for the HASH join.

Materialized view "MVW_D_GEO" has 79350 rows in it.

when we join these two data sets, it is that each rows from the materialized view matches with the 3604 rows in the table "F_SPEND_ANALYTIC" - Hence 79350 rows with each matching with 3604 rows produces a total of 79350*3604 = 286000000 = 286M

Hope this helps.
Connor McDonald
June 17, 2017 - 1:18 am UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.