1Z0-052 · Question #11
Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)
The correct answer is A. Shared SQL Area will be allocated in the shared pool F. The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter. The shared SQL area resides in the shared pool of the SGA, and the number of private SQL areas per session is bounded by the OPEN_CURSORS parameter.
Question
Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)
Options
- AShared SQL Area will be allocated in the shared pool
- BShared SQL Area will be allocated when a session starts
- CShared SQL Area will be allocated in the large pool always
- DThe whole of Private SQL Area will be allocated in the Program Global Area (PGA) always
- EShared SQL Area and Private SQL Area will be allocated in the PGA or large pool
- FThe number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter
How the community answered
(47 responses)- A72% (34)
- B13% (6)
- C4% (2)
- D9% (4)
- E2% (1)
Why each option
The shared SQL area resides in the shared pool of the SGA, and the number of private SQL areas per session is bounded by the OPEN_CURSORS parameter.
The shared SQL area is a component of the library cache, which is allocated within the shared pool in the SGA, where parsed SQL and execution plans are stored for reuse across sessions.
The shared SQL area is allocated on demand when a SQL statement is first hard-parsed, not at session startup.
The shared SQL area is always in the shared pool, not the large pool - the large pool is reserved for shared server UGA, parallel query buffers, and RMAN I/O.
In a shared server architecture, the persistent area of the private SQL area is stored in the UGA within the SGA (shared pool or large pool), not always in the PGA.
The shared SQL area is allocated in the shared pool (part of the SGA), not in the PGA or large pool.
The OPEN_CURSORS initialization parameter sets the maximum number of cursors a session can hold open simultaneously, and each open cursor corresponds to one private SQL area allocation.
Concept tested: Oracle SGA memory areas - shared and private SQL area allocation
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html
Topics
Community Discussion
No community discussion yet for this question.