True or False – Optimizer Mode and Tuning Goals

13 05 2010

May 13, 2010

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

Continuing the true or false blog article series with more questions that seem to have conflicting answers. Today’s quiz is on the topic of the optimizer mode and tuning goals, inspired by a recent thread that I found in a discussion forum. A Google search found a couple of related articles.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1. As of Oracle 10g R1, there are three methods for joining tables or other row sources: star transformation join, nested loops join, and hash join.

2. SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.

3. Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints.

4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.

5. An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:

    ALTER SESSION SET OPTIMIZER GOAL = RULE;

6. The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.

7. A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that than a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.

8. The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.

9. The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.

10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins. Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.

11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.

12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.

13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.

14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.

—

I fear that this blog series might be falsely inflating the Google ranking of the Oracle documentation library due to the number of times the documentation library has been referenced - sorry Google users.

Actions

Information

Date : May 13, 2010
Categories : Faulty Quotes, General Administration, Quiz - Whose Answer is it Anyway?

5 responses

13 05 2010

Narendra (06:10:35) :

1. As of Oracle 10g R1, there are three methods for joining tables or other row sources: star transformation join, nested loops join, and hash join.
FALSE. They are sort merge join, nested loops join, and hash join
2. SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.
TRUE. Smallest result set means less number of iterations in case of NESTED LOOP join; small hash table to probe in case of HASH JOIN and small data to sort in case of SORT MERGE join
3. Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints.
FALSE for SQL Profiles and TRUE for SQL Access Advisor
4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.
FALSE. It offers RULE, FIRST_ROWS and ALL_ROWS optimizer modes.
5. An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:
ALTER SESSION SET OPTIMIZER GOAL = RULE;
FALSE. Do I even need to justify the answer?
6. The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.
TRUE
7. A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.
FALSE. “automatically less efficient” is an ambiguous and relative term. What if two SELECT statements process different result sets?
8. The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.
TRUE.
9. The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.
Not sure but I guess TRUE.
10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins. Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.
FALSE. By using System statistics, it is no longer “recommended” to set value for the OPTIMIZER_INDEX_CACHING parameter.
11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.
No idea. (Yes, I know I can read the linked articles and find out but I find it like appearing for examination with text books.
12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.
FALSE. With the RULE based optimizer, the driving table is the first table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table can be any table listed in the FROM clause of the SQL statement.
13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.
FALSE. The Cost-Based optimizer does not perform full table scans by default, irrespective of how many tables are listed in the FROM clause of a SQL statement
14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.
FALSE. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the subsequent SQL statements will be executed to return first 10 rows as quickly as possible.

13 05 2010

Charles Hooper (06:47:43) :

Narendra,

Nice effort for not looking at any of the supplied reading material.

There is something else wrong with question #7 (other than me typing “that” rather than “than“). Need help?

There is something else wrong with question #5… someone is bound to execute that question.

13 05 2010

Charles Hooper (12:38:58) :

Some help for question #5:

SQL> ALTER SESSION SET OPTIMIZER GOAL = RULE;
ALTER SESSION SET OPTIMIZER GOAL = RULE
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
 
 
SQL> ALTER SESSION SET OPTIMIZER_GOAL = RULE;
ALTER SESSION SET OPTIMIZER_GOAL = RULE
                                 *
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete
 
 
SQL> ALTER SESSION SET OPTIMIZER_GOAL = 'RULE';
ALTER SESSION SET OPTIMIZER_GOAL = 'RULE'
                                 *
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete

From the 10g R1 documentation:
“ORA-01986: OPTIMIZER_GOAL is obsolete Cause: An obsolete parameter, OPTIMIZER_GOAL, was referenced.
Action: Use the OPTIMIZER_MODE parameter.”

Not only is the SQL statement missing the _ character between OPTIMIZER and GOAL, but it is also attempting to use a parameter that has been obsolete since Oracle 10g R1 was introduced. I wonder where that SQL statement was found?

On second thought, maybe that is what Narendra was trying to tell me.

13 05 2010

Charles Hooper (14:25:22) :

Time to take out a somewhat easy answer:
#6 “The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics” – FALSE. If memory serves me correctly, CHOOSE was the default optimizer mode in Oracle 9i, but the 10g R1documentation (article link 10 above) states the following:
“CHOOSE – This parameter value has been desupported.
RULE – This parameter value has been desupported.”

I suspect that Oracle Database would not ship with a default parameter that is identified as desupported (on a side-note, CHOOSE still works) in the documentation. The 10g R1 default for OPTIMIZER_MODE is ALL_ROWS.

—
The second half of the question, I believe, is correct even with Oracle Database 11.2.0.1 when the OPTIMIZER_MODE is set to CHOOSE.

17 05 2010

Charles Hooper (19:29:24) :

Time to answer another one of the questions. #2 “SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.” False. Why? What if the smallest two tables are completely unrelated, and may only be joined using a Cartesian join? Would Oracle Database ever do that – yes. Is it sometimes more efficient and sometimes not – yes.

Demonstration:
The four tables:

CREATE TABLE T1 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  C4 VARCHAR2(500),
  PRIMARY KEY (C1));
 
