RE: Used-Mem in execution plans

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Fri, 6 Jun 2014 13:44:18 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022EBB6D_at_WIN02.hotsos.com>

OK, I’m sure you have all been on the edge or your seats to find out what is going on with this.

Turns out that DBMS_XPLAN.DISPLAY_CURSOR gets much (all?) of its data from V$SQL_PLAN_STATISTICS_ALL and not from V$SQL_WORKAREA. After hunting around quite a bit there is a description of the procedure in DBMSXPLN.SQL. This also matches up with the data I see in the output. And also it appears that LAST_TEMPSEG_SIZE (which is Used-Tmp) appears are in K not bytes, the values are not consistent between the two view. The value in V$SQL_WORKAREA is 1024 times larger than the one in V$SQL_PLAN_STATISTICS_ALL.

But not LAST_MEMORY_USED (which is Used-Mem), this one appears to be in bytes, and is consistent in the two views.

For the OMem and 1Mem columns those do appear to be in bytes and are consistent between V$SQL_PLAN_STATISTICS_ALL and V$SQL_WORKAREA. The values for OMem and 1Mem come from the ESTIMATED_OPTIMAL_SIZE and ESTIMATED_ONEPASS_SIZE columns.

The problem is with the Used-Tmp column only. It certainly looks like it is in K not bytes and it comes from V$SQL_PLAN_STATISTICS_ALL. In V$SQL_WORKAREA the value is in bytes.

Got it?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke Sent: Friday, June 06, 2014 11:32 AM
To: Tanel Poder
Cc: oracle-l-freelists
Subject: RE: Used-Mem in execution plans

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