Program Global Area (PGA) The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Each server process has a distinct PGA. Access to it is exclusive to that the server process is read only by Oracle code acting on behalf of it. It is not available for developer's code. Every PGA contains stack space. In a dedicated server environment, each user connecting to the database instance has a separate server process. For this type of connection, the PGA contains a subdivision of memory known as the user global area (UGA). The UGA is composed of the following: • Cursor area for storing runtime information on cursors • User session data storage area for control information about a session • SQL working areas for processing SQL statements consisting of: - A sort area for functions that order data such as ORDER BY and GROUP BY - A hash area for performing hash joins of tables - A create bitmap area used in bitmap index creation common to data warehouses - A bitmap merge area used for resolving bitmap index plan execution In a shared server environment, multiple client users share the server process. In this model, the UGA is moved into the SGA (shared pool or large pool if configured) leaving the PGA with only stack space. 现在做个测试： SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for 32-bit Windows: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> desc v$fixed_table Name Type Nullable Default Comments --------- ------------ -------- ------- -------- NAME VARCHAR2(30) Y OBJECT_ID NUMBER Y TYPE VARCHAR2(5) Y TABLE_NUM NUMBER Y SQL> select o.name from v$fixed_table o where o.name like 'V$PGA%'; NAME ------------------------------ V$PGASTAT V$PGA_TARGET_ADVICE_HISTOGRAM V$PGA_TARGET_ADVICE SQL> select * from v$pgastat; NAME VALUE UNIT ---------------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 444596224 bytes aggregate PGA auto target 364723200 bytes global memory bound 88919040 bytes total PGA inuse 39342080 bytes total PGA allocated 48480256 bytes maximum PGA allocated 120027136 bytes total freeable PGA memory 0 bytes process count 37 max processes count 46 PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 8128512 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 1963648000 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 12655 19 rows selected SQL> show parameter cursor NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_bind_capture_destination string memory+disk cursor_sharing string EXACT cursor_space_for_time boolean FALSE open_cursors integer 300 session_cached_cursors integer 50 SQL> show parameter _area NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 131072 sort_area_retained_size integer 0 sort_area_size integer 65536 workarea_size_policy string AUTO
