A tempfile can remain large after the report that grew it has finished. To diagnose TEMP demand, separate the physical file from the temporary allocations inside it. During a sort, Oracle allocates temporary extents for intermediate work. When that sort releases them, the extents can remain allocated to the sort segment and become available for another operation. The physical file can retain its existing size.
Oracle DBA Lesson 57 Part 2 — Identify a Temporary-Space Consumer
A large tempfile and little current demand can coexist. Measure the current consumers while the work is happening, identify their SQL and sessions, and choose the next capacity or workload check from those observations.
Separate three measurements
| Question | View and useful fields | Interpretation |
|---|---|---|
| What files exist, and how large can they grow? | DBA_TEMP_FILES: FILE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES | File inventory and growth configuration |
| How much temporary space is available? | DBA_TEMP_FREE_SPACE: TABLESPACE_SIZE, ALLOCATED_SPACE, FREE_SPACE | Tablespace-level availability, including reusable allocation |
| Which consumers have temporary allocations now? | V$TEMPSEG_USAGE: USERNAME, SQL_ID_TEMPSEG, SEGTYPE, TABLESPACE, BLOCKS | Current allocation attribution at the instant queried |
ALLOCATED_SPACE includes both allocated-and-used space and allocated space available for reuse. FREE_SPACE includes reusable allocated space and currently unallocated space. These columns overlap. Subtracting ALLOCATED_SPACE from TABLESPACE_SIZE does not give the entire usable free space.
The file's logical size and maximum growth do not establish available filesystem or ASM capacity. Physical allocation of tempfile blocks can be deferred on some platforms. Inventory the Oracle storage and check the actual storage destination when you assess headroom. Part 1 created this tempfile at 64 MiB with a 256 MiB maximum. The file can later stand at 256 MiB after the report that grew it has finished.
SHOW CON_NAME
SELECT file_id, file_name,
ROUND(bytes / 1048576, 2) AS file_mib,
autoextensible,
ROUND(maxbytes / 1048576, 2) AS max_mib
FROM dba_temp_files
WHERE tablespace_name = 'APP_TEMP'
ORDER BY file_id;
SELECT tablespace_name,
ROUND(tablespace_size / 1048576, 2) AS total_mib,
ROUND(allocated_space / 1048576, 2) AS allocated_mib,
ROUND(free_space / 1048576, 2) AS free_mib
FROM dba_temp_free_space
WHERE tablespace_name = 'APP_TEMP';
SHOW CON_NAME is a SQL*Plus command. Confirm that the current container is the intended PDB, APP_PDB in this lesson, before you interpret the results. Preserve complete filenames. This lesson uses MiB: 1 MiB = 1,048,576 bytes. The spoken explanation uses megabytes for these binary sizes.
Run these PDB-scoped joins in the PDB. Tablespace names are not unique across containers, so the same joins from the root do not identify this PDB's files. The statements fit a single-instance serial workload. A RAC investigation uses instance-aware GV$ views and matching INST_ID joins. See DBA_TEMP_FILES and DBA_TEMP_FREE_SPACE.
Capture current consumers
SELECT u.username, u.sql_id_tempseg AS sql_id,
u.segtype, u.tablespace,
ROUND(SUM(u.blocks * t.block_size)
/ 1048576, 2) AS mib
FROM v$tempseg_usage u
JOIN dba_tablespaces t
ON t.tablespace_name = u.tablespace
GROUP BY u.username, u.sql_id_tempseg,
u.segtype, u.tablespace
ORDER BY mib DESC;
An illustrated snapshot while both allocations are still held:
| USERNAME | SQL_ID | SEGTYPE | TABLESPACE | MIB |
|---|---|---|---|---|
APP_USER | 5h2m9c6n1p8q3 | SORT | APP_TEMP | 96 |
REPORT_USER | 3a7b9c2d5f8g1 | HASH | APP_TEMP | 16 |
The first row attributes 96 MiB of currently allocated temporary blocks to a sort for that user and the statement that created the segment. The second attributes 16 MiB to a hash allocation. This is allocation. It is not a measurement of currently occupied payload bytes, disk throughput, or a guaranteed peak.
BLOCKS is a count of allocated Oracle blocks. Multiply it by the matching tablespace's BLOCK_SIZE. An assumed 8 KiB multiplier misstates demand where the block size differs. For a BLOCK_SIZE of 8192, 12,288 blocks × 8192 / 1,048,576 = 96 MiB. The query reads that size from DBA_TABLESPACES instead of hardcoding it.
SQL_ID_TEMPSEG identifies the statement that created the temporary segment. This query aliases it as SQL_ID. The view also has a distinct SQL_ID column. Retain both fields when the investigation needs their distinction. The creating statement is not necessarily the statement the session is executing now. A null identifier, and a temporary allocation of another operation type, needs its own interpretation.
The grouping combines rows that share the user, creating SQL, segment type, and tablespace. Several sessions can contribute to a single result row. The row is an initial ranking. It is not an individual-session cancellation target. See V$TEMPSEG_USAGE and DBA_TABLESPACES.
Correlate the sessions
Use the SQL identifier captured in the consumer query. The predicate below uses the illustrated sort identifier. Replace it with the captured value. When that value is null, inspect the usage rows and the session association directly instead of using this equality predicate.
SELECT DISTINCT s.sid, s.serial#, s.username, s.status
FROM v$session s
JOIN v$tempseg_usage u
ON s.saddr = u.session_addr
AND s.serial# = u.session_num
WHERE u.sql_id_tempseg = '5h2m9c6n1p8q3';
An illustrated result is:
| SID | SERIAL# | USERNAME | STATUS |
|---|---|---|---|
| 83 | 4217 | APP_USER | ACTIVE |
SESSION_ADDR corresponds to the session's SADDR. SESSION_NUM corresponds to SERIAL#. The serial number distinguishes successive sessions that reuse a SID. DISTINCT avoids listing the same session repeatedly when it owns several matching temporary allocations.
Record the observation time, PDB, SID, SERIAL#, creating SQL identifier, SEGTYPE, tablespace, and measured amount together. Recheck the live session's current SQL, application role, and workload before any action. ACTIVE means the session is executing SQL at that moment. This row alone does not identify a bad statement or justify cancelling it. See V$SESSION.
Interpret a later empty result
After the example sort releases its allocation, its usage row can disappear while its extents remain reusable inside the sort segment. If the hash work has also released its allocation, a later snapshot may contain no rows. Preserve both snapshots and their times.
An empty later snapshot cannot reconstruct earlier demand. Preserve the original error and its timestamp, then observe a bounded repeat if the demand still needs to be captured. Temporary tables, temporary indexes, LOBs, and temporary undo can hold allocations with lifetimes that differ from an individual sort. A TEMP allocation does not necessarily disappear at the end of every statement.
V$SORT_SEGMENT can supplement the interpretation for sort segments. CURRENT_USERS and USED_BLOCKS concern active sorts. FREE_BLOCKS describes blocks available inside that sort segment. Use its documented scope when comparing it with the consumer and tablespace views. These changing views are observations taken at different instants, so separately executed queries need not form a perfectly simultaneous reconciliation. See V$SORT_SEGMENT.
Choose the next check
| Captured pattern | Useful next investigation |
|---|---|
| One statement dominates temporary allocation | Inspect its execution plan, joins, input row counts, and sort or hash operations. Determine whether the intermediate work is expected. |
| Many legitimate statements peak together | Compare concurrent demand with available TEMP and PGA work areas. Review overlap and scheduling. |
| Work areas show substantial temporary work or repeated passes | Inspect current work-area memory and pass evidence for the dedicated-server PGA workload, alongside the statement plan. |
| Expected demand exceeds the available configuration | Verify file limits and real storage capacity, then plan bounded growth or workload changes. |
| Low current demand but a large retained file | Inspect reusable space and expected future peaks before planning physical reclaim. |
For a captured statement, this query exposes current work-area evidence. The columns describe dedicated-server processes, whose private work areas are in the PGA. Shared-server memory placement, and RAC or parallel-execution attribution, need additional handling.
SELECT sid, sql_id, operation_type,
ROUND(actual_mem_used / 1048576, 2) AS pga_mib,
number_passes,
ROUND(tempseg_size / 1048576, 2) AS temp_mib
FROM v$sql_workarea_active
WHERE sql_id = '5h2m9c6n1p8q3';
ACTUAL_MEM_USED reports PGA bytes allocated for that work area. NUMBER_PASSES reports its execution passes. Zero corresponds to optimal execution. TEMPSEG_SIZE is null when the work area has not yet spilled. This is another instantaneous view, and temporary-table allocations need not map to an active sort work area. Review the evidence and the memory configuration before changing a memory target.
With the required dictionary access, V$SQL exposes cursor text and V$SQL_PLAN exposes plan operations for the captured identifier. Preserve the child-cursor and plan context. A SQL identifier can have multiple children. A plan's estimates are not measured row counts. Establish which operation and workload need investigation before detailed plan tuning. See V$SQL_WORKAREA_ACTIVE, Tuning the PGA, V$SQL, and V$SQL_PLAN.
Internal reuse and physical shrinking
Making a sort's extents reusable is an internal space-management action. Returning physical space requires a separate operation. Oracle supports shrinking locally managed temporary tablespaces and individual tempfiles. A KEEP value specifies a minimum retained size. The operation attempts to free what it can, so measure the actual result.
Before a shrink, inspect active use, assignments, groups, the full file inventory, the actual destination, expected peaks, and the size that should remain. Retain enough capacity for normal concurrency. Recheck file sizes and the workload after the operation. Avoid repeated shrink-and-regrow cycles driven by one quiet snapshot. Do not delete a tempfile from the filesystem to reclaim Oracle-managed capacity. See temporary tablespaces, space reuse, and shrinking and ALTER TABLESPACE.
Recap
Separate the tempfile from the temporary allocations inside it. File size, maximum growth, and the free-space view answer different questions. ALLOCATED_SPACE and FREE_SPACE both include reusable allocated space, so TABLESPACE_SIZE minus ALLOCATED_SPACE is not the entire usable free space. A file created at 64 MiB with a 256 MiB maximum can remain at a grown size after the report finishes.
While the work is running, V$TEMPSEG_USAGE attributes current temporary blocks to a user, the creating SQL identifier, a segment type, and a tablespace. Convert BLOCKS with that tablespace's BLOCK_SIZE. SQL_ID_TEMPSEG names the statement that created the segment, which can differ from the session's current SQL and from the view's separate SQL_ID column. Join V$SESSION on SADDR and SERIAL#. The serial number distinguishes sessions that reuse a SID. ACTIVE means the session is executing SQL at that moment.
After the work releases its allocation, the usage row can disappear while the extents stay reusable in the sort segment. An empty later snapshot cannot reconstruct the earlier demand. Use the captured pattern to choose the next check: the plan and its inputs, concurrent demand, work-area passes for a dedicated-server PGA, file limits and real storage, or reusable space before any physical shrink. Shrinking a locally managed temporary tablespace or tempfile is separate from internal reuse. KEEP names the minimum size to retain. Measure the result.
No comments:
Post a Comment