UGA < PGA for dedicated server?
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Mon, 13 May 2019 18:23:34 +0000
Message-ID: <CY1PR0201MB07789713F89DA965C7FBADD3EB0F0_at_CY1PR0201MB0778.namprd02.prod.outlook.com>
In the Oracle Database 18c documentation<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/memory-architecture.html#GUID-61B574AB-8493-4D43-8B30-0050A30550AD>, I read: "The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state. ... The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/glossary.html#GUID-E94CE0E3-CC86-4F46-B8EF-54945F026326> connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/glossary.html#GUID-8B14C804-9D68-471C-A581-5AEE673A9FCD> connection, the UGA is stored in the PGA."
Date: Mon, 13 May 2019 18:23:34 +0000
Message-ID: <CY1PR0201MB07789713F89DA965C7FBADD3EB0F0_at_CY1PR0201MB0778.namprd02.prod.outlook.com>
In the Oracle Database 18c documentation<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/memory-architecture.html#GUID-61B574AB-8493-4D43-8B30-0050A30550AD>, I read: "The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state. ... The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/glossary.html#GUID-E94CE0E3-CC86-4F46-B8EF-54945F026326> connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server<https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/glossary.html#GUID-8B14C804-9D68-471C-A581-5AEE673A9FCD> connection, the UGA is stored in the PGA."
Here are some metrics from an Oracle Database 18c dedicated-server instance. It does not appear that "the UGA is stored in the PGA." What am I missing? Memory leaks? Timing issues in the monitoring functionality?
Instance Activity Stats - Absolute Values
- Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value logons current 160 174 opened cursors current 304 388 session cursor cache count 73,510 102,126 session pga memory 371,171,328 429,231,056 session pga memory max 517,122,688 996,809,616 session uga memory 889,632,888 1,231,680,896session uga memory max 5,794,278,744 7,998,201,224
Also, does AWR report the max(session pga memory max) and max(session uga memory max) from v$sesstat across all connected sessions when the snapshot was taken? The reason I ask is because sum(session pga memory max) and sum(session uga memory max) across all connected sessions don't seem very relevant to me becaus each max would happen at a different point in time.
Thanks in advance for any insights.
Iggy
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 13 2019 - 20:23:34 CEST