INSERT INTO T1
SELECT
  ROWNUM,
  MOD(ROWNUM,100),
  RPAD('A',100,'A'),
  RPAD('A',500,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000),
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000);
 
CREATE TABLE T2 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  C4 VARCHAR2(500),
  PRIMARY KEY (C1));
 
INSERT INTO T2
SELECT
  ROWNUM,
  MOD(ROWNUM,100),
  RPAD('A',100,'A'),
  RPAD('A',500,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000),
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000);
 
CREATE TABLE T3 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=2;
 
CREATE TABLE T4 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=2;
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)

The script:

SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 1000
ALTER SESSION SET STATISTICS_LEVEL='ALL';
SET TIMING ON
 
SPOOL SMALLEST_TEST3.TXT
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT
  T1.C1,
  T2.C1,
  T1.C3,1,10 T1_C3,
  T2.C3 T2_C3
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2 IN (
    SELECT
      C1
    FROM
      T3)  
  AND T2.C2 IN (
    SELECT
      C1
    FROM
      T4);
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT /*+ LEADING(T1, T3, T4) */
  T1.C1,
  T2.C1,
  T1.C3,1,10 T1_C3,
  T2.C3 T2_C3
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2 IN (
    SELECT
      C1
    FROM
      T3)  
  AND T2.C2 IN (
    SELECT
      C1
    FROM
      T4);
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT /*+ LEADING(T1, T3, T2) */
  T1.C1,
  T2.C1,
  T1.C3,1,10 T1_C3,
  T2.C3 T2_C3
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2 IN (
    SELECT
      C1
    FROM
      T3)  
  AND T2.C2 IN (
    SELECT
      C1
    FROM
      T4);
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT /*+ LEADING(T3, T4) */
  T1.C1,
  T2.C1,
  T1.C3,1,10 T1_C3,
  T2.C3 T2_C3
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2 IN (
    SELECT
      C1
    FROM
      T3)  
  AND T2.C2 IN (
    SELECT
      C1
    FROM
      T4);
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ LEADING(T4, T3, T2, T1) USE_HASH(T1) USE_HASH(T2) */
  T1.C1,
  T2.C1,
  T1.C3,1,10 T1_C3,
  T2.C3 T2_C3
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2 IN (
    SELECT
      C1
    FROM
      T3)  
  AND T2.C2 IN (
    SELECT
      C1
    FROM
      T4);
 
SPOOL OFF

In the above, tables T3 and T4 have the smallest resultsets. The output of the above script:

 
SQL> SELECT
  2    T1.C1,
  3    T2.C1,
  4    T1.C3,1,10 T1_C3,
  5    T2.C3 T2_C3
  6  FROM
  7    T1,
  8    T2
  9  WHERE
 10    T1.C1=T2.C1
 11    AND T1.C2 IN (
 12      SELECT
 13        C1
 14      FROM
 15        T3)
 16    AND T2.C2 IN (
 17      SELECT
 18        C1
 19      FROM
 20        T4);
 
20000 rows selected.
 
Elapsed: 00:00:21.40
 
Statistics
----------------------------------------------------------
          1  recursive calls                              
          0  db block gets                                
     111034  consistent gets                              
     101906  physical reads                               
          0  redo size                                    
     304141  bytes sent via SQL*Net to client             
        709  bytes received via SQL*Net from client       
         21  SQL*Net roundtrips to/from client            
          1  sorts (memory)                               
          0  sorts (disk)                                 
      20000  rows processed

SQL> SELECT /*+ LEADING(T1, T3, T4) */
  2    T1.C1,
  3    T2.C1,
  4    T1.C3,1,10 T1_C3,
  5    T2.C3 T2_C3
  6  FROM
  7    T1,
  8    T2
  9  WHERE
 10    T1.C1=T2.C1
 11    AND T1.C2 IN (
 12      SELECT
 13        C1
 14      FROM
 15        T3)
 16    AND T2.C2 IN (
 17      SELECT
 18        C1
 19      FROM
 20        T4);
 
20000 rows selected.
 
Elapsed: 00:00:14.02
 
Statistics
----------------------------------------------------------
          1  recursive calls                              
          0  db block gets                                
     181958  consistent gets                              
     181906  physical reads                               
          0  redo size                                    
     304141  bytes sent via SQL*Net to client             
        709  bytes received via SQL*Net from client       
         21  SQL*Net roundtrips to/from client            
          2  sorts (memory)                               
          0  sorts (disk)                                 
      20000  rows processed

SQL> SELECT /*+ LEADING(T1, T3, T2) */
  2    T1.C1,
  3    T2.C1,
  4    T1.C3,1,10 T1_C3,
  5    T2.C3 T2_C3
  6  FROM
  7    T1,
  8    T2
  9  WHERE
 10    T1.C1=T2.C1
 11    AND T1.C2 IN (
 12      SELECT
 13        C1
 14      FROM
 15        T3)
 16    AND T2.C2 IN (
 17      SELECT
 18        C1
 19      FROM
 20        T4);
 
20000 rows selected.
 
Elapsed: 00:00:41.55
 
Statistics
----------------------------------------------------------
          1  recursive calls                              
          0  db block gets                                
     151012  consistent gets                              
     103743  physical reads                               
          0  redo size                                    
     304141  bytes sent via SQL*Net to client             
        709  bytes received via SQL*Net from client       
         21  SQL*Net roundtrips to/from client            
          0  sorts (memory)                               
          0  sorts (disk)                                 
      20000  rows processed

SQL> SELECT /*+ LEADING(T3, T4) */
  2    T1.C1,
  3    T2.C1,
  4    T1.C3,1,10 T1_C3,
  5    T2.C3 T2_C3
  6  FROM
  7    T1,
  8    T2
  9  WHERE
 10    T1.C1=T2.C1
 11    AND T1.C2 IN (
 12      SELECT
 13        C1
 14      FROM
 15        T3)
 16    AND T2.C2 IN (
 17      SELECT
 18        C1
 19      FROM
 20        T4);

20000 rows selected.
 
Elapsed: 00:00:20.31
 
Statistics
----------------------------------------------------------
          1  recursive calls                              
          0  db block gets                                
     131077  consistent gets                              
     101906  physical reads                               
          0  redo size                                    
     304141  bytes sent via SQL*Net to client             
        709  bytes received via SQL*Net from client       
         21  SQL*Net roundtrips to/from client            
          3  sorts (memory)                               
          0  sorts (disk)                                 
      20000  rows processed

SQL> SELECT /*+ LEADING(T4, T3, T2, T1) USE_HASH(T1) USE_HASH(T2) */
  2    T1.C1,
  3    T2.C1,
  4    T1.C3,1,10 T1_C3,
  5    T2.C3 T2_C3
  6  FROM
  7    T1,
  8    T2
  9  WHERE
 10    T1.C1=T2.C1
 11    AND T1.C2 IN (
 12      SELECT
 13        C1
 14      FROM
 15        T3)
 16    AND T2.C2 IN (
 17      SELECT
 18        C1
 19      FROM
 20        T4);

20000 rows selected.
 
Elapsed: 00:00:14.30
 
Statistics
----------------------------------------------------------
          1  recursive calls                              
          0  db block gets                                
     181958  consistent gets                              
     181906  physical reads                               
          0  redo size                                    
     304141  bytes sent via SQL*Net to client             
        709  bytes received via SQL*Net from client       
         21  SQL*Net roundtrips to/from client            
          3  sorts (memory)                               
          0  sorts (disk)                                 
      20000  rows processed

Now, let’s take a look at the execution plans:

SET AUTOTRACE OFF
SET PAGESIZE 1000

SELECT /*+ LEADING(S) */
  T.PLAN_TABLE_OUTPUT
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE 'SELECT%'
    AND SQL_TEXT LIKE '%T1.C1=T2.C1%'
    AND SQL_TEXT LIKE '%T4)') S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST +COST')) T;

SQL_ID  g3s82hra84bgv, child number 0                                                                                                       
-------------------------------------                                                                                                       
SELECT   T1.C1,   T2.C1,   T1.C3,1,10 T1_C3,   T2.C3 T2_C3 FROM   T1,                                                                       
T2 WHERE   T1.C1=T2.C1   AND T1.C2 IN (     SELECT       C1     FROM                                                                        
   T3)   AND T2.C2 IN (     SELECT       C1     FROM       T4)                                                                              
             
