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     - CentOS 7.0 Enable iptables firewall (Linux)

- Iptables command in detail (Linux)

- Oracle Data File Management (Database)

- Oracle set and remove columns unavailable (Database)

- HA-Federation-HDFS + Yarn cluster deployment (Server)

- Using Java arrays implement sequential stack (Programming)

- There are three ways to run a Linux operating system from a USB stick (Linux)

- Linux operating system security settings initial understanding (Linux)

- Ubuntu 14.04 Nvidia graphics driver installation and settings (Linux)

- Puppet installation and testing (Server)

- Oracle Database Delete Delete million or more common method of heap table data (Database)

- VMware difference in three network connection (Linux)

- Installation and use the telnet command under CentOS 6.x (Linux)

- pga_aggregate_target and _pga_max_size can not use absolute limit actual PGA (Database)

- Subsequent binary search tree traversal sequence (Programming)

- MySQL time field based partitioning scheme summary (Database)

- SUSE Linux network configuration and firewall configuration (Linux)

- The hashcode method of Java (Programming)

- Python control multi-process and multi-threaded concurrency (Programming)

- How to fix apt-get update can not add a new CD-ROM error (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:      
 
- To batch create users under Linux (Linux)
- Linux operating system security settings initial understanding (Linux)
- Ubuntu will be written in a command file, executable file, source command (Linux)
- MySQL enabled SSD storage (Database)
- Compare Oracle MySQL (ICP) index condition pushdown is described (Database)
- Web cache basics: terminology, HTTP headers, and caching policies (Server)
- MongoDB 3.2 Cluster Setup (Database)
- After installation of Debian 6.0 do a few things first (Linux)
- Example of use WebSocket (Programming)
- High-performance JavaScript loops and flow control (Programming)
- Linux disk and File System Concepts (Linux)
- Node.JS different ways to install under Ubuntu 15.04 (Linux)
- RedHat / CentOS ext4 partition can not be formatted large supplementary ext4 formatting (Linux)
- How to run Kali Linux 2.0 in Docker container (Linux)
- Android project using the command to create and install the package (Programming)
- How to add a new hard disk without restarting the CentOS 7 / RHEL 7 virtual machine (Linux)
- Web database security tips (Linux)
- Efficient Linux Shell - Shell special characters Summary (Linux)
- ls command: 15 Level Linux interview question (Linux)
- Using Linux strace command trace / debug a program commonly used options (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.