Oracle: V$SQL_WORKAREA

v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.

Select statement

The following select statement joins v$sql_workarea to v$session and v$sql_plan.

select
   ses.sid,
   ses.serial#,
   lpad(' ', pln.depth * 2) || pln.operation   pln_operation,
   pln.options                                 pln_options,
   wra.operation_type,
   wra.policy,
   wra.estimated_optimal_size,
   wra.estimated_onepass_size,
   wra.last_memory_used,
   wra.last_execution,
   wra.last_degree,
   wra.total_executions,
   wra.optimal_executions,
   wra.onepass_executions,
   wra.multipasses_executions,
   wra.active_time,
   wra.max_tempseg_size,
   pln.cost,
   pln.cpu_cost,
   pln.io_cost,
   pln.time,
   pln.bytes,
   pln.object_node,
-- pln.object#
   pln.object_owner,
   pln.object_name,
   pln.object_type,
   pln.object_alias,
   pln.search_columns,
   pln.projection,
   pln.access_predicates,
   pln.filter_predicates,
   pln.id,
   pln.parent_id
from
   v$session      ses                                                     join
   v$sql_plan     pln on ses.sql_id           = pln.sql_id       and
                         ses.sql_child_number = pln.child_number     left join
   v$sql_workarea wra on pln.sql_id           = wra.sql_id       and
                         pln.child_number     = wra.child_number and
                         pln.id               = wra.operation_id
where
   ses.sid = sys_context('userenv', 'sid')
-- ses.osuser       = 'Rene'
-- ses.taddr is not null
-- pln.sql_id       = '8sgwz9ataxnt1' and
-- pln.child_number = 0
order by
   pln.id;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/sql/workarea/select.sql

Show different workarea operation types

select
   count(*),
   pln.operation,
   pln.options,
   wka.operation_type
from
   v$sql_plan pln  join v$sql_workarea wka on
      pln.sql_id       = wka.sql_id and
      pln.child_number = wka.child_number and
      pln.id           = wka.operation_id
group by
   pln.operation,
   pln.options,
   wka.operation_type
order by
   wka.operation_type;

This query finds (on the system I am currently oh) the following Workarea operation types

	Plan operations
CONNECT-BY (SORT)	`BUFFER SORT`
GROUP BY (SORT)	`CONNECT BY` (`WITH FILTERING`, `WITH FILTERING (UNIQUE)`)
HASH-JOIN	`HASH JOIN`
IDX MAINTENANCE (SORT)	`DELETE STATEMENT`, `INSERT STATEMENT`
LOAD WRITE BUFFERS	`LOAD AS SELECT`
RANGE TQ COMPUTATION	`PX COORDINATOR`
RESULT CACHE	`RESULT CACHE`
SORT (V1)	(`ORDER BY`)
SORT (v2)	`SORT` (`CREATE INDEX`, `JOIN`, `ORDER BY`, `ORDER BY STOPKEY`)
WINDOW (SORT)	`WINDOW` (`SORT`, `NOSORT`)

Oracle: V$SQL_WORKAREA

Select statement

Show different workarea operation types

See also