Temporary Tablespace Storage Parameters – What is Wrong with this Quote

22 04 2012

April 22, 2012 (Modified April 24, 2012)

I had intended to finish assembling the second half of the “Oracle Database 11gR2 Performance Tuning Cookbook” book review, however my free time that may be dedicated to book reviews has been a bit limited lately (I have a review of another book started, but left untouched for the last three months).  On a side note, I am a little shocked that none of the 21 errata reports that I filed for the first 88 pages of the book made it onto the publisher’s website, but maybe that is not too uncommon (the same has happened for at least one Apress book). 

I encountered an interesting quote on page 252 of the “Oracle Database 11gR2 Performance Tuning Cookbook” that I thought I would share with readers:

“The PCTINCREASE parameter cannot be specified when we use the AUTOALLOCATE option for the tablespace. We can have different tablespaces for each database user and we can query the dynamic performance view V$SORT_USAGE to retrieve the details about the active disk sorts occurring in the instance.

We can obtain better performance by striping the temporary tablespace using multiple disks. Please note that – due to their nature – temporary tablespaces are not affected by backup and restore operations.”

If you own the book, start on page 251 and read through the end of page 252. – there are a couple of additional interesting sentences in this section of the book 

What, if anything, is wrong with the above quote from the book? For those readers that need some assistance with this task, the Oracle Database 11.2 documentation might be helpful.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

(Added April 24, 2012, my book review notes for the four quoted sentences, plus one of the preceding sentences)


  • The book’s discussion of defining the INITIAL and NEXT storage parameters for temporary tablespaces as a multiple of the SORT_AREA_SIZE parameter seems to be out of place in an Oracle Database 11g R2 performance tuning book – dictionary managed tablespaces were deprecated with the release of Oracle Database 9.2, and the effect of these parameters is different in locally managed tablespaces (page 252).
  • The book states, “The PCTINCREASE parameter cannot be specified when we use the AUTOALLOCATE option for the tablespace.”  This statement appears to be incorrect – the Oracle Database documentation states that a specified value for PCTINCREASE will be ignored when creating an AUTOALLOCATE tablespace (page 252).
  • The book states, “We can have different tablespaces for each user and we can query the dynamic performance view V$SORT_USAGE to retrieve the details about the active disk sorts occurring in the instance.”  This statement is a bit confusing, so it is probably best to break the sentence into two separate logical sentences for analysis.  The first half of the statement seems to suggest that a separate temp tablespace should (or could) be created for each user – I am not sure that this is the author’s intended interpretation; the default temporary tablespace may be set at the user level so that not all users are required to use the same (default) temporary tablespace.  For the second logical sentence, the V$SORT_USAGE performance view was deprecated with the release of Oracle Database 9.2, replaced with V$TEMPSEG_USAGE.  In Oracle Database 11.2 (the version mentioned on the book’s front cover), the V$SORT_USAGE view is based on the GV$SORT_USAGE view which selects from X$KTSSO (confirm by issuing SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME=’GV$SORT_USAGE’;).  The GV$SORT_USAGE  view definition, which is pointed to by both the V$SORT_USAGE and V$TEMPSEG_USAGE synonyms, indicates that the SEGTYPE column values may be one of SORT, HASH, DATA, INDEX, LOB_ DATA, LOB_INDEX , or UNDEFINED.  The potential values of the SEGTYPE column suggests that the view potentially shows a variety of activity in addition to what the book mentions (the WHERE clause should be adapted to restrict the rows returned from this view, so that the results are consistent with this chapter’s contents).  The V$SORT_USAGE view also shows entries for aborted SQL statements, for instance when Ctrl-C is pressed in SQL*Plus and another SQL statement has not been executed by the session (page 252).
  • The book states, “Please note that – due to their nature – temporary tablespaces are not affected by backup and restore operations.”  This is a vague statement at best, and the accuracy of the statement depends in part on the type of backup performed (online or offline, user-managed or RMAN hot backup, etc.).  An online RMAN backup will not back up the tempfiles of locally managed temporary tablespaces; however, when those backed up databases are restored and recovered, Oracle Database 10.2 and later will recreate the temporary tablespace tempfiles when the database is opened (page 252).



11 responses

22 04 2012
Mladen Gogala

Well, it’s lunch time here in NYC, so in a short time I have, I noticed two problematic things:
1) Oracle Corp. claims that PCTINCREASE is being used during the object creation, which casts
slight doubt on the statement that it isn’t possible to specify it.
2) V$SORT_USAGE reflects all sort operations, not just the disk one. Every sort allocates temp segments
blocks, it just doesn’t always write to them.

22 04 2012
Dom Brooks

Not a comprehensive reply but my first thoughts are that in 11gR2 we should be looking at V$TEMPSEG_USAGE not V$SORT_USAGE, the name of which better reflects the nature of the data.

Secondly, we can obtain much better performance by avoiding using temp space altogether – one importance of accurate execution plan statistics – or if we have to use temp by trying to avoid multi-pass operations, a topic which might need to branch into workarea policy/sizing.

I don’t have the book but do they mentioned multiple tablespace groups?

22 04 2012
Mladen Gogala

Dominic, you really should hurry up and get this book quickly. I believe that the series of reviews done on this blog by Charles will undoubtedly turn the reviewed book into a wildly successful Oracle tuning book that will evaporate from the shelves of the book stores very, very quickly. Charles is really going out of his way to advertise that book.

23 04 2012
Charles Hooper

With the reviews that the book has been receiving, it most certainly will be flying off the shelf. I found a couple of other reviews of the book:

I buy every book that I review (although in one case the selling site could not ship the book for many months, and then the selling site cancelled my second order for the book), so when first opening the book I expect it to be a book that matches the accuracy and throughness of the better technical books on the market. If I do not see that level of accuracy and throughness, I mention that fact in detail in the review as a cautionary note to anyone else who may have the fortune of reading the same book. On a positive note, with the help of my reviews and the reader comments found in the public comment blog articles that are associated with the reviewed books, some books become much more valuable to the reader. So, thank you to the readers of this blog for helping technical Oracle books fly off the shelves (and hopefully not into the round filing cabinet). 😉

22 04 2012
Charles Hooper


I don’t have the book but do they mentioned multiple tablespace groups?

No mention that I have seen in the book. (That feature is mentioned in the documentation that is referenced above.)

Regarding the use of V$SORT_USAGE, the Oracle documentation indicates that performance view is deprecated as of Oracle Database 9.2. Oddly, even though the documentation indicates that the V$SORT_USAGE view is deprecated, in the V$TEMPSEG_USAGE synonym as well as the V$SORT_USAGE synonym points to the V_$SORT_USAGE view (which of course selects from the V$SORT_USAGE view which selects from the GV$SORT_USAGE view which selects from X$KTSSO).

I think that there are more interesting items in the book’s quote. I will wait for at least 24 hours before sharing my review comments.

22 04 2012
Charles Hooper

Looking back at my comment above, it might not be clear that I am not disagreeing with Dom. I was a little surprised that the suggestion of using V$TEMPSEG_USAGE rather than V$SORT_USAGE was offered so quickly (that is one of the items that I missed when reviewing the Firefighting book, but did catch in this book’s review).

23 04 2012
Dom Brooks

It’s a view that I quite often have to look at and, because I’m still flitting between 9i and 11gR2 instances, the knowledge has not been flushed from my own very limited cache.

24 04 2012
Charles Hooper

By book review notes for these four sentences (plus one bonus sentence) are now attached at the bottom of this article.

25 04 2012
Mich Talebzadeh


Apologies for being off topic here.

I looked at the topic section but I cannot recall seeing any topic on effective measurements of writes and redo logs generated by a query. Tracing is great with tkprof for assorted reads as each process does its own reads. However, the writes and redos will be performed by DBWR and LGWR. I would like to see the volume of physical writes and wait time associated with them.

One way would be to use AWR and take snapshots

INSERT INTO testwrites
SELECT * FROM tdash;

The above is a simple example of inserting 1.5 million rows from one table to another (tdash based on all_objects table + 2 varchar2(4000) columns). Then I can examine the redo size and physical writes. I realise that redo size can also be obtained from autotrace. Are there other alternatives to see the volume of writes generated?



26 04 2012
Charles Hooper


If you purchase Jonathan Lewis’ “Oracle Core” book, you will have access to a couple of the scripts that he wrote for capturing perfomance statistic deltas. I think that the two scripts that you are interested in are snap_myst.sql and snap_stat.sql

Tanel Poder offers a free utility called Snapper. I personally have not used it, but I have heard many favorable comments about what it does:

My Hyper-Extended Oracle Performance Monitor program will also do that, you can see the screen captures here:

In one of the book chapters that Randolf Geist and I co-wrote, we included a simple script that calculates the delta values of various performance views – this might be a good starting point:

On page 212 in the same book, we showed how to do the same with Statspack snapshots (with a timed delay between captures):

For those people who might not be aware, usage of the DBMS_WORKLOAD_REPOSITORY package requires an Enterprise Edition license and a Diagnostic Pack license, while Statspack requires neither.

12 05 2012

The temporary LOB’s seen in v$sort_usage indicate that the session is doing a disk sort and the session statistic for ‘sorts (disk)’ would not be increased.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: