Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Wrong Information about Temporary Space Usage in V$SQL_PLAN_STATISTICS_ALL and DBMS_XPLAN Output

5 May 2009 16 Comments Written by Christian Antognini

As you can read in the documentation, the columns MAX_TEMPSEG_SIZE and LAST_TEMPSEG_SIZE in the dynamic performance view V$SQL_WORKAREA provide information about the size of the temporary segment used for a specific workarea. The values are given in bytes. Let’s perform a test to check this information…

  • Create a test table that contains about 1MB of data:
SQL> CREATE TABLE t AS
  2  SELECT rownum AS id, dbms_random.string('p',1000) AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000;

SQL> execute dbms_stats.gather_table_stats(user, 't')
  • Setup the session to force the user process to spill into a temporary segment:
SQL> ALTER SESSION SET workarea_size_policy = manual;
SQL> ALTER SESSION SET sort_area_size = 524288;
  • Run test query including a sort operation (that spills to the temporary tablespace):
SQL> SELECT id FROM t ORDER BY pad;
  • Check the amount of used temporary space by querying V$SQL_WORKAREA:
SQL> SELECT max_tempseg_size, last_tempseg_size
  2  FROM v$sql_workarea
  3  WHERE (sql_id, child_number) IN (SELECT prev_sql_id, prev_child_number
  4                                   FROM v$session
  5                                   WHERE sid = sys_context('userenv','sid'));

MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
---------------- -----------------
         2097152           2097152

According to this information the size of the temporary space used to execute the query was 2MB. So far, so good.

Always according to the documentation another dynamic performance view, V$SQL_PLAN_STATISTICS_ALL, should provide the same information (remember, V$SQL_PLAN_STATISTICS_ALL shows in a single view all the information provided by the views V$SQL_PLAN, V$SQL_PLAN_STATISTICS, and V$SQL_WORKAREA). Let’s check it…

  • Run the same test query as before:
SQL> SELECT id FROM t ORDER BY pad;
  • Check the amount of used memory by querying V$SQL_PLAN_STATISTICS_ALL:
SQL> SELECT max_tempseg_size, last_tempseg_size
  2  FROM v$sql_plan_statistics_all
  3  WHERE (sql_id, child_number) IN (SELECT prev_sql_id, prev_child_number
  4                                   FROM v$session
  5                                   WHERE sid = sys_context('userenv','sid'))
  6  AND max_tempseg_size IS NOT NULL;

MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
---------------- -----------------
            2048              2048

Ups! According to this information the size of the temporary space used to execute the query was 2KB. Mhmm, something is not good… For this reason, at the end of 2007 I opened a service request about this issue. The support guy recognized the problem and opened a bug. Fine. For some unknown reasons (?) yesterday I was checking the status of few bugs. While doing so I noticed that this specific bug was closed few months ago with the status “Could Not Reproduce”! I don’t know you, but on my 64-bit Linux server I can reproduce it with at least 11.1.0.7.0, 11.1.0.6.0, 10.2.0.4.0, 10.2.0.3.0, 10.2.0.2.0, 10.2.0.1.0, 10.1.0.5.0, 10.1.0.4.0 and 10.1.0.3.0. Geez!

It is essential to note that also the package DBMS_XPLAN shows wrong information (here an example for the same query as before):

SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'memstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  ftb71b6926dtn, child number 0
-------------------------------------
SELECT id FROM t ORDER BY pad

Plan hash value: 961378228

---------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |         |
|   1 |  SORT ORDER BY     |      |   1000 |  1152K|   562K|  529K (1)|    2048 |
|   2 |   TABLE ACCESS FULL| T    |   1000 |       |       |          |         |
---------------------------------------------------------------------------------

The only good thing about the fact that Oracle is not willing to fix the bug is that my book, Troubleshooting Oracle Performance, does not need to be updated. In fact, at page 210, while describing the output of the package DBMS_XPLAN I wrote the following information:

  • Used-Tmp: The amount of temporary space used by the operation during the last execution. This value must be multiplied by 1,024 to be consistent with the other memory utilization columns (for example, 32K means 32MB).
  • Max-Tmp: The maximum amount of temporary space used by the operation. This value has to be multiplied by 1,024 to be consistent with the other memory utilization columns (for example, 32K means 32MB).

ADDENDA (Mai 6, 2009): This post was noticed by an Oracle employee and, as a result, the bug was reopened. Thank you Greg!

ADDENDA (November 2, 2020): Finally, a fix for that bug is included in 19c.

10gR1, 10gR2, 11gR1, Bug, TOP
Report Information about CPU Activity in SQL*Plus
Troubleshooting Oracle Performance – Downloadable Files

16 Comments