Plan hash value: 1754840566                                                                                                                 
             
-------------------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                      | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT               |           |      1 |        | 40739 (100)|  20000 |00:00:21.34 |     111K|    101K|       |       |          |  
|*  1 |  HASH JOIN RIGHT SEMI          |           |      1 |    202 | 40739   (1)|  20000 |00:00:21.34 |     111K|    101K|  1452K|  1452K|  809K (0)|  
|   2 |   TABLE ACCESS FULL            | T3        |      1 |      2 |     3   (0)|      2 |00:00:00.01 |       3 |      2 |       |       |          |  
|*  3 |   HASH JOIN                    |           |      1 |  20000 | 40735   (1)|  20000 |00:00:21.34 |     111K|    101K|  3172K|  1015K| 4269K (0)|  
|   4 |    NESTED LOOPS                |           |      1 |        |            |  20000 |00:00:14.21 |   20048 |  10953 |       |       |          |  
|   5 |     NESTED LOOPS               |           |      1 |  20000 | 10026   (1)|  20000 |00:00:00.48 |      48 |     44 |       |       |          |  
|   6 |      SORT UNIQUE               |           |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|  
|   7 |       TABLE ACCESS FULL        | T4        |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |  
|*  8 |      INDEX RANGE SCAN          | IND_T2_C2 |      2 |  10000 |    20   (0)|  20000 |00:00:00.45 |      45 |     42 |       |       |          |  
|   9 |     TABLE ACCESS BY INDEX ROWID| T2        |  20000 |  10000 | 10022   (1)|  20000 |00:00:13.73 |   20000 |  10909 |       |       |          |  
|  10 |    TABLE ACCESS FULL           | T1        |      1 |   1000K| 24864   (1)|   1000K|00:00:06.03 |   90983 |  90951 |       |       |          |  
-------------------------------------------------------------------------------------------------------------------------------------------------------  
              
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   1 - access("T1"."C2"="C1")                                                                                                               
   3 - access("T1"."C1"="T2"."C1")                                                                                                          
   8 - access("T2"."C2"="C1")

SQL_ID  4mxrrjfw67bq8, child number 0                                                                                                       
-------------------------------------                                                                                                       
SELECT /*+ LEADING(T1, T3, T4) */   T1.C1,   T2.C1,   T1.C3,1,10 T1_C3,                                                                     
  T2.C3 T2_C3 FROM   T1,   T2 WHERE   T1.C1=T2.C1   AND T1.C2 IN (                                                                          
SELECT       C1     FROM       T3)   AND T2.C2 IN (     SELECT       C1                                                                     
    FROM       T4)                                                                                                                          
             
Plan hash value: 3921125646                                                                                                                 
             
-----------------------------------------------------------------------------------------------------------------------------------------   
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |   
-----------------------------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT      |      |      1 |        | 72260 (100)|  20000 |00:00:13.93 |     181K|    181K|       |       |          |   
|*  1 |  HASH JOIN            |      |      1 |    202 | 72260   (1)|  20000 |00:00:13.93 |     181K|    181K|  6445K|  2023K| 6946K (0)|   
|   2 |   MERGE JOIN CARTESIAN|      |      1 |  20202 | 41547   (1)|  40000 |00:00:06.80 |   90975 |  90955 |       |       |          |   
|*  3 |    HASH JOIN SEMI     |      |      1 |  10101 | 30593   (1)|  20000 |00:00:06.77 |   90972 |  90953 |   134M|  8091K|  186M (0)|   
|   4 |     TABLE ACCESS FULL | T1   |      1 |   1000K| 24864   (1)|   1000K|00:00:06.00 |   90969 |  90951 |       |       |          |   
|   5 |     TABLE ACCESS FULL | T3   |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |   
|   6 |    BUFFER SORT        |      |  20000 |      2 | 41544   (1)|  40000 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|   
|   7 |     SORT UNIQUE       |      |      1 |      2 |     1   (0)|      2 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|   
|   8 |      TABLE ACCESS FULL| T4   |      1 |      2 |     1   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |   
|   9 |   TABLE ACCESS FULL   | T2   |      1 |   1000K| 24864   (1)|   1000K|00:00:06.03 |   90983 |  90951 |       |       |          |   
-----------------------------------------------------------------------------------------------------------------------------------------   
             
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   1 - access("T1"."C1"="T2"."C1" AND "T2"."C2"="C1")                                                                                       
   3 - access("T1"."C2"="C1")

