RE: Used-Mem in execution plans
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>, "Tanel Poder" <tanel@xxxxxxxxxxxxxx>
- Date: Fri, 6 Jun 2014 13:44:18 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx [mailto:tanel@xxxxxxxxxx] 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@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:
