Friday, June 6, 2014

Memory Used in a Sort

Found a little issue with the data displayed by DBMS_XPLAN.DISPLAY_CURSOR when it comes to the Used-Tmp column. This is certainly something to be aware of when you look at that column in the output. 


Take a look at this simple statement:


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 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------------

Looking at the Used-Tmp column it appears this didn’t use much temp space, only 385K for a sort that has a Used-Mem of 100M, hey that’s really great!  But how can that be?  Well it can’t is the short answer.  There appears to be a bug here. 

The data used by this function is coming from V$SQL_PLAN_STATISTICS_ALL.  If we look at that view for this query we can see that value is in the LAST_TEMPSEG_SIZE column (394240/1024 = 385K).  The documentation says that all these column are in bytes.  But It certainly appears that LAST_TEMPSEG_SIZE isn’t.

SQL> SELECT SQL_ID, ESTIMATED_OPTIMAL_SIZE, ESTIMATED_ONEPASS_SIZE, LAST_MEMORY_USED, LAST_TEMPSEG_SIZE
  2  FROM v$sql_plan_statistics_all
  3  WHERE SQL_ID = 'g4tr51k11z5a0';

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE
------------- ---------------------- ---------------------- ---------------- -----------------
g4tr51k11z5a0
g4tr51k11z5a0              453454848                7027712        104879104            394240
g4tr51k11z5a0

How is it that I can say with any level of certainty that this isn’t in bytes?  One thing is it certainly seems odd that a sort that used 100M would only use a couple hundred bytes of temp space.  But also if we look at a different view V$SQL_WORKAREA we see:

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

SQL_ID        ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_TEMPSEG_SIZE
------------- ---------------------- ---------------------- ---------------- -----------------
g4tr51k11z5a0              453454848                7027712        104879104         403701760

This appears to be in bytes.  Note that 40370176/1024 = 394240 the number we saw in V$SQL_PLAN_STATISTICS_ALL.  

So it sure looks like the view V$SQL_PLAN_STATISTICS_ALL is already converting the temp segment size into Kilobytes, but the function DBMS_XPLAN.DISPLAY_CURSOR thinks it’s in bytes and covert it to Kilobytes.  This actually makes it Megabytes, so it wasn’t using 385K it was using 385M.

No comments:

Post a Comment