Also known as Process/Private Global Area is the private area attached to each individual user process. It works on behalf of the server process. It is created with the creation of server process and is terminated as soon as server process finishes serving that particular user process.
The number of PGAs is dependent on which mode your database server is running. When the database is in dedicated mode there is a PGA for each server process. But when the server is in shared mode then the PGA is only one and behaves like a container. The container PGA consists of individual UGAs (User Global Area) - one for each user process.
The lines below describe the Structure of Individual PGA/UGA.
The last two pieces of information are quite straightforward. Lets discuss what's held in Private SQL Area.
Private SQL area contains data manipulated by specific SQL being run at the time and is attached to shared pool of SGA. It consists of two areas: One is persistent area which contains the actual result values and the other is run-time area which consists of SQL work areas. SQL work areas are used for massive data manipulation like sorting, hashing and bitmap creation.
You can tune these areas individually with the help of parameters like sort_area_size, hash_area_size, bitmap_merge_area_size and create_bitmap_area_size. Setting these parameters is difficult because they are dependent on data - an highly volatile entity. Therefore for easy management Oracle provides the PGA_aggregate_size parameter. This parameter allows you to set the total size for PGA and Oracle will auto-tune everything else. You also need to provide the workarea_size_policy. This initialization parameter has only two values; manual or auto. The default is auto.
Starting from 11g you can set memory_target parameter to let Oracle decide the distribution between SGA and PGA - further simplifying the Memory management.
The number of PGAs is dependent on which mode your database server is running. When the database is in dedicated mode there is a PGA for each server process. But when the server is in shared mode then the PGA is only one and behaves like a container. The container PGA consists of individual UGAs (User Global Area) - one for each user process.
The lines below describe the Structure of Individual PGA/UGA.
- private SQL area,
- Cursor information and
- session information
The last two pieces of information are quite straightforward. Lets discuss what's held in Private SQL Area.
Private SQL area contains data manipulated by specific SQL being run at the time and is attached to shared pool of SGA. It consists of two areas: One is persistent area which contains the actual result values and the other is run-time area which consists of SQL work areas. SQL work areas are used for massive data manipulation like sorting, hashing and bitmap creation.
You can tune these areas individually with the help of parameters like sort_area_size, hash_area_size, bitmap_merge_area_size and create_bitmap_area_size. Setting these parameters is difficult because they are dependent on data - an highly volatile entity. Therefore for easy management Oracle provides the PGA_aggregate_size parameter. This parameter allows you to set the total size for PGA and Oracle will auto-tune everything else. You also need to provide the workarea_size_policy. This initialization parameter has only two values; manual or auto. The default is auto.
Starting from 11g you can set memory_target parameter to let Oracle decide the distribution between SGA and PGA - further simplifying the Memory management.
No comments:
Post a Comment