ORACLE PGA Introduction

Understanding the PGA

PGA (Program Grlobal areas or process global area) refers to the private memory space of the service process, including the service process global variables, data structure and control information, such as cursors for the service process SQL query, the PGA consists of the following structures:

Private SQL Zone:

The private data and control structures required by the storage service process to execute SQL, including the fixed area and the run area (Runtimearea). The fixed-area data persists until the SQL cursor is closed and the run zone is present at SQL execution (such as when insert, delete, and update execution are complete, and the SELECT statement frees the run area only if all query result sets get completed or canceled). For standalone mode links, the private SQL zone is allocated in the PGA, while the shared-mode link private SQL area is allocated in the SGA.

Session space:

Save session control information such as user logon information. For shared mode, the session information is shared.

SQL Workspace (SQL workarea):

for complex SQL, multiple table queries are required, and additional areas may be required such as:

Sort Operations

HASH Jion

Bitmap links

Creating bitmaps

Multi-table Hash link

......

In Oracle server, where the PGA space is independent of the SGA, the SQL workspace in the PGA has a significant impact on the performance of the database, and a well-configured PGA can be very helpful in tuning the system.

PGA is independent of the SGA memory space, mainly for the implementation of SQL, table connection, sorting operations, generally according to the system configuration and application software, you can use the 15%--30% of physical memory for the PGA space.

The sort area operation requires allocating memory space, which is called Sort_area. The Sort_area is automatically managed in a system that is automatically managed with the PGA, otherwise the Sort_areaz_size parameter is assigned the maximum value for each session using Sort_area.

With V$sort_segment, you can view the usage space for the temporal table space sort segment.

 sql> selecttablespace_name, extent_size, total_extents, used_extents, free_ extents,max_used_size  2  from v$sort_segment; tablespace_name                  extent_size total_ Extentsused_extents free_extents max_used_size------------------------------------------ ------ ------- ------------ ------------ -------------temp                                      128             58             0          58            &nbsP; 9 

See how much temporary space is currently being used by a database user and can be implemented by looking at V$sort_usage and v$session:

Sql>select S.username, U.tablespace, U.contents, U.extents, U.blocks 2 from v$session S, v$sort_usage u 3 whe  Re s.saddr = u.session_addr; PGA Pga_aggregate_target

PGA Pga_aggregate_target

sql>show parameters pga name                                   TYPE         VALUE----------------------------------------------- ------------------------------Pga_aggregate_target                  big Maximum space  INTEGER 800MPGA can use sql>show parameters workarea_size name                                   TYPE         value----------------------------------------------- ------------------------------ workarea_size_policy                 string      Auto Assign SQL Workspace rules,  auto or mannualsql>show parameters sort_area_size name                                   TYPE         value----------------------------------------------- ------------------------------Sort _area_size                        integer     65536 defines the maximum value of the SQL sort area for each session sql> show parameters hash_area_size name                                   TYPE        value----------------------------------------------- ---------- --------------------hash_area_size                        integer      131072 defines the maximum memory space for each session hash connection sql>show parameters bitmap_merge_area_size name                                   TYPE         value----------------------------------------------- ------------------------------ bitmap_merge_area_size                integer     1048576 defines the maximum value of the memory work area when each session uses a bitmap merge connection sql>show parameters  Create_bitmap_area_size name                                  TYPE         value----------------------------------------------- ---------------------- --------create_bitmap_area_size               integer     8388608 defines the maximum number of memory work areas that can be used when creating bitmaps per session

If Workarea_size_policy is set to auto, you do not need to set a value for *_area_size, and the PGA automatically manages to automatically allocate the memory size for each workspace. After oracle10g, the shared mode connection can also be set workarea_size_policy automatic management.

Some references for adjusting pga_aggregatge_target parameters can be obtained by v$sgastat

sql>select * from v$pgastat; name                                                                     value unit-------------------------------------------------------------------------- --- ---------aggregate pga targetparameter                                     838860800 bytesaggregate PGA autotarget                                          531348480  bytesglobal memory bound                                                 104857600 bytestotal pga inuse                                                     248585216 bytestotal PGA allocated                                                319751168  bytesmaximum pga allocated                                              481471488 bytestotal  freeable PGAmemory                                           45678592 bytesprocess count                                                              67max  processes count                                                        96PGA memory freed back toOS                                       3.1422E+11 bytestotal PGA  used for autoworkareas                                           0 bytes name                                                                     value unit--------------------------------------------------------------------------  ------------maximum pga used for autoworkareas                                  56870912 bytestotal PGA used for  manualworkareas                                       0  bytesmaximum pga used for manualworkareas                                 542720 bytesover allocation count                                                       0bytes processed                                                    3.6840E+11 bytesextra bytes read/written                                                    0 bytescache hit percentage                                                      100 percentrecompute  count  (total)                                        &nBsp;      2761548 19 rows selected. 

AGGREGATEPGA Auto Target The adjustable PGA space, only this part of the space can be used by the Oracle Auto-tuning function, and for a variety of SQL work area, this part of the space should occupy the largest proportion of pga_aggregate_target, if too small, may cause performance problems.

TOTALPGA used for Auto Workarea

adjustable PGA space used by the system, Maxmum PGA used for auto Workarea displays the maximum value of the adjustable PGA after the system is started

TOTALPGA in used :

Current use of PGA space, this value is the same as the Pga_used_mem in V$process

cachehit Percentage: This value appears after oracle9i, which shows the performance of the PGA. A value of 100% indicates that the PGA space is sufficient.

When the PGA space is low, Oracle needs to use an external secondary space, combined with One-pass or Multi-Pass mode to complete the operation, the performance of the system will be affected, the worthy formula is:

Pgacache hit Ratio=total byte processed * [Total bytes processed + Totalextra bytes read/written)

Use the V$sql_workarea_histogram view to view the detailed usage of the PGA in the system, which lists

With the V$sal_workarea_active view, you can view the currently active PGA workspace

To be Continued ...

This article is from the "DBA Sky" blog, so be sure to keep this source http://kevinora.blog.51cto.com/9406404/1765483

ORACLE PGA Introduction