Database Reference
In-Depth Information
RS@SQL> select inst_id, file_id, extents_cached , extents_used
from gv$temp_extent_pool
order by inst_id, file_id;
INST_ID FILE_ID EXTENTS_CACHED EXTENTS_USED
---------- ---------- -------------- ------------
...
1 6 4026 0
1 7 4021 2
1 8 3984 4
1 9 4033 2
...
3 6 3984 0
3 7 3988 1
3 8 3985 0
3 9 3967 1
The size of the cached extent is governed by the attributes of the temporary tablespace. For example, in RAC1
database, the extent size is 128KB.
RS@SQL>select tablespace_name, INITIAL_EXTENT, NEXT_EXTENT
from dba_tablespaces
where tablespace_name='TEMP';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEMP 131072 131072
Extent caching is a soft reservation technique. Consider that a RAC1 instance used all of its cached extents
(because of a session connected to RAC1 performing a massive sort or join operation); then, that instance will ask
other instances to release the soft reservation for a group of extents in the temporary tablespace. After the extents are
uncached from other instances, the RAC1 instance can cache the uncached extents.
Extent caching and uncaching operations are performed under the protection of SS Enqueue. Further, the DFS
lock handle mechanism (discussed in Chapter 11) with CI Enqueue is used to trigger uncaching of extents in other
instances. So, excessive caching/uncaching can lead to waits for SS Enqueue and waits for DFS lock handle. Since
version 11g, uncaching of extents is performed in batches of 100 extents each per operation, and so SS Enqueue
contention is greatly reduced.
The following guidelines can be used while creating temporary tablespaces:
Create as many temp files as the number of instances in every temp tablespace. For example,
in an eight-instance database, there should be at least eight temp files associated with every
temporary tablespace. This strategy reduces the file header level locking issues dramatically.
While adding an instance to a database, increase the size of temporary tablespace. Also,
increase the number of temp files in temporary tablespaces.
If an application component uses an excessive amount of temporary tablespace, then create
a new user for that application component, create additional temporary tablespace, and use
application affinity to keep that component to a node. This strategy reduces the ill effects
associated with excessive extent caching and uncaching activities.
 
Search WWH ::




Custom Search