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