First Table is 550MB, Second Table is 26GB – Nested Loops or Full Table Scan? 3

3 10 2010

October 3, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the last blog article in addition to changing the Oracle Database release version, we made a couple of modifications to various parameters including: OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING, OPTIMIZER_MODE, and PGA_AGGREGATE_TARGET, all to try to determine the impact of those changes on the execution plans when a test table T1 (that was intended to simulate a 26GB table) was joined to a test table T2 (that was intended to simulate a 550MB table).  One thing is certain, Oracle Database’s query optimizer has a lot of options when joining two tables together, and some of those choices lead to efficient execution plans, while others do not.  By experimenting with the various parameters (setting the parameters to non-default values), we changed most of the hash joins to multiple nested loops joins, changed other hash joins to sort-merge joins, and in one case changed multiple nested loops joins into a single hash join.  All of those tests were performed with no-workload system (CPU) statistics.

In this blog post we will continue the experiments.  We will see what happens to the execution plans when we change the 200 character padding column values to 10 character padding column values, similar to a modifcation to my test script performed by another person.  For this test script I simply started with the previous test script and replaced T1 with TT1, and T2 with TT2.  The insert statements were changed as shown below:

INSERT INTO
  TT1
SELECT
  ROWNUM,
  10000000-ROWNUM,
  RPAD(TO_CHAR(ROWNUM),10,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

INSERT INTO
  TT2
SELECT
  ROWNUM,
  10000000-ROWNUM,
  RPAD(TO_CHAR(ROWNUM),10,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=100000);

What in the execution plans changed?  Obviously, the number of bytes predicted to leave each operation in the excution plan decreased, as did the temp tablespace requirements.  Comparing the baseline output from the baseline output from the previous blog article, we do see a couple of execution plan changes.  For example:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2
  7  FROM
  8    TT1,
  9    TT2
 10  WHERE
 11    TT1.COL1=TT2.COL1
 12    AND TT1.COL1 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3927111238

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  9998 |   322K|   118   (1)| 00:00:02 |
|*  1 |  HASH JOIN                   |              |  9998 |   322K|   118   (1)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT2          |  9999 |   107K|    58   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018343 |  9999 |       |    20   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    59   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018341 |  9999 |       |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT2"."COL1"<10000)
   5 - access("TT1"."COL1"<10000)

While the previous baseline had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 2371011080

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  1 |  HASH JOIN                   |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  2 |   VIEW                       | index$_join$_002 |  9999 |   107K|   260   (1)| 00:00:04 |
|*  3 |    HASH JOIN                 |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN         | SYS_C0018300     |  9999 |   107K|    22  (10)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |  9999 |   107K|   299   (1)| 00:00:04 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|   325   (0)| 00:00:04 |
|*  7 |    INDEX RANGE SCAN          | SYS_C0018298     |  9999 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   2 - filter("T2"."COL1"<10000)
   3 - access(ROWID=ROWID)
   4 - access("T2"."COL1"<10000)
   7 - access("T1"."COL1"<10000)

So, with the smaller COL3 padding column values the optimizer decided not to perform the index fast full scan of the index on COL2 of table TT2 (to avoid directly accessing table TT2), and now Oracle will perform one hash join rather than performing two hash joins.  Continuing:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2
  7  FROM
  8    TT1,
  9    TT2
 10  WHERE
 11    TT1.COL1=TT2.COL1
 12    AND TT1.COL2 > (10000000-10000);

Execution Plan
----------------------------------------------------------
Plan hash value: 4109682512

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  9999 |   322K|   169   (2)| 00:00:03 |
|*  1 |  HASH JOIN                   |              |  9999 |   322K|   169   (2)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    64   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TT1_COL2 |  9999 |       |    26   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | TT2          |   100K|  1074K|   104   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT1"."COL2">9990000)

While the previous baseline had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 512758489

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9999 |  2177K|       |  1238   (1)| 00:00:15 |
|*  1 |  HASH JOIN                   |                  |  9999 |  2177K|  2192K|  1238   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|       |   330   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_COL2      |  9999 |       |       |    26   (0)| 00:00:01 |
|   4 |   VIEW                       | index$_join$_002 |   100K|  1074K|       |   691   (1)| 00:00:09 |
|*  5 |    HASH JOIN                 |                  |       |       |       |            |          |
|   6 |     INDEX FAST FULL SCAN     | SYS_C0018300     |   100K|  1074K|       |   236   (1)| 00:00:03 |
|   7 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |   100K|  1074K|       |   299   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL2">9990000)
   5 - access(ROWID=ROWID)

Once again, with the smaller COL3 padding column values the optimizer decided not to perform the index fast full scan of the index on COL2 of table TT2 (to avoid directly accessing table TT2), and now Oracle will perform one hash join rather than performing two hash joins, while avoiding the use of approximately 2192K (if I recall correctly, that indicates 2,298,478,592 bytes because the scale for the TempSpc column is off by a factor of 1,024) of estimated temp space.

So, not many changes, other than Oracle’s optimizer no longer avoiding a table access through the use of a fast full index scan.

Decreasing the PGA_AGGREGATE_TARGET FROM 2,000MB to 1,000MB and to 100MB had no impact on the execution plans.

What happened when the OPTIMIZER_MODE was changed from the default of ALL_ROWS to FIRST_ROWS_1000?  If we just look at the changes from the new baseline:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2
  7  FROM
  8    TT1,
  9    TT2
 10  WHERE
 11    TT1.COL1=TT2.COL1
 12    AND TT1.COL1 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1414977455

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  1002 | 33066 |    66   (0)| 00:00:01 |
|   1 |  MERGE JOIN                   |              |  1002 | 33066 |    66   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TT2          |  1002 | 11022 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | SYS_C0018343 |  9999 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |              |  9999 |   214K|    59   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    59   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | SYS_C0018341 |  9999 |       |    21   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TT2"."COL1"<10000)
   4 - access("TT1"."COL1"="TT2"."COL1")
       filter("TT1"."COL1"="TT2"."COL1")
   6 - access("TT1"."COL1"<10000)

While the baseline had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 3927111238

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  9998 |   322K|   118   (1)| 00:00:02 |
|*  1 |  HASH JOIN                   |              |  9998 |   322K|   118   (1)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT2          |  9999 |   107K|    58   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018343 |  9999 |       |    20   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    59   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018341 |  9999 |       |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT2"."COL1"<10000)
   5 - access("TT1"."COL1"<10000)

So, the optimizer exchanged a hash join for a sort merge join to return the first 1,000 rows – note that the predicted number of rows dropped from 9,998 to 1,002 and that the calculated cost for the query dropped accordingly from 118 to 66.

What happened when the OPTIMIZER_MODE was changed from FIRST_ROWS_1000 to FIRST_ROWS_100?  We could predict that every execution plan that is expected to return more than 100 rows would likely see a change in its execution plan, if the execution plan had not already changed.  If we just look at the changes from the FIRST_ROWS_1000 output:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2,
  7    TT2.COL3
  8  FROM
  9    TT1,
 10    TT2
 11  WHERE
 12    TT1.COL1=TT2.COL1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1268144893

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100 |  4400 |   103   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |   100 |  4400 |   103   (0)| 00:00:02 |
|   3 |    TABLE ACCESS FULL         | TT1          |  1000 | 22000 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0018343 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TT2          |     1 |    22 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("TT1"."COL1"="TT2"."COL1")

While the FIRST_ROWS_1000 execution had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 2456117181

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 66000 |       |   295   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |  1000 | 66000 |  3328K|   295   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| TT2  |   100K|  2148K|       |   104   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| TT1  | 10000 |   214K|       |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")

As we saw with the earlier tests in the previous blog article, changing the optimizer mode to FIRST_ROWS_100 was sufficient to cause the optimizer to switch from a hash join to two nested loops joins.  The clustering factor of column COL2 was sufficiently high enough for the execution plan joining that column in the two tables to remain as a hash join.  Continuing:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2,
  7    TT2.COL3
  8  FROM
  9    TT1,
 10    TT2
 11  WHERE
 12    TT1.COL1=TT2.COL1
 13    AND TT1.COL1 BETWEEN 1 AND 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1414977455

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   102 |  4488 |    11   (0)| 00:00:01 |
|   1 |  MERGE JOIN                   |              |   102 |  4488 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TT2          |   103 |  2266 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | SYS_C0018343 |  1000 |       |     2   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |              |  1000 | 22000 |     8   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TT1          |  1000 | 22000 |     8   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | SYS_C0018341 |  1000 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TT2"."COL1">=1 AND "TT2"."COL1"<=1000)
   4 - access("TT1"."COL1"="TT2"."COL1")
       filter("TT1"."COL1"="TT2"."COL1")
   6 - access("TT1"."COL1">=1 AND "TT1"."COL1"<=1000)

While the FIRST_ROWS_1000 execution had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 473932778

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   999 | 43956 |    16   (7)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |   999 | 43956 |    16   (7)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT1          |  1000 | 22000 |     8   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018341 |  1000 |       |     4   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TT2          |  1000 | 22000 |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018343 |  1000 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT1"."COL1">=1 AND "TT1"."COL1"<=1000)
   5 - access("TT2"."COL1">=1 AND "TT2"."COL1"<=1000)

Another one of the hash joins changed into a sort merge join.  Continuing:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2
  7  FROM
  8    TT1,
  9    TT2
 10  WHERE
 11    TT1.COL1=TT2.COL1
 12    AND TT1.COL1 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 382692435

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   102 |  3366 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |   102 |  3366 |    14   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018341 |  9999 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018343 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TT2          |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("TT1"."COL1"<10000)
   5 - access("TT1"."COL1"="TT2"."COL1")
       filter("TT2"."COL1"<10000)

Another set of nested loops joins, did the execution plan previously show a hash join?  The FIRST_ROWS_1000 execution had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 1414977455

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  1002 | 33066 |    66   (0)| 00:00:01 |
|   1 |  MERGE JOIN                   |              |  1002 | 33066 |    66   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TT2          |  1002 | 11022 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | SYS_C0018343 |  9999 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |              |  9999 |   214K|    59   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    59   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | SYS_C0018341 |  9999 |       |    21   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TT2"."COL1"<10000)
   4 - access("TT1"."COL1"="TT2"."COL1")
       filter("TT1"."COL1"="TT2"."COL1")
   6 - access("TT1"."COL1"<10000)

So, we lost the sort merge join that we picked up with the FIRST_ROWS_1000 OPTIMIZER_MODE and switched to two nested loops joins – interesting… is there a sillyness scale indicator here from hash join to sort merge join to nested loops?  :-)  Continuing:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2
  7  FROM
  8    TT1,
  9    TT2
 10  WHERE
 11    TT1.COL1=TT2.COL1
 12    AND TT1.COL2 > (10000000-10000);

Execution Plan
----------------------------------------------------------
Plan hash value: 654336742

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   101 |  3333 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |   101 |  3333 |    14   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_TT1_COL2 |  9999 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018343 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TT2          |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("TT1"."COL2">9990000)
   5 - access("TT1"."COL1"="TT2"."COL1")

 The FIRST_ROWS_1000 execution had the following execution plan for the same query:

Execution Plan
----------------------------------------------------------
Plan hash value: 4109682512

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1001 | 44044 |    77   (2)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |  1001 | 44044 |    77   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT1          |  9999 |   214K|    64   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TT1_COL2 |  9999 |       |    26   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | TT2          | 10011 |   107K|    12   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT1"."COL2">9990000)

We see that the optimizer has switched another execution plan from a hash join to multiple nested loops joins.

The output with FIRST_ROWS_10 and FIRST_ROWS_1 specified for the optimizer mode showed nested loops joins for all execution plans.

When compared to the new baseline, changing the OPTIMIZER_INDEX_CACHING had no effect on the execution plans in the test case.

As we saw in the earlier tests of the previous blog articles, changing the OPTIMIZER_INDEX_COST_ADJ parameter from the default value of 100 to 50 only impacted one SQL statement’s execution plan:

SQL> SELECT
  2    TT1.COL1,
  3    TT1.COL2,
  4    TT1.COL3,
  5    TT2.COL1,
  6    TT2.COL2,
  7    TT2.COL3
  8  FROM
  9    TT1,
 10    TT2
 11  WHERE
 12    TT1.COL1=TT2.COL1
 13    AND TT1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 473932778

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     9 |   396 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |     9 |   396 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TT1          |    10 |   220 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018341 |    10 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TT2          |    10 |   220 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018343 |    10 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TT1"."COL1"="TT2"."COL1")
   3 - access("TT1"."COL1">=1 AND "TT1"."COL1"<=10)
   5 - access("TT2"."COL1">=1 AND "TT2"."COL1"<=10)

While the new baseline showed:

Execution Plan
----------------------------------------------------------
Plan hash value: 382692435

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |   396 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |   396 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TT1          |    10 |   220 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018341 |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018343 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TT2          |     1 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("TT1"."COL1">=1 AND "TT1"."COL1"<=10)
   5 - access("TT1"."COL1"="TT2"."COL1")
       filter("TT2"."COL1"<=10 AND "TT2"."COL1">=1)

The optimizer switched from two nested loops joins to a single hash join.  What happened when other OPTIMIZER_INDEX_COST_ADJ values were tested?  We see essentially the same pattern of sort merge joins and nested loops joins introduced as we saw with the larger padding values in column COL3 of the test tables.

What is left to test?  We could switch from no-workload system (CPU) statistics to workload system (CPU) statistics.  If we execute the following, and then perform a variety of actions that result in physical reads for a 30 minute time period:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>30)

We might find that the contents of SYS.AUX_STATS$ change from this:

PNAME                PVAL1
--------------- ----------
STATUS
DSTART
DSTOP
FLAGS                    1
CPUSPEEDNW      2128.85496
IOSEEKTIM               10
IOTFRSPEED            4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

To this:

PNAME                PVAL1
--------------- ----------
STATUS
DSTART
DSTOP
FLAGS                    0
CPUSPEEDNW      2128.85496
IOSEEKTIM               10
IOTFRSPEED            4096
SREADTIM          2490.746
MREADTIM          9162.743
CPUSPEED              2714
MBRC                    78
MAXTHR              302080
SLAVETHR

You might be wondering what happens to the execution plans with the above workload system statistics in place.  Part 4 of this series will take a look at those execution plans.


Actions

Information

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




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: