SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3719259950
--------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     1 |    14
|   1 |  TABLE ACCESS BY INDEX ROWID| DEAL_STRUCTURES            |     1 |    14
|*  2 |   INDEX RANGE SCAN          | DESR_DEIT_REFERS_SUCC_FK_I |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DESR_DEIT_ID_REFERS_SUCC"=20002232)
       filter("DESR_DEIT_ID_REFERS_SUCC"<>4243434343)

15 rows selected
---------------------------------------------------------------------------------------------------------------

SELECT nvl(ttdi_short_rom_code,tmac_short_rom_code) short_rom_code
            INTO l_short_rom_code
            FROM deal_items,
                 tested_dies,
                 tested_marked_ass_chips
            WHERE deit_id = l_deit_id
            AND deit_ttdi_id = ttdi_id(+)
            AND deit_tmac_id = tmac_id(+)

SQL> explain plan for SELECT nvl(ttdi_short_rom_code,0) short_rom_code
  2                          FROM deal_items,
  3                   tested_dies,
  4                   tested_marked_ass_chips
  5              WHERE deit_id = '1111'
  6              AND deit_ttdi_id = ttdi_id(+)
  7              AND deit_tmac_id = tmac_id(+);

Explained

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4158858003
--------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    28 |     3   (0
|   1 |  NESTED LOOPS OUTER           |             |     1 |    28 |     3   (0
|   2 |   NESTED LOOPS OUTER          |             |     1 |    18 |     2   (0
|   3 |    TABLE ACCESS BY INDEX ROWID| DEAL_ITEMS  |     1 |    11 |     1   (0
|*  4 |     INDEX UNIQUE SCAN         | DEIT_PK     |     1 |       |     1   (0
|*  5 |    INDEX UNIQUE SCAN          | TMAC_PK     | 58898 |   402K|     1   (0
|   6 |   TABLE ACCESS BY INDEX ROWID | TESTED_DIES | 37189 |   363K|     1   (0
|*  7 |    INDEX UNIQUE SCAN          | TTDI_PK     |     1 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEIT_ID"=1111)
   5 - access("DEIT_TMAC_ID"="TMAC_ID"(+))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - access("DEIT_TTDI_ID"="TTDI_ID"(+))

21 rows selected

request to help me in tuning below qury.

select * from table_name order by col1;

Its taking long time and having app 1 million rows.