Re: Used-Mem in execution plans
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>
- Date: Fri, 6 Jun 2014 00:01:22 -0500
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>
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:
