October 4, 2010
(Back to the Previous Post in the Series)
The previous blog articles in this series used the following no workload system (CPU) statistics while trying to determine if Oracle Database’s optimizer prefers hash joins or nested loops when joining a couple of test tables:
SQL> SELECT 2 PNAME, 3 PVAL1 4 FROM 5 SYS.AUX_STATS$; PNAME PVAL1 --------------- ---------- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2128.85496 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM MREADTIM CPUSPEED MBRC MAXTHR SLAVETHR
I then collected workload system (CPU) statistics for a period of 30 minutes while performing various index based and full table scan queries using the sample tables created in the earlier blog articles of this series. The generated workload system (CPU) statistics are as follows:
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
If we check the documentation, we see that the units for the SREADTIM, MREADTIM, and IOSEEKTIM values are in ms (1/1000th of a second). Something is very strange about those automatically collected SREADTIM and MREADTIM values, but they must be correct because they were automatically collected (9.1 seconds for a multi-block read? If this statistic really has a unit of ms, how would an average single block read time into the buffer cache of 0.1 ms from SSD storage devices be represented?). The MBRC value is also probably a little large, but it does likely represent the average number of blocks read during multi-block reads during the statistics collection period. We could expect to see a couple of changes to the execution plans with the workload system statistics, and probably would see a significant change in the estimated execution time if the SREADTIM and MREADTIM values are in fact stated in milliseconds.
I might expect to see statistics something like the following (from another server):
SQL> SELECT 2 PNAME, 3 PVAL1 4 FROM 5 SYS.AUX_STATS$; PNAME PVAL1 --------------- ---------- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 1077.92208 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM 4.754 MREADTIM 7.432 CPUSPEED 1335 MBRC 32 MAXTHR 18432 SLAVETHR
But then the MAXTHR value in the above from the second server, according to the documentation, appears to be very suspect since the documentation states that this is the maximum throughput that the I/O subsystem can deliver in bytes per second.
Let’s take a look through the workload baseline output and compare it with the no workload baseline output, only showing when the execution plan changed:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL1 < 10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2140947373
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 2177K| 471 (1)| 00:19:32 |
|* 1 | HASH JOIN | | 9998 | 2177K| 471 (1)| 00:19:32 |
|* 2 | TABLE ACCESS FULL | T2 | 9999 | 107K| 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| 325 (0)| 00:13:30 |
|* 4 | INDEX RANGE SCAN | SYS_C0018298 | 9999 | | 21 (0)| 00:00:53 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
2 - filter("T2"."COL1"<10000)
4 - access("T1"."COL1"<10000)
The no workload baseline plan for the same SQL statement:
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)
With the workload system statistics in place the optimizer is no longer performing an index join operation to avoid visiting the T2 table. Notice that while the calculated costs decreased slightly with the workload statistics, the estimated execution time increased significantly. Continuing:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL2 > (10000000-10000);
Execution Plan
----------------------------------------------------------
Plan hash value: 570481587
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 550 (1)| 00:22:49 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 550 (1)| 00:22:49 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 330 (0)| 00:13:42 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 26 (0)| 00:01:05 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 1074K| | 145 (0)| 00:06:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
The no workload execution plan for the same SQL statement :
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)
Again, with the workload system statistics in place the optimizer is no longer utilizing an index join operation to avoid accessing the table T2. Also, the calculated cost dropped from 1238 to 550 while the estimated execution time increased from 15 seconds to 22 minutes and 49 seconds. Specifying smaller PGA_AGGREGATE_TARGET values did not cause the execution plans to deviate from those found for the no workload baseline.
Continuing with test results from changing the OPTIMIZER_MODE from ALL_ROWS, starting with FIRST_ROWS_1000, comparing workload with no workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL1=T2.COL1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 621K| | 559 (1)| 00:23:12 |
|* 1 | HASH JOIN | | 1000 | 621K| 21M| 559 (1)| 00:23:12 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 2070K| | 16 (0)| 00:00:40 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
The no workload output for the same SQL statement with the OPTIMIZER_MODE set to FIRST_ROWS_1000:
Execution Plan
----------------------------------------------------------
Plan hash value: 2077217387
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 414K| 1085 (1)| 00:00:14 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1000 | 414K| 1085 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 2070K| 84 (0)| 00:00:02 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0018300 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 212 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL1"="T2"."COL1")
In the above, the workload statistics execution plan completely avoided the index access paths and performed a hash join rather than two nested loops joins.
For the OPTIMIZER_MODEs FIRST_ROWS_100, FIRST_ROWS_10, and FIRST_ROWS_1 the workload execution plans did not differ from the no workload execution plans for all SQL statements.
Next, we will take a look at what changed when the INDEX_COST_ADJ parameter was set to 50 for the execution plans with the workload system statistics in place:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL2 > (10000000-10000);
Execution Plan
----------------------------------------------------------
Plan hash value: 570481587
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 385 (1)| 00:15:58 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 385 (1)| 00:15:58 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 165 (0)| 00:06:51 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 13 (0)| 00:00:33 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 1074K| | 145 (0)| 00:06:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
INDEX_COST_ADJ = 50, no workload:
Execution Plan
----------------------------------------------------------
Plan hash value: 512758489
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 859 (1)| 00:00:11 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 859 (1)| 00:00:11 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 165 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 13 (0)| 00:00:01 |
| 4 | VIEW | index$_join$_002 | 100K| 1074K| | 477 (1)| 00:00:06 |
|* 5 | HASH JOIN | | | | | | |
| 6 | INDEX FAST FULL SCAN | SYS_C0018300 | 100K| 1074K| | 118 (0)| 00:00:02 |
| 7 | INDEX FAST FULL SCAN | IND_T2_COL2 | 100K| 1074K| | 149 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
5 - access(ROWID=ROWID)
Once again, Oracle Database’s optimizer decided not to perform an index join operation to avoid accessing table T2 when the workload system statistics were in place. Continuing with the INDEX_COST_ADJ parameter set to 20 with workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL1=T2.COL1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 5543 (1)| 03:50:06 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 5543 (1)| 03:50:06 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 1431 (0)| 00:59:25 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
The execution plan for the same SQL statement with the INDEX_COST_ADJ parameter set to 20 with no workload system statistics:
Execution Plan
----------------------------------------------------------
Plan hash value: 3187704125
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 11880 (1)| 00:02:23 |
| 1 | MERGE JOIN | | 100K| 40M| | 11880 (1)| 00:02:23 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 202M| | 6441 (1)| 00:01:18 |
| 3 | INDEX FULL SCAN | SYS_C0018298 | 1000K| | | 377 (1)| 00:00:05 |
|* 4 | SORT JOIN | | 100K| 20M| 43M| 5439 (1)| 00:01:06 |
| 5 | TABLE ACCESS FULL | T2 | 100K| 20M| | 825 (1)| 00:00:10 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL1"="T2"."COL1")
filter("T1"."COL1"="T2"."COL1")
As we can see from the above, the optimizer has oddly decided to perform a hash join with full table scans, even though we have told the optimizer that the index access paths are 1/5th the original calculated costs. I suspect that the hash join (likely with the tables being read by direct path reads in 11.2.0.1) will be a bit more efficient than a sort merge join that appeared with the no workload system statistics, but we will not know that until we actually execute the SQL statement. You might recall that with the INDEX_COST_ADJ parameter set to 10 with no workload system statistics, all of the hash joins in the execution plans were replaced with either sort merge joins or nested loops joins.
Continuing with the INDEX_COST_ADJ parameter set to 20 with workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL2=T2.COL2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 5543 (1)| 03:50:06 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 5543 (1)| 03:50:06 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 1431 (0)| 00:59:25 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"="T2"."COL2")
The execution plan for the same SQL statement with the INDEX_COST_ADJ parameter set to 20, no workload:
Execution Plan
----------------------------------------------------------
Plan hash value: 1173042340
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 11979 (1)| 00:02:24 |
| 1 | MERGE JOIN | | 100K| 40M| | 11979 (1)| 00:02:24 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 202M| | 6540 (1)| 00:01:19 |
| 3 | INDEX FULL SCAN | IND_T1_COL2 | 1000K| | | 476 (1)| 00:00:06 |
|* 4 | SORT JOIN | | 100K| 20M| 43M| 5439 (1)| 00:01:06 |
| 5 | TABLE ACCESS FULL | T2 | 100K| 20M| | 825 (1)| 00:00:10 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL2"="T2"."COL2")
filter("T1"."COL2"="T2"."COL2")
We again see the optimizer selecting a hash join and full table scans when the column having an index with a high clustering factor is the join column with the workload system statistics in place.
Continuing with the INDEX_COST_ADJ parameter set to 20 with workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL2 > (10000000-10000);
Execution Plan
----------------------------------------------------------
Plan hash value: 570481587
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 286 (1)| 00:11:52 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 286 (1)| 00:11:52 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 66 (0)| 00:02:45 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 5 (0)| 00:00:13 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 1074K| | 145 (0)| 00:06:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
INDEX_COST_ADJ = 20, no workload:
Execution Plan
----------------------------------------------------------
Plan hash value: 512758489
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 632 (1)| 00:00:08 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 632 (1)| 00:00:08 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 66 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 5 (0)| 00:00:01 |
| 4 | VIEW | index$_join$_002 | 100K| 1074K| | 349 (1)| 00:00:05 |
|* 5 | HASH JOIN | | | | | | |
| 6 | INDEX FAST FULL SCAN | SYS_C0018300 | 100K| 1074K| | 47 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | IND_T2_COL2 | 100K| 1074K| | 60 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
5 - access(ROWID=ROWID)
In the above, we see hash joins both with and without workload system statistics, but without workload system statistics the optimizer decided to completely avoid directly accessing table T2.
Dropping the INDEX_COST_ADJ parameter down to a value of 10 with workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL1=T2.COL1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 5543 (1)| 03:50:06 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 5543 (1)| 03:50:06 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 1431 (0)| 00:59:25 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
INDEX_COST_ADJ = 10, no workload:
Execution Plan
----------------------------------------------------------
Plan hash value: 3187704125
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 8660 (1)| 00:01:44 |
| 1 | MERGE JOIN | | 100K| 40M| | 8660 (1)| 00:01:44 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 202M| | 3221 (1)| 00:00:39 |
| 3 | INDEX FULL SCAN | SYS_C0018298 | 1000K| | | 189 (1)| 00:00:03 |
|* 4 | SORT JOIN | | 100K| 20M| 43M| 5439 (1)| 00:01:06 |
| 5 | TABLE ACCESS FULL | T2 | 100K| 20M| | 825 (1)| 00:00:10 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL1"="T2"."COL1")
filter("T1"."COL1"="T2"."COL1")
Again, we see what is likely a sensible hash join with two full table scans with the workload system statistics, but a sort merge join with no workload system statistics. Continuing with the INDEX_COST_ADJ parameter set to 10 with workload:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL2=T2.COL2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 5543 (1)| 03:50:06 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 5543 (1)| 03:50:06 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 1431 (0)| 00:59:25 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"="T2"."COL2")
INDEX_COST_ADJ = 10, no workload:
Execution Plan
----------------------------------------------------------
Plan hash value: 1173042340
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 8709 (1)| 00:01:45 |
| 1 | MERGE JOIN | | 100K| 40M| | 8709 (1)| 00:01:45 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 202M| | 3270 (1)| 00:00:40 |
| 3 | INDEX FULL SCAN | IND_T1_COL2 | 1000K| | | 238 (1)| 00:00:03 |
|* 4 | SORT JOIN | | 100K| 20M| 43M| 5439 (1)| 00:01:06 |
| 5 | TABLE ACCESS FULL | T2 | 100K| 20M| | 825 (1)| 00:00:10 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL2"="T2"."COL2")
filter("T1"."COL2"="T2"."COL2")
Another hash join with workload system statistics and a sort merge join without (all hash joins disappeared with no workload statistics when the INDEX_COST_ADJ parameter was set to 10).
There were no differences between the execution plans for the workload and no workload system statistics when the INDEX_COST_ADJ parameter set to 1.
If we assume that Oracle Database’s collection procedure somehow collected the statistics wrong, and it really does not take 2.491 seconds to complete a single block read into the buffer cache and 9.163 seconds to perform a multi-block read into the buffer cache, we might be willing to believe that some of the above execution plans might not be ideal. What if the server actually had a RAID array of SSD drives, or an attached SAN with a large built-in cache? Maybe we would consider manually setting the workload system statistics like this:
EXEC DBMS_STATS.SET_SYSTEM_STATS('SREADTIM',0.1)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MREADTIM',0.2)
If we then query SYS.AUX_STATS$, we would see something like this:
SQL> SELECT 2 PNAME, 3 PVAL1 4 FROM 5 SYS.AUX_STATS$; PNAME PVAL1 --------------- ---------- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2128.85496 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM .1 MREADTIM .2 CPUSPEED 2714 MBRC 78 MAXTHR 302080 SLAVETHR
Let’s run the test script again to see how the execution plans change. From the new test output, comparing the new workload system statistics execution plans with the original workload execution plans:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL1=T2.COL1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 6447 (45)| 00:00:01 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 6447 (45)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 229 (66)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 2274 (66)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
Looks like the SQL statement will complete in about 1 second. The original workload system statistics baseline plan looked like this:
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 40M| | 5543 (1)| 03:50:06 |
|* 1 | HASH JOIN | | 100K| 40M| 21M| 5543 (1)| 03:50:06 |
| 2 | TABLE ACCESS FULL| T2 | 100K| 20M| | 145 (0)| 00:06:02 |
| 3 | TABLE ACCESS FULL| T1 | 1000K| 202M| | 1431 (0)| 00:59:25 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
So, comparing the two, the original baseline cost was less (5543 compared to 6447), but the original baseline was expected to complete in three hours and 50 minutes rather than one second. Other than the time and cost estimates changing, the execution plans remained the same for the two sets of workload statistics execution plans – until we take a look at what happens when the OPTIMIZER_INDEX_COST_ADJ parameter is changed to 50 or less. What do we see as changed execution plans (at a value of 50 for OPTIMIZER_INDEX_COST_ADJ)?
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL1 < 10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3560619641
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 2177K| 357 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 9998 | 2177K| 357 (9)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 9999 | 107K| 173 (7)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0018300 | 9999 | | 14 (29)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| 174 (7)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0018298 | 9999 | | 14 (22)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T2"."COL1"<10000)
5 - access("T1"."COL1"<10000)
While the automatically generated system statistics with the same value of 50 for the OPTIMIZER_INDEX_COST_ADJ produced the following execution plan (a value of 20 for this parameter also generated the same execution plan):
Execution Plan
----------------------------------------------------------
Plan hash value: 2371011080
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 2177K| 293 (1)| 00:12:09 |
|* 1 | HASH JOIN | | 9998 | 2177K| 293 (1)| 00:12:09 |
|* 2 | VIEW | index$_join$_002 | 9999 | 107K| 130 (1)| 00:05:23 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX RANGE SCAN | SYS_C0018300 | 9999 | 107K| 11 (10)| 00:00:28 |
| 5 | INDEX FAST FULL SCAN | IND_T2_COL2 | 9999 | 107K| 149 (0)| 00:06:11 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| 163 (0)| 00:06:45 |
|* 7 | INDEX RANGE SCAN | SYS_C0018298 | 9999 | | 11 (0)| 00:00:27 |
-------------------------------------------------------------------------------------------------
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)
The optimizer is no longer avoiding the table access through the use of the IND_T2_COL2 index.
When the OPTIMIZER_INDEX_COST_ADJ parameter is set to 10 we see a couple of additional execution plan changes:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL2 > (10000000-10000);
Execution Plan
----------------------------------------------------------
Plan hash value: 570481587
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 395 (59)| 00:00:01 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 395 (59)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 35 (6)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 1074K| | 221 (65)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
Compared to before the workload system statistics were modified at the same OPTIMIZER_INDEX_COST_ADJ parameter value of 10:
Execution Plan
----------------------------------------------------------
Plan hash value: 512758489
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 2177K| | 240 (1)| 00:09:57 |
|* 1 | HASH JOIN | | 9999 | 2177K| 2192K| 240 (1)| 00:09:57 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| | 33 (0)| 00:01:23 |
|* 3 | INDEX RANGE SCAN | IND_T1_COL2 | 9999 | | | 3 (0)| 00:00:07 |
| 4 | VIEW | index$_join$_002 | 100K| 1074K| | 132 (0)| 00:05:30 |
|* 5 | HASH JOIN | | | | | | |
| 6 | INDEX FAST FULL SCAN | SYS_C0018300 | 100K| 1074K| | 24 (0)| 00:00:59 |
| 7 | INDEX FAST FULL SCAN | IND_T2_COL2 | 100K| 1074K| | 30 (0)| 00:01:15 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL2">9990000)
5 - access(ROWID=ROWID)
Somewhat surprisingly, even with a OPTIMIZER_INDEX_COST_ADJ parameter value of 1, with workload system statistics a couple of the execution plans still showed hash joins, while that was not the case with the no workload system statistics:
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2,
7 T2.COL3
8 FROM
9 T1,
10 T2
11 WHERE
12 T1.COL1=T2.COL1
13 AND T1.COL1 BETWEEN 1 AND 1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1188283739
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 413K| 3 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 999 | 413K| 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000 | 207K| 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0018298 | 1000 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 207K| 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0018300 | 1000 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T1"."COL1">=1 AND "T1"."COL1"<=1000)
5 - access("T2"."COL1">=1 AND "T2"."COL1"<=1000)
SQL> SELECT
2 T1.COL1,
3 T1.COL2,
4 T1.COL3,
5 T2.COL1,
6 T2.COL2
7 FROM
8 T1,
9 T2
10 WHERE
11 T1.COL1=T2.COL1
12 AND T1.COL1 < 10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3560619641
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 2177K| 17 (65)| 00:00:01 |
|* 1 | HASH JOIN | | 9998 | 2177K| 17 (65)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 9999 | 107K| 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0018300 | 9999 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 9999 | 2070K| 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0018298 | 9999 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL1"="T2"."COL1")
3 - access("T2"."COL1"<10000)
5 - access("T1"."COL1"<10000)
So, the original question likely remains. If the first table is small (the T2 and TT2 test tables) and the driven table (the T1 and TT1 test tables) contains an index on the join column, will Oracle’s optimizer favor nested loops joins over hash joins? That would make a great test question for a certification exam and would appropriately be included in my blog category Quiz – Whose Answer is it Anyway? for those types of questions.
Charles Hooper's Oracle Notes 
Leave a comment