By Franck Pachot

.
Here are the details about an execution plan screenshot I’ve tweeted recently because the numbers looked odd. It’s not a big problem, or maybe not a problem at all. Just something surprising. I don’t like when the numbers don’t match and then I try to reproduce and get an explanation, just to be sure there is not something hidden that I misunderstood.

Here is a similar test case joining two small tables DEMO1 and DEMO2 with specific stale statistics.

Hash Join

I start by forcing a full table scan to get a hash join:

select /*+ full(DEMO2) */ * from DEMO1 natural join DEMO2
Plan hash value: 3212315601
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |   130 (100)|      0 |00:00:00.01 |       3 |       |       |          |
|*  1 |  HASH JOIN         |       |      1 |    200 |   130   (1)|      0 |00:00:00.01 |       3 |  1696K|  1696K|  520K (0)|
|   2 |   TABLE ACCESS FULL| DEMO1 |      1 |    200 |     3   (0)|      0 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEMO2 |      0 |    100K|   127   (1)|      0 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

The cost of DEMO1 full table scan is 3. The cost of DEMO2 full table scan is 127. That’s a total of 130 (the cost of the hash join itself is negligible here)

Nested Loop

When forcing an index access, a nested loop will be used:

select /*+ index(DEMO2) */ * from DEMO1 natural join DEMO2
Plan hash value: 995663177
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |   203 (100)|      0 |00:00:00.01 |       3 |
|   1 |  NESTED LOOPS                |        |      1 |    200 |   203   (0)|      0 |00:00:00.01 |       3 |
|   2 |   NESTED LOOPS               |        |      1 |    200 |   203   (0)|      0 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS FULL         | DEMO1  |      1 |    200 |     3   (0)|      0 |00:00:00.01 |       3 |
|*  4 |    INDEX UNIQUE SCAN         | DEMOPK |      0 |      1 |     0   (0)|      0 |00:00:00.01 |       0 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEMO2  |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------

The cost of the index access is 1 and as it expected to run 200 loops the total cost is 200. With the full table scan of DEMO1 the total is 203.

Adaptive plan

Here is an explain plan to see the initial plan with active and inactive branches:

SQL> explain plan for
  2  select /*+              */ * from DEMO1 natural join DEMO2;
SQL> select * from table(dbms_xplan.display(format=>'adaptive'));
Plan hash value: 3212315601
------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |        |   200 |  6400 |   130   (1)| 00:00:01 |
|  *  1 |  HASH JOIN                    |        |   200 |  6400 |   130   (1)| 00:00:01 |
|-    2 |   NESTED LOOPS                |        |   200 |  6400 |   130   (1)| 00:00:01 |
|-    3 |    NESTED LOOPS               |        |       |       |            |          |
|-    4 |     STATISTICS COLLECTOR      |        |       |       |            |          |
|     5 |      TABLE ACCESS FULL        | DEMO1  |   200 |  1000 |     3   (0)| 00:00:01 |
|- *  6 |     INDEX UNIQUE SCAN         | DEMOPK |       |       |            |          |
|-    7 |    TABLE ACCESS BY INDEX ROWID| DEMO2  |     1 |    27 |   127   (1)| 00:00:01 |
|     8 |   TABLE ACCESS FULL           | DEMO2  |   100K|  2636K|   127   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

The active branches (full table scan) have the correct cost: 127 + 3 = 130

However, it’s not the case with inactive ones: no estimations for ‘INDEX UNIQUE SCAN’ and it seems that the ‘TABLE ACCESS BY INDEX ROWID’ get the cost from the full table scan (here 127).

It’s just an observation here. I’ve no explanation about it and I’ve no idea about the consequences except the big surprise when you see the numbers. I guess that the cost of the inactive branches is meaningless. What is important is that the right cost has been used to determine the inflection point.

The index access having a cost of 1, the cost of the nested loop will be higher than full table scan (estimated to 127) when there are more than 127 loops. This is what we see from the 10053 trace:

SQL> host grep ^DP DEMO14_ora_19470_OPTIMIZER.trc
DP: Found point of inflection for NLJ vs. HJ: card = 127.34

Now, as I have no rows in the tables, the nested loop branch will be activated in place of the hash join. So if we display the plan once it is resolved, we will see the lines with an unexpected cost:

Plan hash value: 995663177
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |   130 (100)|      0 |00:00:00.01 |       3 |
|   1 |  NESTED LOOPS                |        |      1 |    200 |   130   (1)|      0 |00:00:00.01 |       3 |
|   2 |   NESTED LOOPS               |        |      1 |        |            |      0 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS FULL         | DEMO1  |      1 |    200 |     3   (0)|      0 |00:00:00.01 |       3 |
|*  4 |    INDEX UNIQUE SCAN         | DEMOPK |      0 |        |            |      0 |00:00:00.01 |       0 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEMO2  |      0 |      1 |   127   (1)|      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------

I think it’s harmless, just a bit misleading. 127 is not the cost of the index access. It’s the cost of the full table scan.
I had this surprise when trying to understand why the optimizer choose a full scan instead of index access. This is probably the only reason why I look at the cost: use hints to force the plan that I think is better, in order to understand where the optimizer thinks it is more expensive.

Post Views: 316
Thumbnail [60x60]
by
Oracle Team