What are the OMem and 1Mem columns in the plan output?

The plan output, for example

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR( '' ,NULL,'ALLSTATS  LAST'));

has the columns OMem and 1Mem. What is their meaning? I could not find any reference or documentation about them, just this single web page, claiming (or asking):


OMem:- Estimated optimal mem (PGA) size, to execute the operation in memory.


1Mem:- Estimated size to execute the operation in single pass to disk?

OMem - Estimated amount of memory needed to perform the operation in memory only. This is also called the optimal execution.

1Mem - Estimated amount of memory needed to perform the operation in one pass (write to and read from disk (temp) only once). This is called a one-pass execution.

A multi-pass execution is where the same data is written to and read from disk more than once.

Think about sorting, where the database has to sort large amount data in a small PGA/sort area.

Since you query plan statistics with ALLSTATS LAST, some extra columns:

Used-Mem - The amount of memory actually used for this operation.
Also there is a number in brackets in this column. If the number is 0, then it was an optimal execution, used only memory and no temporary space. If the number is 1, then it was a one-pass execution. If the number is greater than 1, it was a multi-pass execution, and that number is the number of passes.

Used-Tmp - The amount of temporary space used for this operation.

Here is a presentation about the execution passes starting at page 28: http://www.nocoug.org/download/2003-08/how_cbo_works.ppt

StackExchange Database Administrators Q#84012, answer score: 7