Oracle Memory Structure)

Source: Internet
Author: User
The memory structure of Oracle consists of SystemGlobalArea (SGA) and ProgramGlobalArea (PGA ). SGA: When an instance is started

The memory structure of Oracle consists of System Global Area (SGA) and Program Global Area (PGA ). SGA: When an instance is started

The memory structure of Oracle consists of System Global Area (SGA) and Program Global Area (PGA ).

SGA: allocated is a basic component of an instance when it is started.

PGA: allocated when a Server Process is started. Server Process.

Memory-> SGA

SGA is dynamic and can be set by the SGA_MAX_SIZE parameter. The increase and decrease of SGA have a basic unit granule.

In SQL plus, we can view the SGA allocation and granule.

SQL> show sga

SQL> select component, granule_size from

2 v $ sga_dynamic_components;

SGA also has its components. Here we mainly introduce Shared Pool, Large Pool and Java Pool. Of course, there are also some other buffer and cache, such as Data Buffer Cache. There is also a Stream Pool in Oracle11g.

Large Pool and Java Pool are optional memory structures in SGA. The former will be used in backup, IO processing and parallel operations, and the latter will be used in Java.

Memory-> SGA-> Shared Poll

The Shared Pool is mainly used to store the vast majority of recently executed SQL statements and recently used data definitions ). We can use SHARED_POOL_SIZE to set its size. Statement: alert system set SHARED_POOL_SIZE = 64 M; Shared Pool includes two main memory structures that affect performance: Library Cache and Data Dictionary Cache.

Libray Cache is used to store the vast majority of recently used SQL and PL/SQL statements, and to share recently used statements. It mainly includes Shared SQL area and Shared PL/SQL area. Its size is determined by the Shared Pool, which is managed by the Least recently used mechanism (Least recently used, LRU algorithm ).

Data Dictionary Cache is a set of definitions in most recently used databases, including database files, tables, columns, users, and permissions. It provides server process with object name resolution and access verification (validate access ). Its size is also determined by the Shared Pool.

The Shared Pool also stores data blocks and redo log buffering.

Memory-> PGA

Every user connected to the Oracle database will have their own PGA. It is created with the creation of the user process and ended with the termination of the user process.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.