In the first execution plan, the sub query was executed for each row in the NL. The number of in-memory sort was very high.

The UNION is not needed in the sub query:

Try this:

Code:
SQL>  SELECT  /*+ ordered */ DISTINCT
  2                  dmc.dmc_fds_id,
  3                  asmc.asm_smv_id,
  4                  asmc.asm_sho_id,
  5                  asmc.asm_smv_smc_id,
  6                asmc.asm_tpr_from,
  7                asmc.asm_tpr_to
  8            FROM  TRAG_NODE_HIE_MKT mnu,
  9        TTAG_DS_MKT_CHARACTERISTIC dmc,
 10        TMAG_ASS_SHOP_MAPPED_CHAR asmc
 11           WHERE  mnu.mnu_level_node_type = 'SHOP'
 12        AND  -mnu.mnu_mah_id = asmc.asm_sho_id
 13        AND  dmc.dmc_fds_id = 1017672  -- pFactory_datascope_id
 14        AND  dmc.dmc_type = 'M'
 15        AND  dmc.dmc_chr_id = asmc.asm_smv_smc_id
 16        AND  dmc.dmc_chr_id IN
 17                    (SELECT mhl_smc_id
 18                       FROM TTAG_MS_HIERARCHY_LEVEL
 19                      WHERE mhl_fds_id=1017672   -- pFactory_datascope_id
 20                        AND mhl_level_type='M'
 21                        AND mhl_mhi_id=mnu_hii_id
 22                      UNION ALL
 23                     SELECT fsh_smc_id
 24                       FROM TRDS_FDS_SOFT_SHOP_CHAR
 25                      WHERE fsh_fds_id=1017672  -- pFactory_datascope_id
 26*                       AND fsh_type='M')
SQL> /