A SQL sort or hash join needs working memory to arrange or match rows. This lesson explains where that memory lives, why an operation may use temporary storage, and how an administrator can read PGA pressure over time.
Oracle DBA Lesson 10 Part 2 — Why Sorts Use Temporary Disk
Private working memory
The program global area, or PGA, is private memory belonging to a server process. In the dedicated-server example, two processes have separate PGA allocations in RAM. A SQL workarea is an allocation inside this private memory for a memory-intensive operation. A sort workarea holds rows and sorting structures. A hash join builds a lookup structure from one input and probes it using join keys from another. See memory architecture.
A workarea is one use of PGA memory. Other consumers include execution state and application-language memory. Shared-server session-memory placement is a separate topic.
Follow a sort from RAM to TEMP
Suppose a report needs rows in a required order and its execution plan contains a sort operator. If the rows and supporting structures fit in the available workarea, the operation runs optimally in memory. Larger demand can require extra passes. One-pass and multipass executions use additional processing and can increase response time. See PGA tuning.
The video's simplified external-sort case follows sorted row batches:
- Sort a batch in private RAM. Call that sorted run R1.
- Write R1 to a physical temporary file on disk.
- Reuse the RAM workarea for another batch and save another run, R2.
- Read saved runs back into RAM and merge their rows into the final order.
The run symbols represent groups of sorted rows. They are conceptual objects, rather than Oracle table blocks. Actual run sizes, buffering, and pass counts depend on the operation. Large sorts can write to disk and subsequently read that data back through direct I/O. See direct path reads.
TEMP is a temporary tablespace. Its storage consists of physical temporary files, or tempfiles. TEMP is a common name, and the assigned temporary tablespace can have another name. Its transient space supports operations such as sorts that exceed available working memory. Completing a sort does not delete the physical tempfile. See storage structures and temporary tablespaces.
Target and limit
| Parameter | Useful interpretation |
|---|---|
PGA_AGGREGATE_TARGET | Target for aggregate PGA memory. With automatic workarea management, Oracle adjusts workarea allocations as demand changes. Total allocation can exceed the target. |
PGA_AGGREGATE_LIMIT | Aggregate enforcement threshold. Exceeding it can cause calls and eligible sessions to be terminated. |
The instance's memory budget serves workareas and other PGA consumers. Concurrency affects available memory for each operation. Under automatic memory management (MEMORY_TARGET positive), a positive PGA_AGGREGATE_TARGET also acts as a minimum for instance PGA sizing. The video's budget example uses automatic PGA workarea sizing without that AMM case. See PGA_AGGREGATE_TARGET.
Limit enforcement first terminates calls of sessions using the most untunable memory. If usage remains above the threshold, Oracle can terminate those sessions. Parallel queries are treated as a unit. SYS processes and background processes other than job queue processes are exempt from those actions. Their high usage can be reported to trace files. A limit of zero disables aggregate-limit enforcement. This parameter is an aggregate threshold, rather than an equal allowance assigned to each session. See PGA_AGGREGATE_LIMIT.
Inspect allocation and pressure
In SQL*Plus, the SQL statement selects the statistics. The client command displays configured parameters:
SELECT name, value, unit
FROM v$pgastat
WHERE name IN ('total PGA allocated','maximum PGA allocated',
'over allocation count','extra bytes read/written')
ORDER BY name;
SHOW PARAMETER pga_aggregate| Statistic | Time meaning |
|---|---|
total PGA allocated | Allocation now, across the instance. |
maximum PGA allocated | Highest allocation reached since startup. |
over allocation count | Cumulative over-allocation occasions since startup. |
extra bytes read/written | Cumulative bytes processed in extra passes since startup. |
Read VALUE together with UNIT. Byte values can be converted to MiB by dividing by 1024 twice. The over-allocation row is a count and may show a blank unit. Startup establishes the baseline for these cumulative statistics. See V$PGASTAT.
| Statistic | Observation 1 | Observation 2 | Interpretation |
|---|---|---|---|
| Extra bytes read/written | 104857600 bytes | 157286400 bytes | Increase of 52428800 bytes, or 50 MiB. |
| Over allocation count | 4 | 4 | No new over-allocation occasion in this interval. |
Both observations belong to the same uninterrupted instance uptime. Separately, a current allocation of 157286400 bytes is 150 MiB. A startup peak of 268435456 bytes is 256 MiB. These figures concern the whole instance and can reflect different operations.
The interval establishes increased extra-pass processing. Relate it to the report's SQL operator, input size, simultaneous activity, and host RAM. Workarea statistics can identify which operator used temporary space. PGA advisory information can inform a proposed target adjustment. See PGA monitoring and tuning.
A restart invalidates the shared cumulative baseline.
Recap
A sort workarea is private PGA memory in the server process. When the rows and supporting structures fit, the sort runs in that workarea. When they do not, Oracle can write sorted runs to temporary files and merge them later. TEMP is a temporary tablespace of physical tempfiles, and completing a sort does not delete the tempfile. PGA_AGGREGATE_TARGET guides aggregate PGA allocation and can be exceeded. PGA_AGGREGATE_LIMIT is a separate threshold that can terminate calls and eligible sessions, with exemptions for SYS processes and background processes other than job queue processes. V$PGASTAT shows the current allocation, the highest allocation since startup, and cumulative over-allocation and extra-pass bytes. Compare those cumulative counters during the same uninterrupted uptime.
No comments:
Post a Comment