Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ pga_aggregate_target and _pga_max_size can not use absolute limit actual PGA     - Ubuntu way of decompressing files (Linux)

- Linux, Oracle listen address modification (Database)

- How to use Monit to deploy a server to monitor the system (Server)

- GO five stages of language learning (Programming)

- Linux initialization init systems - Systemd (Linux)

- C # DateTime structure common method (Programming)

- RegExp object implements regular match --JavaScript (Programming)

- 30 Practical Linux system administrators will learn the command (Linux)

- DRBD-based installation and configuration of CentOS 6.5 (Server)

- MySQL optimization resulting order by using filesort (Database)

- Android thread mechanism --AsyncTask (Programming)

- Android design patterns - state mode (Programming)

- Linux (RHEL6 CENTOS6 OLE6) VNC-SERVER Installation and Configuration (Server)

- Linux, MySQL / MariaDB Galera Cluster Setup Process (Database)

- CentOS installation Docker series (Linux)

- Shell Scripting early experience (Programming)

- Graphical development environment to build Android under Ubuntu 11.04 (Linux)

- How to clear the v $ archived_log view expiration information (Database)

- Java eight new features 8 (Programming)

- How do you prevent other users from accessing your home directory in Linux (Linux)

 
         
  pga_aggregate_target and _pga_max_size can not use absolute limit actual PGA
     
  Add Date : 2018-11-21      
         
         
         
  And sga_target different, pga_aggregate_target does not limit the size of the PGA. Another difference is that, SGA is the database startup according sga_max_size pre-assigned, and PGA is the "needs" of.

      Following a detailed explanation of why pga_aggregate_target does not limit the size of the PGA. PGA general document will be divided into two parts of stack space and UGA, but the wording was too general and does not help us understand the problem. We put it into part of the work area and the work area outside.

      The so-called work area, is the session to execute SQL, the database needs internal distribution, in order to store the intermediate results of memory. For example sort area, in order to sort by memory, such as hash area, in order to use memory hash join, this part directly related to the implementation of SQL, affect the efficiency of SQL execution, such as a larger hash area will hash join faster. pga_aggregate_target actually only limit the size of the work area. When the limit of pga_aggregate_target work area is reached, it will generate an error 4030. Implicit parameter _pga_max_size just limit the size of work area using a single process, and it is less confined within the limits of the pga_aggregate_target. The memory work area outside, it is not limited pga_aggregate_target and _pga_max_size. So you will often see the PGA size exceeds pga_aggregate_target. This is usually because PL / SQL variables and arrays in data loaded into a huge result. A common example is the bulk collect, imp / exp, sql loader and other tools also have similar problems.

      Here I give a specific example:
alter system set "_pga_max_size" = 10m scope = both;
alter system set pga_aggregate_target = 100m scope = both;
SQL> shutdown immediate
startup
SQL> select ksppinm as "hidden parameter", ksppstvl as "value" from x $ ksppi join x $ ksppcv using (indx) where ksppinm like '% _pga_max_size%' order by ksppinm;
hidden parameter
-------------------------------------------------- ------------------------------
value
-------------------------------------------------- ------------------------------
_pga_max_size
10485760
<========= Restrict the use of a single process PGA 100M (in fact, work area)

Execute a PL / SQL, use bulk collect memory usage so that it greatly exceeds pga_aggregate_target and _pga_max_size:
DECLARE
  CURSOR c1 IS
    SELECT *
    FROM Oracle.employees;
  TYPE emp_row IS TABLE OF c1% ROWTYPE;
  table_set emp_row;
BEGIN
  - Assign values ​​to nested table of records:
  SELECT *
    BULK COLLECT INTO table_set
    FROM oracle.employees;
  - Print nested table of records:

    FOR i IN table_set.FIRST .. table_set.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        table_set (i) .email || '' ||
        table_set (i) .last_name || ',' ||
        table_set (i) .first_name
      );
    END LOOP; END;
/

select pid, spid, program, pga_used_mem / 1024/1024, pga_alloc_mem / 1024/1024 from v $ process
where spid = 3735758

PID SPID PROGRAM PGA_USED_MEM / 1024/1024 PGA_ALLOC_MEM / 1024/1024
--- ------------ ----------------------------------- ------------- ---------------------- --------------- --------
19 3735758 oracle @ nascds5 (TNS V1-V3) 616.22734165191650390625 616.63962650299072265625
"========= PGA uses a 600M

select a.name, to_char (b.value, '999,999,999') value
from v $ statname a, v $ mystat b
where a.statistic # = b.statistic #
and a.name like '% ga memory%';

NAME VALUE
-------------------------------------------------- -------------- ------------
session uga memory 1,896,024
session uga memory max 1,896,024
session pga memory 2,330,120
session pga memory max 647,400,968 <========== Peak PGA size for the session. 647,400,968


12c PGA_AGGREGATE_LIMIT added a new parameter to limit the size of PGA:
Limiting process size with database parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1)
     
         
         
         
  More:      
 
- Terminal multiplexing tool tmux use (Linux)
- Oracle table space rename and delete table space (Database)
- SteamOS installation under Ubuntu 14.04 (Linux)
- MySQL Online DDL tools of pt-online-schema-change (Database)
- CentOS 6.5 dual card configuration, one of the external network, a local area network connection (Linux)
- Impact test noatime Linux file access time (Linux)
- Web cache basics: terminology, HTTP headers, and caching policies (Server)
- To compile and install Clang 3.5.0 in Linux CentOS (Linux)
- AIX system file security aspects (Linux)
- Linux command execution judge sentences -;, &&, || (Linux)
- Hibernate Search 5.5 in the search results sorted (Linux)
- Python objects (Programming)
- Oracle Migration partition table (Database)
- Terminal Linux command prints - echo (Linux)
- Slice MyCAT common fragmentation rules of enumeration (Database)
- Linux novice common commands (Linux)
- Linux foreground to background process switch (Linux)
- Apache2.4.7 make an error [exports.lo] Error 1 Solution (Server)
- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)
- Installing Linux and Windows 10 dual system (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.