how to see only the execution plan in oracle database

The easiest way :  in sqlplus type : set autot trace exp  and then the desired statement;


If you have a statement with bind variables ( for  example, the one from a procedure/package ):
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                    BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var b1 number;
SQL>  select count(*) from ledger_stat where consolidation_cd = :b1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3847011453

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |     1 |     3 |  7624   (3)| 00:02:18 |        |      |            |
|   1 |  SORT AGGREGATE           |               |     1 |     3 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |               |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000      |     1 |     3 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |               |     1 |     3 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |               |    24M|    70M|  7624   (3)| 00:02:18 |  Q1,00 | PCWC |            |
|*  6 |       INDEX FAST FULL SCAN| I_LEDGER_YEAR |    24M|    70M|  7624   (3)| 00:02:18 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("CONSOLIDATION_CD"=TO_NUMBER(:B1))