PT - Hints /* + */ using Optimizer Goal:

Hints  /* +  */   using Optimizer Goal:

ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='ALL_ROWS' ( THROUGH OUTPUT)

ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='FIRST_ROWS_n' ( RESPONSE TIME )

By Default optimizer mode  is set to ALL_ROWS

SQL> sho parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>

SET LINESIZE 180
SET AUTOTRACE ON ( PLUSTRACE ALREADY ENABLED)



SQL> SELECT /*+ FIRST_ROWS */ /* MYTEST */ ORDER_ID  FROM ORDERS;
  ORDER_ID
----------
    979999
    989999
    999999
    999900
    990000

1352070 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2880579201

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |  1353K|  7929K|  2835   (1)| 00:00:01 |
|   1 |  INDEX FULL SCAN | ORDER_PK |  1353K|  7929K|  2835   (1)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        137  recursive calls
          0  db block gets
      93042  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1352070  rows processed

################################################################################

(Query With out Hints)

SQL>  SELECT ORDER_ID  FROM ORDERS;

  ORDER_ID

----------

    565152

    575152

    585152

    595152

    605152

1352070 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |  1353K|  7929K|   774   (2)| 00:00:01 |        |      |    |

|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR    |          |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | PCWC |    |

|   4 |     INDEX FAST FULL SCAN| ORDER_PK |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | PCWP |    |

-----------------------------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

         65  recursive calls

          0  db block gets

       4533  consistent gets

          0  physical reads

          0  redo size

   25948070  bytes sent via SQL*Net to client

     992115  bytes received via SQL*Net from client

      90139  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

    1352070  rows processed

SQL>

#################################################################################

( Query Check using SQL_TEXT)

SQL> SET LINESIZE 150
SQL> SET SQL_TEXT format A60

SQL>  SELECT OPTIMIZER_MODE, SUBSTR(SQL_TEXT,1,60) SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%MYTEST%ORDERS%';

OPTIMIZER_

----------

SQL_TEXT

------------------------------------------------------------------------------------------------------------------------------------------------------

FIRST_ROWS

EXPLAIN PLAN SET STATEMENT_ID='PLUS80089' FOR SELECT /*+ FIR

ALL_ROWS

 SELECT OPTIMIZER_MODE, SUBSTR(SQL_TEXT,1,60) SQL_TEXT FROM

FIRST_ROWS

SELECT /*+ FIRST_ROWS */ /* MYTEST */ ORDER_ID  FROM ORDERS

Execution Plan

----------------------------------------------------------

Plan hash value: 903671040

--------------------------------------------------------------------------------------

| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                   |     1 |   528 |     0   (0)| 00:00:01 |

|*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   528 |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("KGLNAOBJ" IS NOT NULL AND "KGLNAOBJ" LIKE '%MYTEST%ORDERS%'

              AND "INST_ID"=USERENV('INSTANCE'))

Statistics

----------------------------------------------------------

        394  recursive calls

          0  db block gets

        115  consistent gets

          0  physical reads

          0  redo size

        884  bytes sent via SQL*Net to client

        608  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

          3  rows processed

SQL>

#################################################################################

(Example of query using FIRST_ROWS(10)  We can see cost got reduced ) 

SQL> SELECT /*+ FIRST_ROWS(10) */ /* MYTEST */ ORDER_ID  FROM ORDERS

SQL>

  ORDER_ID

----------

    565152

    575152

    585152

    595152

    605152

1352070 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |    10 |    60 |     2   (0)| 00:00:01 |        |      |    |

|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR    |          |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |    |

|   4 |     INDEX FAST FULL SCAN| ORDER_PK |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |    |

-----------------------------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

         49  recursive calls

          0  db block gets

       4533  consistent gets

          0  physical reads

          0  redo size

   25948070  bytes sent via SQL*Net to client

     992115  bytes received via SQL*Net from client

      90139  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

    1352070  rows processed

#######################################################################
(Example of query using FIRST_ROWS(1000)  We can see cost got reduced ) 

SQL> SELECT /*+ FIRST_ROWS(1000) */ /* MYTEST */ ORDER_ID  FROM ORDERS
SQL>

 ORDER_ID
----------
    565152
    575152
    585152
    595152
    605152

1352070 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1000 |  6000 |     3   (0)| 00:00:01 |        |      |    |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |          |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |    |
|   4 |     INDEX FAST FULL SCAN| ORDER_PK |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |    |
-----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
       4533  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1352070  rows processed

SQL>