SQL Tuning I- get explain plan

SET AUTOTRACE OFF
No AUTOTRACE report is generated. This is the default.

SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.

SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.

SET AUTOTRACE ON
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

SET AUTOTRACE TRACEONLY
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

SET AUTOTRACE ON EXPLAIN ERROR:

ORA-01039: insufficient privileges on underlying objects of the view

GRANT PLUSTRACE TO <USER>;

Example for SET AUTOTRACE ON EXPLAIN output

Execution Plan

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

Plan hash value: 2393427686

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

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

| Id  | Operation                | Name                 | Rows  | Bytes | Cost (

%CPU)| Time     |

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

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

|   0 | SELECT STATEMENT         |                      |   436K|    77M|   309K

  (1)| 00:00:13 |

|   1 |  HASH GROUP BY           |                      |   436K|    77M|   309K

  (1)| 00:00:13 |

|*  2 |   HASH JOIN              |                      |   436K|    77M|   309K

  (1)| 00:00:13 |

|   3 |    TABLE ACCESS FULL     | IPN_PROFILES         |   713 | 12121 |     6

  (0)| 00:00:01 |

|*  4 |    HASH JOIN             |                      |   436K|    70M|   309K

  (1)| 00:00:13 |

|   5 |     INDEX FULL SCAN      | SGA_OUT_COMM_PK      |     6 |    18 |     1

  (0)| 00:00:01 |

|*  6 |     HASH JOIN RIGHT OUTER|                      |   436K|    69M|   309K

  (1)| 00:00:13 |

|   7 |      TABLE ACCESS FULL   | SGA_SUBSCRIBER_SFI   |     2 |   106 |     3

  (0)| 00:00:01 |

|*  8 |      HASH JOIN           |                      |   436K|    47M|   309K

  (1)| 00:00:13 |

|*  9 |       INDEX SKIP SCAN    | SHM_ACTIVE_SITE_PK   |     4 |    28 |     1

  (0)| 00:00:01 |

|* 10 |       TABLE ACCESS FULL  | SGA_W_IPN_SUBSCRIBER |   498K|    50M|   309K

  (1)| 00:00:13 |

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

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

Predicate Information (identified by operation id):

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

   2 - access("S"."IPN_CURR_IPN_PROFILE_ID"="PROF"."PROFILE_ID")

   4 - access("S"."IPN_CURRENT_COMMUNITY"="COMM"."COMMUNITY_ID")

   6 - access("S"."IMSI"="SSSA"."IMSI"(+) AND

              "S"."AFFILIATE_ID"="SSSA"."AFFILIATE_ID"(+))

   8 - access("S"."AFFILIATE_ID"="AFFILIATE_ID")

   9 - access("VNFC_ID"=32)

       filter("VNFC_ID"=32)

  10 - filter("S"."CURRENT_NETWORK_ID"<>(-999))