See RAM used in a SQL execution plan
Oracle Tips by Burleson Consulting

Displaying RAM usage for SQL execution plan steps:

The query using v$sql_workarea to see memory usage for each step of an SQL execution plan requires you to get the address of your SQL statement:

select
   address
from
   v$sql
where
   sql_text like ‘%NEW_CUSTOMER%’;

88BB460C

Now that you have the SQL address, just plug it into the SQL below to query v$sql_workarea and see the RAM costs associated with each execution plan step:

select
   operation,
   options,
   object_name                        name,
   trunc(bytes/1024/1024)             "input(MB)",
   trunc(last_memory_used/1024)       last_mem,
   trunc(estimated_optimal_size/1024) opt_mem,
   trunc(estimated_onepass_size/1024) onepass_mem,
   decode(optimal_executions, null, null,
          optimal_executions||'/'||onepass_executions||'/'||
          multipasses_exections)      "O/1/M"
from
   v$sql_plan     p,
   v$sql_workarea w
where
   p.address=w.address(+)
and
   p.hash_value=w.hash_value(+)
and
   p.id=w.operation_id(+)
and
   p.address='88BB460C';

Now, via v$sql_workarea we see the memory usage associated with each execution plan step:

OPERATION OPTIONS NAME input(MB) LAST_MEM OPT_MEM ONEPASS_MEM O/1/M

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

SELECT STATE

SORT GROUP BY 4582 8 16 16 26/0/0

HASH JOIN SEMI 4582 5976 5194 2187 16/0/0

TABLE ACCESS FULL ORDERS 51