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     - Docker Basic and Advanced (Linux)

- Connect to the Oracle Database Help class (Database)

- Oracle 11gr2 new APPEND_VALUES tips (Database)

- RHEL5 stalled due to power service error system can not start (Linux)

- The virtual memory (Linux)

- C ++ constant definition (Programming)

- The strings in Python reside (Programming)

- sed command (Linux)

- Based shell: using read, the command-line script to achieve mass participation and input two integer calculation (Programming)

- Python Basics Tutorial - lambda keyword (Programming)

- Use MD5 transform algorithm to prevent exhaustive decipher passwords (Linux)

- CentOS 6.5 installation VNCServer implement graphical access (Server)

- Linux vi command list (Linux)

- Linux kernel source code analysis (Linux)

- Linux system Passwd file detailed analysis (Linux)

- grep search from files and display the file name (Linux)

- Linux install and configure Heartbeat (Server)

- Hibernate Performance Optimization of EHCache cache (Programming)

- The multiplexed signal driving IO (Programming)

- Use GLOBK batch command network adjustment (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
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
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------------------
<========= 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:
    SELECT *
    FROM Oracle.employees;
  table_set emp_row;
  - Assign values ​​to nested table of records:
    BULK COLLECT INTO table_set
    FROM oracle.employees;
  - Print nested table of records:

    FOR i IN table_set.FIRST .. table_set.LAST LOOP
        table_set (i) .email || '' ||
        table_set (i) .last_name || ',' ||
        table_set (i) .first_name

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

--- ------------ ----------------------------------- ------------- ---------------------- --------------- --------
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%';

-------------------------------------------------- -------------- ------------
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)
- Java rewrite the hashcode method (Programming)
- Linux in order to make NMAP hide and seek with the firewall (Linux)
- Binary tree traversal: the first sequence in order preorder recursive and non-recursive and traversal sequence (Programming)
- GEC2440 easiest a kernel compile Linux2.6.30.4 (Programming)
- Linux iptables firewall and vsftpd to resolve the issue (Linux)
- Learning how to teach safety system to prevent your own IP leakage (Linux)
- Let OpenCV face detection score output codes (Programming)
- MySQL Data Types (Database)
- Linux data recovery software efficiently practical application extundelete (Linux)
- MySQL migration tool used in the production environment (Database)
- Let your PHP 7 faster the Hugepage (Linux)
- You need to know 12 Git High Command (Linux)
- Oracle set the timer task JOB scheduler to execute stored procedures or PL / SQL code block (Database)
- How to use Linux iptables tool for network sharing (Linux)
- Upgrading KDE Plasma 5.3 in Ubuntu 15.04 (Linux)
- mysqldump issue a note (Database)
- NFS installation process under the CentOS (Linux)
- Oracle 11g to create a second instance on Linux (Database)
- The Linux-based security settings Ipchains Firewall (Linux)
- Sorting Algorithm (1) Quick Sort C ++ implementation (Programming)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.