Direct path reading in PGA

[navaneethdba]

hi,

Can some one explain " Direct path reading in PGA" plz?

Thanx,

[davey23uk]

where did you pick up that term from

[navaneethdba]

Sir,

Thanx for ur reply..
Sorry sir it is "direct path read (single pass,multi pass) and sorting in PGA "
plz explain me abt when does 'direct path read' occurs?

Thank you,

[navaneethdba]

Sir,
Thanx for ur reply...
It is 'Direct path reading and sorting in PGA!!'
plz explain
1.when does this direct path reading(single pass)occurs in PGA ?
2.when does it uses temporary tablespace?
Thanx,

[liakat]

HELLO.........

db file sequential read (single block read into one SGA buffer)
db file scattered read (multiblock read into many discontinuous SGA buffers)
direct read (single or multiblock read into the PGA, bypassing the SGA)

Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous IO is supported (and in use) then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on "direct path read" until the required IO completes. If asynchronous IO is not being used then the IO requests block until completed but these do not show as waits at the time the IO is issued. The session returns later to pick up the completed IO data but can then show a wait on "direct path read" even though this wait will return immediately.

This happens in the following situations:

The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
Parallel slaves are used for scanning data.
The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.

INCREASE the SORT_AREA_SIZE, HASH_AREA_SIZE if WORKAREA_SIZE_POLICY is manual or increase PGA_AGGREGATE_TARGET is WORKAREA_SIZE_POLICY auto,
try to reduce full table scan.check DISK_ASYNCH_IO is TRUE,check t tablespace for unexpacted disk sort operation .

[navaneethdba]

hey, thanx a lot man...

[mike9]

In the online document "Oracle® Database Performance Tuning Guide" (chapter 7.3 and 10.3) it is well explained:


http://download.oracle.com/docs/cd/B...ory.htm#i49320

7.5 PGA Memory Management
The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it. An example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor
...

http://download.oracle.com/docs/cd/B....htm#sthref955

10.3.5 direct path read and direct path read temp
When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
...