SQL> explain plan for
  2  SELECT
  3     *
  4  FROM
  5     NXMO.PRICE P,
  6     NXMO.UNDERLYING U,
  7     NXMO.DOMAINS D
  8  WHERE
  9     P.UNDERLYING_ID = U.UNDERLYING_ID AND
 10     U.OCC_ELIGIBLE = 'Y' AND
 11     P.PRICE_ID = (SELECT
 12                                             MAX(PRICE_ID)
 13                                     FROM
 14                                             NXMO.PRICE
 15                                     WHERE
 16                                             UNDERLYING_ID = P.UNDERLYING_ID
 17                                             AND CURRENCY_CODE  = 'USD'
 18                                             AND  IS_DELETED = 'N'
 19     ) AND U.SECURITY_TYPE = D.DOMAIN_CODE
 20       AND D.DOMAIN_VALUE = 'STOCK'
 21               AND D.DOMAIN_TYPE = 'SECURITY_TYPES'
 22       AND U.IS_DELETED = 'N'
 23  ORDER BY
 24     P.UNDERLYING_ID;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 110236611

-------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |     1 |   215 |  6214   (3)| 00:01:15 |
|*  1 |  FILTER                           |             |       |       |            |          |
|   2 |   SORT GROUP BY                   |             |     1 |   215 |  6214   (3)| 00:01:15 |
|*  3 |    HASH JOIN                      |             |  9328 |  1958K|  6213   (2)| 00:01:15 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | PRICE       |    27 |  1242 |    29   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |             |   559 |   105K|  1097   (1)| 00:00:14 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
|   6 |       NESTED LOOPS                |             |    21 |  3087 |   644   (2)| 00:00:08 |
|   7 |        TABLE ACCESS BY INDEX ROWID| DOMAINS     |     1 |    32 |     2   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | DOMAINS_UK1 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL          | UNDERLYING  |    21 |  2415 |   642   (2)| 00:00:08 |
|* 10 |       INDEX RANGE SCAN            | PRICE_UK    |    27 |       |     2   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS FULL             | PRICE       |  1966K|    41M|  5102   (3)| 00:01:02 |
-------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
   1 - filter("P"."PRICE_ID"=MAX("PRICE_ID"))
   3 - access("UNDERLYING_ID"="P"."UNDERLYING_ID")
   8 - access("D"."DOMAIN_VALUE"='STOCK' AND "D"."DOMAIN_TYPE"='SECURITY_TYPES')
   9 - filter("U"."OCC_ELIGIBLE"='Y' AND "U"."IS_DELETED"='N' AND
              "U"."SECURITY_TYPE"="D"."DOMAIN_CODE")
  10 - access("P"."UNDERLYING_ID"="U"."UNDERLYING_ID")
  11 - filter("CURRENCY_CODE"='USD' AND "IS_DELETED"='N')

29 rows selected.

SQL>