SQL_ID  571d483mam30r, child number 0                                                                                                       
-------------------------------------                                                                                                       
SELECT /*+ LEADING(T1, T3, T2) */   T1.C1,   T2.C1,   T1.C3,1,10 T1_C3,                                                                     
  T2.C3 T2_C3 FROM   T1,   T2 WHERE   T1.C1=T2.C1   AND T1.C2 IN (                                                                          
SELECT       C1     FROM       T3)   AND T2.C2 IN (     SELECT       C1                                                                     
    FROM       T4)                                                                                                                          
             
Plan hash value: 512342661                                                                                                                  
             
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        | 50804 (100)|  20000 |00:00:41.46 |     151K|    103K|       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI         |              |      1 |    102 | 50804   (1)|  20000 |00:00:41.46 |     151K|    103K|  1452K|  1452K|  809K (0)|
|   2 |   TABLE ACCESS FULL           | T4           |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |
|   3 |   NESTED LOOPS                |              |      1 |        |            |  20000 |00:00:41.43 |     151K|    103K|       |       |          |
|   4 |    NESTED LOOPS               |              |      1 |  10101 | 50800   (1)|  20000 |00:00:09.17 |     131K|  92832 |       |       |          |
|*  5 |     HASH JOIN SEMI            |              |      1 |  10101 | 30593   (1)|  20000 |00:00:06.93 |   90973 |  90953 |   134M|  8091K|  186M (0)|
|   6 |      TABLE ACCESS FULL        | T1           |      1 |   1000K| 24864   (1)|   1000K|00:00:06.00 |   90969 |  90951 |       |       |          |
|   7 |      TABLE ACCESS FULL        | T3           |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       4 |      2 |       |       |          |             
|*  8 |     INDEX UNIQUE SCAN         | SYS_C0016529 |  20000 |      1 |     1   (0)|  20000 |00:00:02.25 |   40036 |   1879 |       |       |          |             
|   9 |    TABLE ACCESS BY INDEX ROWID| T2           |  20000 |      1 |     2   (0)|  20000 |00:00:32.26 |   20000 |  10909 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
              
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   1 - access("T2"."C2"="C1")                                                                                                               
   5 - access("T1"."C2"="C1")                                                                                                               
   8 - access("T1"."C1"="T2"."C1")

SQL_ID  7k4pq59uh51xc, child number 0                                                                                                       
-------------------------------------                                                                                                       
SELECT /*+ LEADING(T3, T4) */   T1.C1,   T2.C1,   T1.C3,1,10 T1_C3,                                                                         
T2.C3 T2_C3 FROM   T1,   T2 WHERE   T1.C1=T2.C1   AND T1.C2 IN (                                                                            
SELECT       C1     FROM       T3)   AND T2.C2 IN (     SELECT       C1                                                                     
    FROM       T4)                                                                                                                          
 
Plan hash value: 2581882832                                                                                                                 
 
------------------------------------------------------------------------------------------------------------------------------------------------------   
| Id  | Operation                     | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |   
------------------------------------------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT              |           |      1 |        | 50764 (100)|  20000 |00:00:20.24 |     131K|    101K|       |       |          |   
|*  1 |  HASH JOIN                    |           |      1 |    200 | 50764   (1)|  20000 |00:00:20.24 |     131K|    101K|  6445K|  2023K| 6947K (0)|   
|   2 |   NESTED LOOPS                |           |      1 |        |            |  40000 |00:00:13.58 |   40094 |  10955 |       |       |          |   
|   3 |    NESTED LOOPS               |           |      1 |  20000 | 20052   (1)|  40000 |00:00:00.31 |      94 |     46 |       |       |          |   
|   4 |     MERGE JOIN CARTESIAN      |           |      1 |      2 |     7  (15)|      4 |00:00:00.03 |       6 |      4 |       |       |          |   
|   5 |      SORT UNIQUE              |           |      1 |      2 |     3   (0)|      2 |00:00:00.01 |       3 |      2 |  2048 |  2048 | 2048  (0)|   
|   6 |       TABLE ACCESS FULL       | T3        |      1 |      2 |     3   (0)|      2 |00:00:00.01 |       3 |      2 |       |       |          |   
|   7 |      BUFFER SORT              |           |      2 |      2 |            |      4 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|   
|   8 |       SORT UNIQUE             |           |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|   
|   9 |        TABLE ACCESS FULL      | T4        |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |   
|* 10 |     INDEX RANGE SCAN          | IND_T2_C2 |      4 |  10000 |    20   (0)|  40000 |00:00:00.28 |      88 |     42 |       |       |          |   
|  11 |    TABLE ACCESS BY INDEX ROWID| T2        |  40000 |  10000 | 10022   (1)|  40000 |00:00:13.29 |   40000 |  10909 |       |       |          |   
|  12 |   TABLE ACCESS FULL           | T1        |      1 |   1000K| 24864   (1)|   1000K|00:00:06.03 |   90983 |  90951 |       |       |          |   
------------------------------------------------------------------------------------------------------------------------------------------------------   
             
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="C1")                                                                                       
  10 - access("T2"."C2"="C1")

