RE: Used-Mem in execution plans
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "tanel@xxxxxxxxxxxxxx" <tanel@xxxxxxxxxxxxxx>, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>
- Date: Fri, 6 Jun 2014 08:19:48 +0000
It's the last_execution column - which would show "1 PASS" (or "2 PASSES" etc.)
It's not really a user-friendly column name, given that there's also a
"total_executions" column which means something completely different, viz:
number of times that a workarea has been used (as opposed to starts/last
starts, which are about the number of times the line has been called).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Tanel Poder [tanel@xxxxxxxxxxxxxx]
Sent: 06 June 2014 06:01
To: Ric Van Dyke
Cc: oracle-l-freelists
Subject: Re: Used-Mem in execution plans
Yep! The USED figures come from the V$SQL_WORKAREA view (the LAST_... columns).
It won't be populated until the query finishes (or is canceled). In the
brackets you have number of passes that's 0 for optimal executions (LAST_PASSES
colum I think). The Used-TMP field in DBMS_XPLAN is buggy, it's in kilobytes,
so the 385K in your output actually means 385 MB :)
--
Tanel Poder
Enkitec (The Exadata Experts)
Services<http://enkitec.com> | Training<http://blog.tanelpoder.com/seminar/> |
Troubleshooting<http://blog.tanelpoder.com/> | Exadata
Book<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923>
On Thu, Jun 5, 2014 at 11:22 AM, Ric Van Dyke
<ric.van.dyke@xxxxxxxxxx<mailto:ric.van.dyke@xxxxxxxxxx>> wrote:
Soft-ball here. I can’t find any documentation or blog posts that speak to
this.
When I look at an execution plan with DBMS_XPLAN.DISPLAY_CURSOR that does a
sort I see the memory used for the sort. There is now a column Used-Mem along
with the OMem and 1Mem columns. In the Used-Mem there is a number in
parentheses, I assume the number is the number of passes over/thru the memory
segment, and that a single pass would actually show a zero. Can someone
confirm or deny this?
For example:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
3 ('g4tr51k11z5a0',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID g4tr51k11z5a0, child number 0
-------------------------------------
select * from big_tab order by owner
Plan hash value: 3765827574
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2868K|00:00:46.50 |
48224 | 97395 | 49190 | | | | |
| 1 | SORT ORDER BY | | 1 | 2868K| 2868K|00:00:46.50 |
48224 | 97395 | 49190 | 432M| 6863K| 100M (1)| 385K|
| 2 | TABLE ACCESS FULL| BIG_TAB | 1 | 2868K| 2868K|00:00:04.26 |
48215 | 48205 | 0 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL>
Thanks
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Oracle Ace Associate
Hotsos Ltd.
**** Hotsos Symposium
**** March 1-5 2015
Make your plans to be there now!
Other related posts:
