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.
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.
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
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”.
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
Well all this keeps us busy, doesn’t it ;)
Good news ;-)
The autotrace command shows correct values
Rachid
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
[…] 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.) […]
[…] 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/ ) […]
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
[…] 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/ ) […]
[…] 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/ ) […]
[…] 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/ ) […]
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
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??
Hi, that bug was fixed in 19c. Finally!