5 Pings/Trackbacks

  1. Alberto Dell'Era Alberto Dell'Era
    5 May 2009    

    I feel your pain – I did quit submitting bug reports to Support such as the one you are describing, that I used to submit in an attempt to be “nice to the Oracle ecosystem”, several years ago, since I’ve noticed that they are not liked by them – they are almost always closed or ignored (at least mine). It is sad since that means that a lot of inconsistencies are left in the product – this one is a good example, it would take a few minutes to be fixed, with almost zero risk, for the benefit of everyone. Amen.

    Reply
  2. Christian Antognini Christian Antognini
    5 May 2009    

    Ciao Alberto

    I try to not give up!

    An example: right now the oldest SR I have in Metalink, ups… My Oracle Support (and the name explicitly says that they provide support), is dated “05-SEP-07 18:49:03”. The associated bug spent the whole 2008 hibernated and, who knows why, they resumed it in 2009. Few minutes ago when I checked the date I looked at the bug and I saw “*** 05/04/09 10:37 am *** (CHG: FixBy->11.2) “. Yeah! I’m looking forward to get the next beta release to check the fix. BTW, this is also a bug with V$SQL_PLAN_STATISTICS_ALL… the one that I mention at page 233 in TOP. So, expect a post to explain the problem and says that it has been fixed ;-)

    Cheers,
    Chris

    Reply
  3. Alberto Dell'Era Alberto Dell'Era
    6 May 2009    

    Ciao Chris,

    great to know, but a sane process would not need to open a bug AND then blog about it AND then have an “Oracle employee” notice it … … it should be enough to open a bug, and frankly, I would also expect a “thank you!” note from Support for taking the time to log a bug “for the benefit of Oracle and its community”.

    Reply
  4. Christian Antognini Christian Antognini
    6 May 2009    

    Ciao Alberto

    It goes without saying that I fully agree with you. They have a problem with the quality of the support… I hope that they will adress it soon or later. In the mean time, we have to struggle with it.

    Cheers
    Chris

    Reply
  5. Tanel Poder Tanel Poder
    9 May 2009    

    Well all this keeps us busy, doesn’t it ;)

    Reply
  6. Rachid Rachid
    28 May 2009    

    Good news ;-)
    The autotrace command shows correct values

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 961378228
    
    -----------------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |  1000 |   980K|       |   247   (1)| 00:00:03 |
    |   1 |  SORT ORDER BY     |      |  1000 |   980K|  2008K|   247   (1)| 00:00:03 |
    |   2 |   TABLE ACCESS FULL| T    |  1000 |   980K|       |    33   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

    Rachid

    Reply
  7. Christian Antognini Christian Antognini
    29 May 2009    

    Rachid

    Please notice that TempSpc is “only” the estimated amount of temporary space used by an operation. For this reason, I find it not very interesting…

    Cheers,
    Chris

    Reply
  8. PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server « Charles Hooper's Oracle Notes PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server « Charles Hooper's Oracle Notes
    18 January 2010    

    […] Let’s see, almost 25 minutes to execute the SQL statement, a total of roughly 425MB of memory was used during one pass workarea executions (but from our earlier output, not all of that memory was in use at the same time) and the SORT ORDER BY operation used 11M of TEMP tablespace space… but is that 11MB, or is it 11 million KB, or is it 11,534,336 KB (2^20 * 11 KB)?  Remember earlier we found “that the temporary segment size in the TEMP tablespace is about 11.14GB”, so that 11M means 11,534,336 KB, or about 11GB.  OK, that was slightly confusing, but we are not done yet.  (Side note: the author of the book “Troubleshooting Oracle Performance” commented on the Used-Tmp column here.) […]

  9. DB Optimizer » Display_Cursor DB Optimizer » Display_Cursor
    18 June 2012    

    […] as bytes, though correctly bytes in v$sql_workarea ) LAST_TEMPSEG_SIZE (kb – "" , see http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/ ) […]

  10. Kyle Hailey Kyle Hailey
    22 June 2012    

    Thanks for the clear example.
    This kind of stuff has cause me hours of time and confusion.
    Great to have an example that sorts it out clearly.

    – Kyle

    Reply
  11. DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 开发者 DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 开发者
    9 August 2013    

    […] as bytes, though correctly bytes in v$sql_workarea ) LAST_TEMPSEG_SIZE (kb – "" , see http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/ ) […]

  12. DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 无忧网 DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 无忧网
    23 September 2013    

    […] as bytes, though correctly bytes in v$sql_workarea ) LAST_TEMPSEG_SIZE (kb – "" , see http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/ ) […]

  13. Kyle Hailey » Power of DISPLAY_CURSOR Kyle Hailey » Power of DISPLAY_CURSOR
    31 October 2013    

    […] as bytes, though correctly bytes in v$sql_workarea ) LAST_TEMPSEG_SIZE (kb – "" , see http://www.antognini.ch/2009/05/wrong-information-about-temporary-space-usage/ ) […]

  14. Brian Fitzgerald Brian Fitzgerald
    20 May 2014    

    11.2.0.4 and 12.1.0.1 are affected

    11.2.0.4 example
    v$sql_workarea:
    max_tempseg_size : 244318208
    last_tempseg_size : 244318208

    | Id | Operation |… | Used-Tmp|
    | 9 | WINDOW SORT |… | 233K|

    $ expr 244318208 / 1024 / 1024
    233

    12.1.0.1 example:
    last_tempseg_size : 24117248

    | Id | Operation |…| Used-Tmp|
    |* 11 | HASH JOIN |…| 23552 |

    $ expr 24117248 / 1024
    23552

    Spot on!

    max_tempseg_size and Used-Tmp are off by exactly 1024.

    It’s May 2014.:
    Happy 5th anniversary blogging this Oracle bug!

    Brian Fitzgerald
    Oracle Exadata 11g Certified Implementation Specialist
    https://twitter.com/ExaGridDba

    Reply
  15. Kaley Kaley
    30 October 2020    

    I am using Oracle Express Edition 18c, and I still seem to be running into this problem where Used-Tmp has to be multiplied by 1k apparently.

    Has this *seriously* still not been fixed a decade later??? Does Oracle have any plans to fix it??

    Reply
    • Christian Antognini Christian Antognini
      2 November 2020    

      Hi, that bug was fixed in 19c. Finally!

      Reply
  1. PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server « Charles Hooper's Oracle Notes on 18 January 2010 at 07:15
  2. DB Optimizer » Display_Cursor on 18 June 2012 at 18:08
  3. DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 开发者 on 9 August 2013 at 09:40
  4. DBMS_XPLAN.Display_Cursor分析[转] - 数据库 - 无忧网 on 23 September 2013 at 00:03
  5. Kyle Hailey » Power of DISPLAY_CURSOR on 31 October 2013 at 14:01

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.