memory usage oracle query
You find all available metrics in

V$SQL_MONITOR - disk rate, requests and consistent gets per execution per session - here you also found if parallel execution is degraded
V$SQL_PLAN_MONITOR - disk and memory data per session, execution and plan line
V$ACTIVE_SESSION_HISTORY - 1 s sampled data per session, execution and plan line 
V$PROCESS - current counters for process memory usage (join to session using PADDR = ADDR)

If you are familiar with execution monitor in Enterprise manager or other Oracle monitoring tools, they use exactly same views. You just need to join the data from them and model into an output you want.

There one more view useful for finding out parallel skew - V$PQ_TQSTAT.