Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: September 02, 2015 - 12:08 am UTC

Last updated: September 15, 2015 - 2:18 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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 |       |   |          |
-------------------------------------------------------------------------------------------------------------------------------------

and Connor said...

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



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.