\ud83e\udde0 Introduction<\/strong> Think of it like a developer having their own laptop (PGA), while the whole team shares a server (SGA).<\/p>\n\n\n\n \ud83e\udde9 What is the PGA?<\/strong> It holds session-specific data and control information that only<\/strong> that process can access.<\/p>\n\n\n\n \ud83e\udde0 PGA Components (Dedicated Server Mode)<\/strong><\/p>\n\n\n\n Used during SQL execution for memory-intensive operations:<\/p>\n\n\n\n Stores session-specific<\/strong> information such as:<\/p>\n\n\n\n \ud83d\udcdd In dedicated server mode<\/strong>, UGA is part of the PGA. Each SQL statement gets its own private workspace<\/strong> to store:<\/p>\n\n\n\n It has two main areas:<\/p>\n\n\n\n \ud83d\udd01 Cursors<\/strong> act as pointers to Private SQL Areas, helping the database track query execution for each session.<\/p>\n\n\n\n \ud83d\udd04 PGA in Shared Server Mode<\/strong><\/p>\n\n\n\n In shared server architecture<\/strong>:<\/p>\n\n\n\n This architecture helps scale to more users with fewer server processes, conserving memory.<\/p>\n\n\n\n \ud83d\udee0\ufe0f DBA Tip of the Day<\/strong> \ud83d\udd0d Show PGA Parameters<\/p>\n\n\n\n \u2705 Query: View PGA Memory Statistics<\/p>\n\n\n\n \u2705 Query: View SQL Work Area Histogram<\/p>\n\n\n\n Program Global Area (PGA) \ud83e\udde0 IntroductionThe Program Global Area (PGA) is like a private workspace for each Oracle process.Unlike the SGA, which is shared by all, the PGA is nonshared \u2014 dedicated to a single server or background process. Think of it like a developer having their own laptop (PGA), while the whole team shares […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[960,952],"class_list":["post-3680","cposts","type-cposts","status-publish","hentry","category-getting-started-with-oracle-dba","category-notes"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3680","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
The Program Global Area (PGA)<\/strong> is like a private workspace for each Oracle process.
Unlike the SGA, which is shared by all, the PGA is nonshared<\/strong> \u2014 dedicated to a single server or background process.<\/p>\n\n\n\n
The PGA<\/strong> is a memory region created when a server process<\/strong> or background process<\/strong> starts and is automatically deallocated when the process ends.<\/p>\n\n\n\n\ud83d\udd39 1. SQL Work Areas<\/strong><\/h3>\n\n\n\n
\n
ORDER BY<\/code>, GROUP BY<\/code>, etc.<\/li>\n\n\n\n\ud83d\udd39 2. User Global Area (UGA)<\/strong><\/h3>\n\n\n\n
\n
In shared server mode<\/strong>, UGA moves to the Large Pool<\/strong> (in the SGA).<\/p>\n\n\n\n\ud83d\udd39 3. Private SQL Area<\/strong><\/h3>\n\n\n\n
\n
\n
\n
\u2714 SQL Work Areas
\u2714 Private SQL Area<\/li>\n<\/ul>\n\n\n\n
Want to reduce sorting or memory-intensive slowdowns?
Tune your PGA using the PGA_AGGREGATE_TARGET<\/code> or PGA_AGGREGATE_LIMIT<\/code> parameters.<\/p>\n\n\n\nSHOW PARAMETER pga;<\/code><\/pre>\n\n\n\nCOLUMN name FORMAT A40\nCOLUMN value FORMAT 9999999999\n\nSELECT * FROM v$pgastat;<\/code><\/pre>\n\n\n\nCOLUMN operation FORMAT A30\nCOLUMN onepass FORMAT 999999\nCOLUMN multipass FORMAT 999999\nCOLUMN optimal FORMAT 999999\nCOLUMN memory_used_in_kb FORMAT 999999999\n\nSELECT * FROM v$sql_workarea_histogram;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"