Oracle Execution Plan -- Statistics

Consider the follow query against SH schema.

select /*MY5*/ cust_id, sum(amount_sold) from sales where prod_id=:prod_id group by cust_id;

With DBMS_XPLAN.Display_cursor, user is able to retrieve and see execution plan.

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%/*MY5*/%';

Plan hash value: 2178334326


----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |       |       |   388 (100)|          |       |       |
|   1 |  HASH GROUP BY                      |                |  4051 | 56714 |   388   (1)| 00:00:05 |       |       |
|   2 |   PARTITION RANGE ALL               |                |  6002 | 84028 |   387   (0)| 00:00:05 |     1 |    28 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |  6002 | 84028 |   387   (0)| 00:00:05 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   5 - access("PROD_ID"=:PROD_ID)

This reveals quite a bit of information, however, what if user would like to know how much memory allocation during the course of this query, how many rows Oracle visited, etc... DBMS_XPLAN.display_cursor provide a way to get these information.

More information from execution plan

alter session set statistics_level=ALL;            

Default value is 'TYPICAL'. In addition to statistics collected at 'TYPICAL' level, 'ALL' instructs Oracle to gather timed OS statistics and plan execution statistics.

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS')) t WHERE sql_text LIKE '%/*MY5*/%';

ALLSTATS: 'IOSTATS MEMSTATS', shows both memory and IO statistics. In order for the memory statistics to be shown, the PGA management should be enabled. That is, PAG_AGGREGATE_TARGET=. The statistics are shown for all executions of the cursor.
To view the statistics of last execution, use 'TYPICAL ALLSTATS LAST'.

Interpreting statistics

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS LAST')) t WHERE sql_text LIKE '%/*MY5*/%';

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |       |   388 (100)|          |       |       |   2492 |00:00:00.26 |     100 |       |       |          |
|   1 |  HASH GROUP BY                      |                |      1 |   4051 | 56714 |   388   (1)| 00:00:05 |       |       |   2492 |00:00:00.26 |     100 |   934K|   934K| 1356K (0)|
|   2 |   PARTITION RANGE ALL               |                |      1 |   6002 | 84028 |   387   (0)| 00:00:05 |     1 |    28 |   6002 |00:00:00.20 |     100 |       |       |          |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |     28 |   6002 | 84028 |   387   (0)| 00:00:05 |     1 |    28 |   6002 |00:00:00.12 |     100 |       |       |          |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |     28 |        |       |            |          |       |       |   6002 |00:00:00.02 |      44 |       |       |          |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_PROD_BIX |     28 |        |       |            |          |     1 |    28 |     16 |00:00:00.01 |      44 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Starts: 
E-Rows: Estimated Number of Rows. "Rows" column from the output of "explain plan" command.
E-Bytes: Estimated Bytes
E-Time: Estimated Time
A-Rows: Actual Rows
A-Time: Actual Time
Buffers: Logical Reads. consistent gets + current gets
Reads: Physical Reads. Only show up when it has a non-zero value.
Writes: Number of writes to temp space.
0Mem: Estimated_Optimal_Size of v$sql_workarea. Esimated size in KB to perform the operation totally in memory (optimal execution). This is the ideal size.
1Mem: Esimated_OnePass_size of v$sql_workarea. Estimated size in KB to execute the operation in one pass. With one pass size, the response time is increased.
Used-Mem: Last_Memory_Used of v$sql_workarea. Memory used during the last execution of the cursor.

Used-Temp: Size of temp space used. Both optimal size and one-pass size memory are allocated from PGA.