Tom,
Can you help me to understand what does "OMem" , "1Mem" and "Used-Mem" in the below plan represents? thanks.
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID g1xpj9fu6n9s0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ch.channel_class, c.cust_city,
t.calendar_quarter_desc, sum(s.amount_sold) sales_amount from sales
s, times t, customers c, channels ch where s.time_id = t.time_id and
s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND
c.cust_state_province = 'Tokyo' AND ch.channel_desc in
('Internet','Catalog') AND t.calendar_quarter_desc IN
('1998-04','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 999844806
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 | 1562 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 2 | 1562 | 1010K| 1010K| 606K (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 14 | 1562 | | | |
| 3 | NESTED LOOPS | | 1 | 5 | 14 | 1552 | | | |
|* 4 | HASH JOIN | | 1 | 5 | 14 | 1539 | 1281K| 1281K| 423K (0)|
|* 5 | HASH JOIN | | 1 | 10 | 14 | 1537 | 1696K| 1696K| 1262K (0)|
|* 6 | TABLE ACCESS FULL | CUSTOMERS | 1 | 10 | 16 | 1457 | | | |
| 7 | PARTITION RANGE SUBQUERY | | 1 | 6857 | 14 | 80 | | | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 6857 | 14 | 26 | | | |
| 9 | BITMAP CONVERSION TO ROWIDS | | 2 | | 14 | 12 | | | |
| 10 | BITMAP AND | | 2 | | 2 | 12 | | | |
|* 11 | BITMAP INDEX SINGLE VALUE | SALES_CUST_IDX | 2 | | 2 | 4 | | | |
| 12 | BITMAP OR | | 2 | | 2 | 8 | | | |
|* 13 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_IDX | 2 | | 0 | 4 | | | |
|* 14 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_IDX | 2 | | 2 | 4 | | | |
|* 15 | TABLE ACCESS FULL | CHANNELS | 1 | 2 | 2 | 2 | | | |
|* 16 | INDEX UNIQUE SCAN | TIMES_PK | 14 | 1 | 14 | 13 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | TIMES | 14 | 1 | 14 | 10 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Those columns map to the PGA workarea estimates/actuals. They should align to what you would see in V$SQL_WORKAREA
SQL> desc v$sql_workarea
Name Null? Type
----------------------------------------------------- -------- --------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
CHILD_NUMBER NUMBER
WORKAREA_ADDRESS RAW(8)
OPERATION_TYPE VARCHAR2(40)
OPERATION_ID NUMBER
POLICY VARCHAR2(10)
ESTIMATED_OPTIMAL_SIZE NUMBER
ESTIMATED_ONEPASS_SIZE NUMBER
LAST_MEMORY_USED NUMBER
LAST_EXECUTION VARCHAR2(10)
LAST_DEGREE NUMBER
TOTAL_EXECUTIONS NUMBER
OPTIMAL_EXECUTIONS NUMBER
ONEPASS_EXECUTIONS NUMBER
MULTIPASSES_EXECUTIONS NUMBER
ACTIVE_TIME NUMBER
MAX_TEMPSEG_SIZE NUMBER
LAST_TEMPSEG_SIZE NUMBER
CON_ID NUMBER
In particular, the following three columns
ESTIMATED_OPTIMAL_SIZE
Estimated size (in bytes) required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions.
ESTIMATED_ONEPASS_SIZE
Estimated size (in bytes) required by this work area to execute the operation in a single pass. Derived from either optimizer statistics or previous executions.
LAST_MEMORY_USED
Memory (in bytes) used by this work area during the last execution of the cursor