RE: Used-Mem in execution plans

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Fri, 6 Jun 2014 10:32:10 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022EBB1B_at_WIN02.hotsos.com>

Thanks Tanel.

It looks like the value in V$SQL_WORKAREA is correct (it’s in bytes) it’s the one in V$SQL_PLAN_STATISTICS_ALL that is off. So if the value is coming from V$SQL_WORKAREA then it’s correct, however if it’s form V$SQL_PLAN_STATISTICS_ALL then it’s off and should be multiplied by 1024 to get the right number of bytes. The docs say the column (LAST_TEMPSEG_SIZE) in both tables is in bytes, but it sure looks like in V$SQL_PLAN_STATISTICS_ALL it’s not.

So maybe the USED values come from V$SQL_PLAN_STATISTICS_ALL? Little more digging around it looks like. Hummm…

SQL> SELECT SQL_ID, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE 2 FROM V$SQL_WORKAREA 3 WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE

---------------- -----------------

g4tr51k11z5a0 403701760 403701760

SQL> SQL> SELECT SQL_ID, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE 2 FROM V$SQL_PLAN_STATISTICS_ALL 3 WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE

---------------- -----------------

g4tr51k11z5a0

g4tr51k11z5a0 394240 394240

g4tr51k11z5a0

SQL> SQL> SELECT 394240*1024 FROM DUAL; 394240*1024

403701760

+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Ric Van Dyke

Education Director

Oracle Ace Associate

Hotsos Ltd.

Hotsos Symposium
March 1-5 2015

Make your plans to be there now!

From: tanel_at_poderc.com [mailto:tanel_at_poderc.com] On Behalf Of Tanel Poder Sent: Friday, June 06, 2014 1:01 AM
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_at_hotsos.com> 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

| 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