Tuesday, June 16, 2009

Temporary Tablespace Groups

These allow to group multiple temporary tablespaces into a single group and assign a user this group of tablespaces instead of a single temporary tablespace.

This raises some interesting questions, and for some of these I don't find answers in the official documentation. Some of these questions are:

- Can a single workarea execution allocate space from more than one temporary tablespace, e.g. to support large serial sort operations?

A workarea belongs to a single operation of an execution plan. There are several different types of operations that require a workarea, among them are sorts, hash joins, group bys and sort/merge joins.

This workarea can fit into available PGA memory, but can also spill to disk in case there is insufficient memory available to support the operation.

Furthermore this implies that a execution of a single SQL statement can require multiple workareas, e.g. a quite simple statement might need two workareas for two hash joins and a third one for a subsequent sort order by operation.

Note that there are other types of operations that don't require a workarea, e.g. a nested loop join doesn't require a workarea (and therefore will never acquire temporary space).

Details about workareas can be obtained from various dynamic performance views, e.g. V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE and V$SQL_WORKAREA_HISTOGRAM.

- Can multiple workareas of a single session allocate space from different tablespaces?

- According to the documentation different sessions of the same user can use different temporary tablespaces from the group. Is this correct?

- What about parallel execution? The documentation says that the parallel slaves can use different temporary tablespaces. Is this correct?

Here's a simple testcase that generates two small temporary tablespaces and assigns them to the same group. A test user is assigned first a single temporary tablespace and afterwards the group to see if this makes any difference.

It uses a carefully crafted table for which a sort operation doesn't fit into a single small temporary tablespace but is supposed to fit if both temporary tablespaces of the group can be used.

The session is deliberately using a very small sort_area_size in manual workarea policy mode to force the sort operation to spill to disk.

The following are the results from 11.1.0.7 Win32. Similar results can be seen from 10.2.0.4 Win32. The database used a 8KB default block size and a locally managed tablespace with manual segment space management (no ASSM) for the data but that shouldn't matter here much.

SQL> SQL> exec open :r for select * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL> SQL> -- Sort still doesn't fit into available temp space (but it should as you'll see later)SQL> -- so obviously a single workarea can't use space from different tablespacesSQL> declare 2 rec test_temp_grp%rowtype; 3 begin 4 fetch :r into rec; 5 end; 6 /declare*ERROR at line 1:ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRPORA-06512: at line 4

SQL> SQL> -- What if we have multiple workareas per sessionSQL> exec open :r for select * from test_temp_grp where rownum <= 5000 order by object_name

SQL> SQL> -- So it looks like a session is limited to a particular tablespace taken from the groupSQL> -- What happens to a second session of the same userSQL> -- One tablespace is still in use by cursor RSQL> -- Check V$SORT_USAGE if interestedSQL> SQL> -- Run the same query in second sessionSQL> -- You might need multiple attempts to get this workingSQL> -- Depending on the tablespace taken from the groupSQL> -- and then close second session to free temp spaceSQL> -- Press ENTER to continueSQL> SQL> pause

SQL> SQL> -- This works if the second session is assigned a different tablespace taken from the groupSQL> -- So different sessions of the same user will potentially be assigned to different tablespacesSQL> SQL> -- What about parallel executionSQL> exec close :r

SQL> SQL> -- This worksSQL> -- So the PX sessions can be assigned to different tablespaces taken from the groupSQL> -- Check V$SORT_USAGE if interestedSQL> declare 2 rec test_temp_grp%rowtype; 3 begin 4 fetch :r into rec; 5 end; 6 /

PL/SQL procedure successfully completed.

SQL> SQL> spool off

So in summary the following can be deduced:

1. A single workarea execution can't use space from more a single tablespace even when the user is assigned a temporary tablespace group. Or in other words: A single workarea execution can only allocate a single temporary segment, and segments in general can't span multiple tablespaces in Oracle, i.e. they must fit into a single tablespace.

2. Even multiple workareas for a single session can't use space from the multiple temporary tablespaces available. So this looks like a session attribute, i.e. assigned to the session, and all workareas of that session will use the single temporary tablespace assigned (taken from the group)

3. Multiple sessions of the same user can be assigned to different temporary tablespaces taken from the group.

4. Parallel execution slave sessions can do the same, they can be assigned to different temporary tablespaces taken from the group.

So in order to leverage the power of temporary tablespace groups you need either multiple sessions running serial SQL or the parallel execution option.

6 comments:

Bryan Grenn
said...

One of the reasons I'm looking at temporary tablespace groups, is to take advantage of the shortcomings you mention.Sometimes we have users adhoc queries go awry and use up all the temporary space.. We are in 10g, and utlize Global Temporary tables that get clobbered when this occurs.. by setting up temporary tablespace groups we can isolate the sessions. If I set up four tablespaces in the group, there is 25% chance your session will be affected by this runaway query (rather than 100% chance).. Someones shortcoming, is another persons advantage.

temporary tablespace groups is interesting concept, however still not sure if understand what is the difference (from performance point of view) between having one TEMP tablespace with 16 tempfiles or temporary tablespace group with 4 temporary tables, 4 temp files in each.

or to be more precise, is it still true in release >= 11gR2 that oracle performs allocations always to first temp file and than continues to another one? From my observations, it seems this is no longer true.

thank you for your answer. Finally I found a link with temporary tablespace groups used for tuning, it had crossed my mind few days ago...

I can confirm there was issue with temporary space allocation in the past, but fortunately, it is no more true.

And now I can continue wondering why was this concept ever introduced :) I can only think of bigfile tablespace DWH/Datamart environment with running huge parallel hash joins... Then having 8 bigfile tablespaces instead of 1 could be way how to avoid buffer busy waits (when allocation temp extents). However, in that case I would maybe made an exception for temp and had used old-fashioned smallfile tablespaces...

And of course, ad-hoc queries and lack of temp space, which was presented by Bryan here.