ORACLE: How much memory does one user occupy when connecting to the server? How to adjust it?

The client can connect to the server only after creating a connection with the server. At this time, the server needs to allocate certain resources to the user for connection, that is, PGA. So how does Oracle manage PGA? How many resources does a PGA need to be allocated to the server? How can we adjust this allocation?

PGA is the sum of resources allocated by the server to manage all user connections. when a user connects to the server, Oracle allocates a certain amount of memory from the PGA to form a UGA, that is, a connection corresponds to a Uga,

SQL> select * from V $ version;

Banner
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
PL/SQL release 9.2.0.1.0-Production
Core 9.2.0.1.0 Production
TNS for 32-bit windows: Version 9.2.0.1.0-Production
Nlsrtl version 9.2.0.1.0-Production

PGA size:

SQL> show parameter PGA

Name type value
---------------------------------------------------------------------
Pga_aggregate_target big integer 16777216

The sum of PGA used by all sessions is determined by two parameters.

One is pga_aggregate_target, and the other is:

SQL> show parameter workare

Name type value
---------------------------------------------------------------
Workarea_size_policy string auto

The PGA and UGA used by the current session:

SQL> select a. Name, B. Value/1024/1024 Value
2 from V $ statname A, V $ mystat B
3 where a. Statistic # = B. Statistic #
4 * and A. name like '% ga memory % ';

NAME value
--------------------------------------------------------------------------
Session UGA memory. 073394775
Session UGA memory Max. 135826111
Session PGA memory. 194011688
Session PGA memory Max. 256511688

If workarea_size_policy is auto, sort area will be meaningless.

Prior to this parameter, DBA should adjust sort_area_size, hash_area_size, bitmap_merge_area_size, and create_bitmap_area_size to optimize performance and PGA memory consumption. The adjustment of these parameters is very troublesome, because it is necessary to consider all the relevant operations, so that the workspace is suitable for their input data size, but also make the PGA memory consumption not too large, leading to the overall system performance decline.

After 9i, The pga_aggregate_target parameter is set to make the workspace size of all sessions automatically allocated. All * _ area_size parameters are invalid. At any time, the total amount of PGA memory that can be used in the instance's workspace is based on the pga_aggregate_target parameter. The total memory in the workspace is equal to the value of the pga_aggregate_target parameter minus the memory consumption of other system components (such as the PGA memory allocated to the session. The PGA memory size allocated to Oracle processes depends on their memory requirements.