oracle中的SGA和PGA

oracle中的SGA和PGA

一、SGA(system global area )

SGA可以认为是instance中的全局共享区，Instance是由SGA和processes（进程）组成。

官方文档中的解释：
     A system global area (SGA)(系统全局区) is a group of shared memory structures that contain data and control information for one Oracle database instance(实例). If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently(因此), the SGA is sometimes called the shared global area(共享全局区).

     An SGA and Oracle processes constitute(构成) an Oracle instance. Oracle automatically allocates(分配) memory for an SGA when you start an instance, and the operating system reclaims(收回) the memory when you shut down the instance. Each instance has its own SGA.

     The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

查看SGA：

    SQL>show parameter sga
    SQL>show sga
    SQL>select * From v$sga;

二、PGA(Process Global Area 或Private Global Area )

      PGA可以认为是为连接上数据库的用户开的一个私有内存区。PGA包含一个服务进程的数据和控制信息，在一个服务进程启动时创建

     1 查看PGA
        show parameter pga;

       

2 修改PGA
         alter system set pga_aggregate_target=90M scope=both;

 

其中：
    pga_aggregate_target是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。