Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Monday, March 24, 2008

Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:
  • User A
    • Assigned to Temporary Tablespace TEMP_A
    • has table TA
  • User B
    • Assigned to Temporary Tablespace TEMP_B
    • has table TB
    • owns procedure PB (definer rights)
  • Both users has access rights for all above object (Select on Tables and Execute on Procedure)
So let's discuss some situations:
  1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
  2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:
Specify the tablespace or tablespace group for the user's temporary segments.
I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

Related Articles on Paweł Barut blog:


Gary Myers said...

Given it appears that temporary tablespace is based on the schema used to parse the query, I wonder if it is built into the optimizer plan.
If you changed a user's temporary tablespace, would a cached SQL still use the original temporary tablespace setting ? Not something that is likely to happen often, I guess.

Paweł Barut said...

Hi Gary,

This is very good question. I did:
1. run query - used TEMP_A
2. altered user - assigned tablespace TEMP_A
3. run exactly same query - used TEMP_B

Same thing happened when query was embedded in PL/SQL stored procedure.

Statistics from v$sql:
loaded_versions = 1
loads = 1
executions = 2
invalidations = 0
parse_calls = 2



Copyright © Paweł Barut
Printing from DOS to USB Printer