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     - Lsblk command lists using Linux block device information (Linux)

- ORA-14400: inserted partition key does not map to any partition (Database)

- Simple RPM package production (Linux)

- Linux system on a virtual machine to access the Internet (Linux)

- Report generation CPU, memory, and input and output ports with the sar command (Linux)

- Use Oracle 11g show spparameter command (Database)

- Linux Workstation Security Checklist - from the Linux Foundation Internal (Linux)

- When RHEL7 use fdisk partition, all partitions can not be used (Linux)

- Linux Open coredump (Linux)

- namespace mechanism Linux kernel analysis (Linux)

- How to set cache valid time in Apache (Server)

- Fatal NI connect error 12170 error in Alert Log (Database)

- Linux Defensive / mitigate DDOS attacks (Linux)

- Linux firewall Iptables study notes (Linux)

- Using DOS command to change UNIX administrator password (Linux)

- CentOS 6.4 under PXE + Kickstart unattended operating system installation (Programming)

- Oracle 12C with multi-column index (Database)

- Nginx Keepalived Nginx monitoring scripts (Server)

- CentOS 7 Configure logging (VirtualBox) (Linux)

- Nginx load balancing configuration (TCP proxy) (Server)

 
         
  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:      
 
- To learn from scratch OpenWrt perfect tutorial (Linux)
- Quickly build and install Linux KVM system (Linux)
- Java Virtual Machine Basics (Programming)
- Python programming style (Programming)
- AngularJS notes --- Data Binding (Programming)
- OpenSSL: implementation creates a private CA, certificate signing request Explanation (Server)
- CentOS 6.5 start ActiveMQ being given to solve (Server)
- Android in the event delivery and handling mechanism (Programming)
- Wine 1.7 is installed on a system based on RedHat or Debian (Linux)
- Security measures under Unix multi-user operating system (Linux)
- CentOS set up FTP server (Server)
- Linux network monitoring tools ntopng installation (Linux)
- CentOS7 compile and install Tengine + PHP + MariaDB notes (Server)
- Oracle 12c detailing the new features (Database)
- Firewall settings oracle remote connection in Linux systems (Linux)
- Linux kernel programming parameter passing between modules and function calls (Programming)
- Use matplotlib scientific drawing in Linux (Linux)
- To install Xen in Ubuntu 12.04 (Linux)
- CentOS 6.5 three ways to configure the IP address (Linux)
- Findbugs installation documentation (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.