SQL_ID  57kwjnjfuwm9x, child number 0                                                                                                       
-------------------------------------                                                                                                       
SELECT /*+ LEADING(T4, T3, T2, T1) USE_HASH(T1) USE_HASH(T2) */                                                                             
T1.C1,   T2.C1,   T1.C3,1,10 T1_C3,   T2.C3 T2_C3 FROM   T1,   T2 WHERE                                                                     
  T1.C1=T2.C1   AND T1.C2 IN (     SELECT       C1     FROM       T3)                                                                       
AND T2.C2 IN (     SELECT       C1     FROM       T4)                                                                                       
             
Plan hash value: 348785823                                                                                                                  
             
------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation              | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |  
------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT       |      |      1 |        | 55706 (100)|  20000 |00:00:14.24 |     181K|    181K|       |       |          |  
|*  1 |  HASH JOIN             |      |      1 |    400 | 55706   (1)|  20000 |00:00:14.24 |     181K|    181K|  6445K|  2023K| 8069K (0)|  
|*  2 |   HASH JOIN            |      |      1 |  40000 | 24874   (1)|  40000 |00:00:06.81 |   90975 |  90955 |  1306K|  1306K|  497K (0)|  
|   3 |    MERGE JOIN CARTESIAN|      |      1 |      4 |     7  (15)|      4 |00:00:00.06 |       6 |      4 |       |       |          |  
|   4 |     SORT UNIQUE        |      |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|  
|   5 |      TABLE ACCESS FULL | T4   |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |  
|   6 |     BUFFER SORT        |      |      2 |      2 |     4  (25)|      4 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|  
|   7 |      SORT UNIQUE       |      |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |  2048 |  2048 | 2048  (0)|  
|   8 |       TABLE ACCESS FULL| T3   |      1 |      2 |     3   (0)|      2 |00:00:00.03 |       3 |      2 |       |       |          |  
|   9 |    TABLE ACCESS FULL   | T2   |      1 |   1000K| 24864   (1)|   1000K|00:00:06.03 |   90969 |  90951 |       |       |          |  
|  10 |   TABLE ACCESS FULL    | T1   |      1 |   1000K| 24864   (1)|   1000K|00:00:07.03 |   90983 |  90951 |       |       |          |  
------------------------------------------------------------------------------------------------------------------------------------------  
             
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="C1")

The above test was performed on Oracle Database 11.1.0.7. The hint LEADING(T1, T3, T4) returned the fastest execution time, and T1 is definitely not smaller than T3.

The final hinted execution plan was necessary to produce the same execution plan returned on another server when “T1.C2=T2.C2″ was specified in the SQL statement, rather than “T1.C1=T2.C1″.

The query optimizer will typically try to start with the two smallest row sources, but that is not always the case.

	Timur Born on Unexpected Timer Resolution, U…
	Charles Hooper on Unexpected Timer Resolution, U…
	Timur Born on Unexpected Timer Resolution, U…
	Charles Hooper on Unexpected Timer Resolution, U…
	Timur Born on Unexpected Timer Resolution, U…
	Natasha on SQL – Filling in Gaps in the S…
	Latest data Industry… on Grouping Data Sets by Week Num…
	Latest data Industry… on Grouping Data Sets by Week Num…
	Latest Data News, Or… on Unexpected Timer Resolution, U…
	Charles Hooper on SESSIONS Derived Value Formula…
	PC on SESSIONS Derived Value Formula…
	Charles Hooper on Value of Improving One’s…
	JMA on Value of Improving One’s…
	Charles Hooper on Value of Improving One’s…
	JMA on Value of Improving One’s…

Charles Hooper's Oracle Notes