Oracle程序员面试分类模拟11

Oracle程序员面试分类模拟11简答题1.

DATABLOCK，EXTENT和SEGMENT的区别是什么?正确答案：DATABLOCK是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理（江南博哥）存储空间时，连续的DATABLOCK就组成了EXTENT。一个数据库对象拥有的所有EXTENT被称为该对象的SEGMENT。

2.

在Oracle中，数据块、Redo日志块及控制文件数据块的大小分别是多少?如何查询?正确答案：这3种数据块分别介绍如下：

1)数据块(DataBlock)，是读写数据文件的最小单位，默认是8KB，可以通过SQL语句“SELECTFILE#,NAME，BLOCKSIZEFROMV$DATAFILE;”查询，单位为BYTE。

2)Redo日志数据块(RedoLogBlock)，大小一般等于操作系统的系统块的大小，一般为512或4096，可以通过SQL语句“SELECTBLOCKSIZEFROMV$LOG;”或“SELECTLEBSZFROMXSKCCLE;”查询，单位为BYTE。

3)控制文件数据块(ControlFileBlock)，默认为16KB，可以通过SQL语句“SELECTBLOCKSIZEFROMV$CONTROLFILE;”查询，单位为BYTE。

3.

行链接和行迁移有什么区别?正确答案：当一行的数据过长而不能存储在单个数据块中时，可能发生两种事情：行链接(RowChaining)或行迁移(RowMigration)。

1)行链接(RowChaining)：当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。在这种情况下，Oracle会使用与该块链接的一个或多个数据块来容纳该行的数据。行链接经常在插入比较大的行时才会发生，例如包含LONG、LONGROW、LOB等类型的数据。在这些情况下，行链接是不可避免的。行链接通常由INSERT操作引起。

2)行迁移(RowMigration)：当一个行上的更新操作导致当前的数据增加以致于不能再容纳在当前块，这个时候就需要进行行迁移，在这种情况下，Oracle将会迁移整行数据到一个新的数据块中。一个行迁移意味着整行数据都将会移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。发生行迁移的时候行的ROWID不会改变。行迁移的情况主要是由于表上的PCTFREE参数设置过小导致，所以必须设置一个合适的PCTFREE参数。可以使用exp/imp工具导入导出来处理行迁移。行迁移通常由UPDATE操作引起。

4.

什么是专用服务器和共享服务器?正确答案：在连接到Oracle数据库的时候，可以有两种连接模式，一种称为专用服务器连接(DedicatedServer)，另外一种称为共享服务器连接(SharedServer)。下面将分别讲解这两种连接方式的不同点。

专用服务器：每次在对Oracle进行访问的时候，Oracle服务器的Listener会得到这个访问请求，然后会为这个访问创建一个新的进程来进行服务。所以说，对于每一个客户端的访问，都会生成一个新的进程进行服务，是一种类似一对一的映射关系。这种连接模式的一个很重要的特点就是UGA(UserGlobalArea，用户全局区)是存储在PGA(ProgramGlobalArea，程序全局区)中的，这个特性也很好说明了当前用户的内存空间是按照进程来进行分配的。专用服务器连接模式是Oracle默认的连接模式。

共享服务器：也称为多线程服务器(Multi-ThreadedServer，MTS)。若采用共享服务器模式的话，则在数据库初始化的时候就会创建一批服务器连接的进程，然后把这些连接进程放入一个连接池来进行管理。初始化的池中的进程数量在数据库初始化建立的时候是可以手动设置的。在连接建立的时候，Listener首先接收到客户端的建立连接的请求，然后Listener去生成一个称为调度器(Dipatcher)的进程与客户端进行连接。调度器把客户端的请求放在SGA(SystemGlobalArea，系统全局区)的一个请求队列中，然后在共享服务器连接池中查找有无空闲的连接，然后让这个空闲的服务器进行处理。处理完毕以后再把处理结果放在SGA的相应队列中。调度器通过查询相应队列，得到返回结果，再返回给客户端。这种连接模式的优点在于服务器进程的数量可以得到控制，不大可能出现因为连接数过多而造成服务器内存崩溃。但是由于增加了复杂度以及请求相应队列，可能在性能上有所下降。

如果在前端使用了Weblogic的连接池，那么在数据库级别就没有必要再使用共享服务器了，因为这时的用户连接已经在Weblogic层面上得到了控制。其实专用服务器和共享服务器是可以并存的，即使使用了共享服务器，某些管理操作也是必须在专用服务器模式下来做的，比如STARTUP或SHUTDOWN。

(1)启用共享服务器

要切换到共享模式，可以使用以下步骤：

1)设置初始化参数SHAREDSERVERS大于0，然后重启数据库即可启动共享模式，SQL如下：

其中，SHARED_SERVERS指定了当实例启动的时候共享服务器进程启动的数量。如果这个参数的值为0，那么表示数据库没有启动共享服务模式。这个参数是配置共享服务器必需的，而且只有这个参数是必需的。MAX_SHARED_SERVERS是Oracle在同一个时刻最大能够使用的共享服务器进程数量。如果SHARED_SERVERS大于MAX_SHARED_SERVERS，那么Oracle会以SHARED_SERVERS参数的值覆盖MAX_SHARED_SERVERS。

2)设置DISPATCHERS参数，可以采用下面的方法设置：

其中，“PROTOCOL=TCP”表示的是协议，“SERVICE=lhrdbXDB”指定要采用共享服务器模式的服务名称。使用上面的模式指定只启动某个服务的共享模式，如果要设置所有服务都使用共享模式，则设置为：

3)客户端的tnsnames.ora的文件中，在CONNECT_DATA设置一项中增加“(server=shared)”一项，即可使用共享服务器连接，如下：

其中，SERVICE_NAME的值可以通过命令“lsnrctlservices＜listenername＞”来查看，如下：

如果服务器端没有启动共享服务器模式，而客户端使用shared方式连接的话，那么会出现错误提示：“ORA-12520:TNS:监听程序无法找到需要的服务器类型的可用句柄”。

如果同时把SHARED_SERVERS和MAX_SHARED_SERVERS都设置成0，那么共享服务器将关闭。

(2)判断会话的连接模式

可以从V$SESSION视图的SERVER列里查看：

若显示的结果中有NONE或SHARED，则说明当前启动了共享服务器模式。其中，状态为NONE的会话表示当前共享服务器进程没有任务处理，若进程正在执行某些任务则会表现为SHARED状态。如果只显示有DEDICATED，则不能说明服务器就一定工作在专用服务器下面，此时只能说明有可能启动了共享模式，但是无连接。

此外，还可以通过视图V$SHARED_SERVER、V$DISPATCHER及V$CIRCUIT视图查询是否启用了共享服务器连接。若V$SHARED_SERVER有记录，且STATUS字段为“WMT(COMMON)”，则说明启动共享；若STATUS为TERMINATED或者无记录，则说明没有启动共享服务器。V$DISPATCHER有无记录都不能说明启动共享服务器，只能说明是配置了DISPATCHERS参数。V$CIRCUIT有记录说明当前有使用共享模式的连接，无记录则不能判定服务器模式。其实通过命令“lsnrctlservices＜listenername＞”也可以判断是否启用了共享服务器连接。

当服务器采用专用服务器模式时，客户端只能使用专用模式连接，也就是在CONNECT_DATA数据中只能使用“SERVER=DEDICATED”。当服务器采用共享服务器模式时，客户端可以选择建立共享连接或是专用连接，只要在CONNECT_DATA中指定“SERVER=DEDICATED”或“SERVER=SHARED”。

5.

内存结构主要由什么组成?正确答案：Oracle内存结构主要分共享内存区与非共享内存区，共享内存区主要包含SGA(SystemGlobalArea，系统全局区)，非共享内存区主要由PGA(ProgramGlobalArea，程序全局区)组成，如图所示。

Oracle实例结构

(1)SGA介绍

SGA(SystemGlobalArea，系统全局区)是Oracle实例的基本组成部分，是Oracle为一个实例分配的一组共享内存缓冲区，保存着Oracle系统与所有数据库用户的共享信息，包括数据维护、SQL语句分析、Redo日志管理等。SGA是实例的主要部分，它在实例启动时分配。

SGA是动态的，由参数SGA_MAX_SIZE决定。查看当前系统的SGA可以使用的最大内存大小的命令是：SHOWPARAMETERSGA_MAX_SIZE。修改SGA最大内存大小的命令是：ALTERSYSTEMSETSGA_MAX_SIZE=1200MSCOPE=SPFILE。因为实例内存的分配是在数据库启动时进行的，所以，要让修改生效，必须重启数据库。当Oracle运行在32位Linux上时，其默认SGA无法超过1.7GB。

在Oracle10g中引入了ASMM(AutomaticSharedMemoryManagement，自动共享内存管理)，DBA只需设置SGA_TARGET，Oracle就会自动地对共享池、数据缓冲区、Redo日志缓冲区、大池、Java池和流池进行自动调配，取消自动调配的方法为设置SGA_TARGET为0。

需要注意的是，Oracle分配内存的单位是granule，即粒度。最小的粒度为4MB，设置大小不到一个粒度按一个粒度计算。在32位操作系统的平台上，粒度的最大值为16MB。粒度的大小在数据库实例周期内不能被修改。按照粒度为单位分配的组件包括SharedPool、BufferCache(以及不同大小块的BufferCache)、RedoLogBuffer、JavaPool、StreamsPool和LargePool。粒度的大小参考下表。

通过视图V$SGAINFO可以查询当前SGA分配的粒度大小，如下：

下面将对SGA的各个组成部分进行介绍。

1)共享池(SharedPool)：缓存了各用户间可共享的各种结构，例如，缓存最近被执行的SQL语句和最近被使用的数据定义。共享池主要包括：库缓存(LibraryCache)、数据字典缓存(DataDictionaryCache)、保留池(ReservedPool)和结果缓存(ResultCache)。

①库缓存(LibraryCache)是存放用户SQL命令、解析树和执行计划的区域。对于库缓存来说，具体包含以下几个部分：

共享SQL区(SharedSQLArea)：保存了SQL语句文本，编译后的语法分析树及执行计划。查看共享SQL区的使用率命令为：SELECT(SUM(PINS-RELOADS))/SUM(PINS)”LIBRARYCACHE”FROMV$LIBRARYCACHE;。

私有SQL区(PrivateSQLArea)：包含当前会话的绑定信息以及运行时内存结构。每个发出SQL语句的会话，都有一个PrivateSQLArea。当多个用户执行相同的SQL语句，此SQL语句保存在共享SQL区。若是共享服务器模式，则PrivateSQLArea位于SGA的SharePool或LargePool中。若是专用服务器模式，则PrivateSQLArea位于PGA中。

共享PL/SQL区(SharedPL/SQLArea)：保存了分析与编译过的PL/SQL块(存储过程、函数、包、触发器和匿名PL/SQL块)。

控制结构区(ControlStructureArea)：保存锁等控制信息。

②数据字典缓存(DataDictionaryCache)存放数据库运行的动态信息，例如表和列的定义，数据字典表的权限。查看数据字典缓冲区使用率的SQL为：“SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"DATADICTIONARYCACHE"FROMV$ROWCACHE;”。

③保留池(ReservedPool)也称为保留区域(ReservedArea)，是指SharedPool中配置的一个内存保留区域，这个保留区域用做当在普通的SharedPool列表中的空间不能用来满足LargeRequest的内存分配请求而分配大块的连续内存块。当一个内存请求大于隐含参数“_SHARED_POOL_RESERVED_MIN_ALLOC”(默认：4400bytes，如果系统经常出现ORA-04031错误，基本上都是请求大于4400的内存块，那么就可能需要增加SHARED_POOL_RESERVED_SIZE参数设置)的值时就是一个LargeRequest，反之当内存请求小于“_SHARED_POOL_RESERVED_MIN_ALLOC”时就是一个SmallRequest。另外关于ReservedPool还有两个参数需要关注一下，一个是SHARED_POOL_RESERVED_SIZE，另外一个是隐含参数“_SHARED_POOL_RESERVED_PCT”(默认：5%)。通过SHARED_POOL_RESERVED_SIZE可以为ReservedPool指定一个大小，也可以通过“_SHARED_POOL_RESERVED_PCT”来为SharedPool指定一个比例。如果这两个参数同时设置了，那么就会以“_SHARED_POOL_RESERVED_PCT”为准。参数SHARED_POOL_RESERVED_SIZE的缺省值是SHARED_POOL_SIZE的5%，最小值为5000bytes，最大不得超过SHARED_POOL_SIZE的50%。通过视图V$SHARED_POOL_RESERVED可以查到保留池的统计信息。其中字段REQUEST_MISSES记录了没有立即从空闲列表中得到可用的大内存段请求次数，这个值理想状态下要为0。当REQUEST_FAILURES大于0时，则需要增加SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE的空间。当REQUEST_MISS等于0，或是FREE_MEMORY大于等于SHARED_POOL_RESERVED_SIZE的空间时，则增加SHARED_POOL_RESERVED_SIZE的空间。MAX_USED_SPACE字段可以用来判断保留池的大小是否合适。保留区使用SharedPool的LRU链表来管理内存块。可以通过如下的SQL语句来查询保留池的命中率(HitRatio)，杳询语句如下：

以上结果应该都要小于1%，如果大于1，那么应该考虑加大SHARED_POOL_RESERVED_SIZE。

结果缓存(ResultCache)是存放SQL查询结果和PL/SQL函数查询结果的区域。

共享池的大小由参数SHARED_POOL_SIZE决定。只要将初始化参数STATISTICS_LEVEL设置为TYPICAL(默认值)或ALL，就能启动对SharedPool的建议功能，若设置为BASIC，则关闭建议功能。使用如下的SQL语句可以查询到Oracle所建议的SharedPool的大小：

第一列表示Oracle所估计的SharedPool的尺寸值，其他列表示在该估计的SharedPool大小下所表现出来的指标值。可以主要关注ESTD_LC_TIME_SAVED_FACTOR列的值，当该列值为1时，表示再增加SharedPool的大小对性能的提高没有意义。对于上例来说，当SharedPool为200MB时，达到最佳大小。对于设置比200MB更大的SharedPool来说，就是浪费空间，没有更多的好处了。

2)数据缓冲区(DatabaseBufferCache)：也称为数据库缓冲区高速缓存，用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能，是数据库实例的重要组成部分。参数DB_CACHE_SIZE可指定数据缓冲区的大小，需要在参数文件中静态修改。Oracle在处理某个查询时，服务器进程会在BufferCache中查找它所需的所有数据块。如果未在BufferCache中找到所需要的数据块，那么服务器进程会从数据文件中读取所需的数据块，并在BufferCache中添加一个副本。因为关于同一数据块的后续请求可能会在内存中找到该数据块，因此，这些请求可能不需要进行物理读操作。BufferCache中的内存块有4种状态：

①Pinned：当前块正在被某个进程读取到Cache或正写到磁盘，即当前正在被访问的数据块，可防止多个会话同时对同一数据块进行写操作。此时，其他会话正等待访问该块。

②Clean：服务器进程从数据文件中读入的Block且还没有被其他进程所修改或者后台进程DBWn将DirtyBuffer写入到数据文件中的Buffer，该Buffer中的内容与数据文件中的Block一致。该状态的数据块是可以立即被移出的候选数据块。

③Free/Unused：Buffer内为空，为实例刚启动时的状态。BufferCache初始化时或者在执行altersystemflushbufier_cache以后的Buffer，该Buffer中没有存放任何内容。此状态与“clean”状态非常相似，不同之处在于“free/unused”状态的缓冲区尚未使用。

④Dirty：脏数据，数据块已被修改，需要先被DBWn刷新到磁盘，才能执行过期处理(移出缓冲区)。在该状态下，该Buffer的内容与数据文件中Block的内容不一致。

DatabaseBufferCache中数据块的状态转变如图所示。

DatabaseBufferCache中数据块的状态转变

数据库高速缓冲区的主要功能是用来暂时存放最近读取白数据库中的数据，也就是数据文件(DataFile)内的数据，而数据文件是以数据块(Block)为单位，因此，数据库高速缓冲区中的大小是以块为基数。当用户通过应用程序第一次向Oracle数据库发出查询请求时，Oracle会先在BufferCache内寻找该数据，如果有该请求所需要的数据，那么就直接从BufferCache传回给用户，这称为缓存命中(CacheHit)，这样就可以减少硬盘上的I/O次数。如果Oracle发现用户要的数据并不在BufferCache里，就称为缓存失误(CacheMiss)，Oracle会从数据库中读取所需要的数据块，先放入BufferCache中，再传送给用户。该区域内的数据块通过LRU(LeastRecentlyUsed，最近最少使用)算法管理。LRU将BufferCache中的所有的Clean和Free状态的Buffer按照它们被读取的频率连接起来(冷端：最少使用的；热端：最常被使用的；在服务器进程将磁盘的Block读取到BufferCache时，会先覆盖冷端的Buffer)。

BufferCache可以分为多个缓冲池：

①回收池(RecyclePool)：放到回收池中的数据，只要空间不够用，它们马上就会被释放出来，即回收池中的数据会最先被替换出内存，很少使用的数据放在该区。被放在回收池中的数据块不会被反复使用。也就是说，这些数据块只在事务(Transaction)还存在时才会被用到，一旦事务结束，就会被释放出来。回收池的大小最好是默认池的1/2，通过DB_RECYCLE_CACHE_SIZE参数指定回收池的大小。该缓存不参与ASMM的动态管理，不能自动调整大小。默认未启用，大小为0。手工修改指定值后，DefaultPool的空间将被相应的减少。

②保留池(KeepPool)：当数据被放到保留池里时，就代表这个数据是需要常常被重复使用的。保留池中的数据不会被替换出去，可以将常用的小表放置在该区可以降低I/O操作。可以通过DB_KEEP_CACHE_SIZE参数指定保留池的火小。该区域的大小不会被ASMM自动调节。默认未启用，大小为0，当手工修改指定该值后，DefaultPool的空间将被相应的减少。

③默认池(DefaultPool)：当没有指定对象存储的缓冲池时，数据就会放在默认池中，相当于一个没有Keep与Recycle池的实例的BufferCache。也就是说，放在默认池的数据利用的是LRU机制。通过DB_CACHE_SIZE参数指定默认池的大小。

BUFFER_POOL子句可以在对象的STORAGE子句中为对象指定使用具体的BufierPool。如果现有对象没有明确指定BufferPool，那么默认都指定为DefaultBufferPool。可以使用CREATE或ALTER语句指定对象存储的绣冲油：

在同一个数据库中，支持多种大小的数据块缓存。通过DB_nK_CACHE_SIZE参数指定，如：

①DB_CACHE_SIZE(指定标准块(这里为8K)的缓存区)。

②DB_2K_CACHE_SIZE(指定块大小为2K的缓存区)。

③DB_41K_CACHE_SIZE(指定块大小为4K的缓存区)。

④DB_16K_CACHE_SIZE(指定块大小为16K的缓存区)。

⑤DB_3ZK_CACHE_SIZE(指定块大小为32K的缓存区)。

标准块缓冲区大小由DB_CACHE_SIZE指定。如标准块为nK，则不能通过DB_nK_CACHE_SIZE来指定标准块缓冲区的大小，应由DB_CACHE_SIZE指定。

当数据库高速缓冲区需要读取或写回数据到数据文件中时，都需要通过DBWn这个后台进程来协助处理，而参数DB_WRITER_PROCESSES主要设置要由几个DBWn来协助处理。在此建议不要超过系统CPU的个数，如果设置的值超过了CPU的个数，那么超过的那些是无法起作用的。

当参数DB_CACHE_ADVICE设置为ON(当STATISTICS_LEVEL为TYPICAL或ALL时，DB_CACHE_ADVICE参数值默认为ON)时，表示开启DB_CACHE_ADVICE功能。当开启参数DB_CACHE_ADVICE后，经过一段时间，Oracle就会自动收集足够的相关统计数据，并预测出DB_CACHE_SIZE在不同大小情况下的性能数据，而这些数据就是通过V$DB_CACHE_ADVICE视图来显示的，因此，可以根据这些数据对DB_CACHE_SIZE做相关的调整，以达到最佳情况。

查询视图V$DBCACHE_ADVICE如下：

由以上结果可以知道，当ESTD_PHYSICAL_READ_FACTOR为1时，最佳的DB_BUFFER_SIZE是48，因为在这之后再进行调整对降低I/O的影响有限，所以该系统最佳的BufferCache的大小为48M。

3)Redo日志缓冲区(RedoLogBuffer)：对数据库进行修改的任何事务(Transaction)在记录到Redo日志文件之前都必须首先放到Redo日志缓冲区中。Redo日志缓冲区中的内容将被后台进程LGWR写入联机Redo日志文件(OnlineRedoLogFiles)中。Redo日志缓冲区是一个循环缓存区，在使用时从顶端向底端写入数据，然后再返回到缓冲区的起始点循环写入。Oracle中所有的DML和DDL操作都会记录日志，即便没有提交的DML操作也会记录日志，在指定了NOLOGGING时，也会记录一些日志。Redo日志缓冲区大小由参数LOG_BUFFER决定，需要在参数文件中静态修改。服务器进程(ServerProcess)及后台进程(BackgroundProcess)对Oracle的变更记录会写到Redo日志缓冲区，这些变更的数据都在内存中的Redo日志缓冲区中以RedoEntry(重做条目，也可称为RedoRecord)的方式存储。RedoEntry是Oracle从用户会话占用的内存里将这些变更的记录复制到Redo日志缓冲区内，其在内存中是一段连续的内存块，Oracle利用后台进程LGWR在适当的时机将Redo日志缓冲区中的信息(也就是RedoEntry)写回到联机Redo日志文件内，当万一数据库崩溃时，可以进行必要的恢复。后台进程LGWR将RedoEntry写回到联机Redo日志文件的时机包括：①用户发出提交命令(COMMIT)；②每隔3s；③Redo日志缓冲区空间剩余不到2/3；④Redo日志缓冲区内的数据达到1MB；⑤在发生联机Redo日志切换(LogSwitch)时；⑥在DBWn进程将修改的缓冲区写入磁盘时(如果相应的Redo日志数据尚未写入磁盘)。

4)大池(LargePool)：SGA中一个可选的内存区域，大池用来分配大块的内存，处理比共享池更大的内存，用来缓解SharedPool的负担。大池主要用在3种情况下，①若是共享服务器模式时，则在LargePool中分配UGA，若LargePool没有分配则在SharedPool中分配。若是专用服务器(多线程服务器MTS，Multi-ThreadedServer)连接，则UGA在PGA中创建。②语句的并行查询(ParallelExecuteionofStatements)，允许进程间消息缓冲区的分配，用来协调并行查询服务器。③恢复管理器RMAN，用于RMAN磁盘I/O缓冲区。大池的大小由参数LARGE_POOL_SIZE决定，可以动态修改。大池也使用共享池的闩锁机制，但和共享池不同的是，大池并不使用LRU机制，而是使用LargeMemoryLatch的保护，因此，大池中缓冲区内的数据不会被置换出来。大池内的数据会利用用户的会话来控制分配和释放大池的空间。如果大池的空间不足，那么也会出现ORA-04031错误。

①Java池(JavaPool)：为Java命令的语法分析提供服务。Java池也是SGA中的一块可选内存块，大小由参数JAVA_POOL_SIZE决定。在Oracle10g以后，提供了一个新的Java池的建议功能，以辅助调整Java池的大小，而建议的统计数据可以通过视图V$JAVA_POOL_ADVICE来查询。

②流池(StreamsPool)：被Oracle流所使用，主要提供专门的Streams复制功能，流池是可选用内存块，它也属于SGA中的可变区域。参数STREAMS_POOL_SIZE可以指定流池的大小。如果设置为0，那么当第一次使用Streams复制功能时，Oracle会自动建立此块区域，而自动建立的大小为共享池大小的10%。Oracle也提供了一个流池的建议功能，来协助调整流池的大小，而建议的统计数据可以通过视图V$STREAMS_POOL_ADVICE来查询。

(2)PGA介绍PGA(ProgramGlobalArea，程序全局区)是单个Oracle进程使用的内存区域，为每个连接到Oracle数据库的用户进程保留的内存，不属于实例的内存结构。它含有单个进程工作时需要的数据和控制信息。PGA是非共享的，只有服务进程本身才能够访问它自己的PGA区。PGA在进程创建时分配，进程结束时释放。PGA的内容随服务器的模式(专用模式/共享服务器模式)不同而不同。PGA的大小由参数PGA_AGGREGATE_TARGET决定，可动态修改。PGA结构如图所示。

PGA结构

PGA有如下几个组件：

1)PrivateSQLArea(私有SQL区)：参考SharedPool部分的介绍。

2)CursorandSQLAreas(游标和SQL区)：OraclePro*C程序(Pro*C是Oracle提供的应用程序专用开发工具，它以C语言为宿主语言，能在C程序中嵌入SQL语句，进行数据库操作)的应用程序开发人员或Oracle调用接口(OracleCallInterface，OCI)程序可以显式打开游标或处理私有SQL区。

3)SessionMemory(会话内存)：保存会话的变量(例如，登录信息)及其他与会话相关的信息。在共享服务器模式下，SessionMemory是共享的。

4)WorkArea(工作区)：PGA的一大部分被分配给WorkArea，用来执行如下操作：

①基于排序的操作，GROUPBY、ORDERBY、ROLLUP和窗口函数。由于排序需要内存空间，Oracle利用该内存排序数据，这部分空间称为排序区。排序区存在于请求排序的用户进程的内存中，该空间的大小为适应排序数据量的大小，可增长，但受参数SORT_AREA_SIZE所限制。

②HASH连接，大小受参数HASH_AREA_SIZE所限制。

③位图合并，大小受参数BITMAP_MERGE_AREA_SIZE所限制。

④位图创建，大小受参数CREATE_BITMAP_AREA_SIZE所限制。

⑤批量装载操作使用的写缓存。

PGA和SGA最明显的差别在于，PGA不是共享内存，是私有不共享的。用户对数据库发起的无论查询还是更新的任何操作，都是在PGA先预处理，然后接下来才进入实例区域，由SGA和系列后台进程共同完成用户发起的请求。PGA起到的具体作用主要有三点：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存PGA区内完成，如果放不下，超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。

(3)UGA介绍UGA(UserGlobalArea)保存了会话信息，会话总能访问这部分内存。UGA的位置取决于会话连接到Oracle的方式。如果是专用服务器连接，那么UGA在PGA中创建；如果是共享服务器连接，那么UGA在SGA的LargePool中创建，若LargePool没有分配则在SharedPool中分配。

PGA和UGA两者间的区别跟一个进程和一个会话之间的区别是类似的。尽管进程和会话之间一般都是一对一的关系，但实际上比这个更复杂。一个很明显的情况是MTS配置，会话往往会比进程多得多。在这种配置下，每一个进程会有一个PGA，每一个会话会有一个UGA。PGA所包含的信息与会话是无任何关联的，而UGA包含的信息是以特定的会话为基础的。

(4)自动PGA内存管理(AutomaticPGAMemoryManagement)若设置参数PGA_AGGREGATE_TARGET为非0，则启用自动PGA内存管理，并忽略所有*_AREA_SIZE的设置，例如SORT_AREA_SIZE、HASH_AREA_SIZE等。默认为启用PGA的自动管理，Oracle根据SGA的20%来动态调整PGA中专用于WorkArea部分的内存大小，最小为10MB。

如果设置参数WORKAREA_SIZE_POLICY为MANUAL(默认值是AUTO)，就代表此数据库的PGA管理模式属于手动管理模式，且在此模式下必须设置SORT_AREA_SIZE、HASH_AREA_SIZE等相关参数。

当自动PGA内存管理功能打开后，可以从V$PGA_TARGET_ADVICE中得到相关的指导数据，进而评估PGA_AGGREGATE_TARGE是否需要调整。该视图的ESTD_OVERALLOC_COUNT列表示需要额外分配的PGA内存，如果此数值不是0，就表示PGA_AGGREGATE_TARGE设置得太小，需要调整。

从上面的查询中可以看出当设置PGA的大小为228MB时，可以消除PGA过载的情形。

(5)SHOW

SGA和V$SGA的结果区别SHOWSGA的结果比V$SGA的结果多一行“TotalSystemGlobalArea”数据。其实，SHOWSGA的结果来源于V$SGA视图。运行命令“vi$ORACLE_HOME/bin/sqlolus”打开sqlplus文件，匹配SGA可以发现这么一行代码：

该行代码的结果和执行showsga可以得到一样的结果，如下：

在以上结果中，各部分的含义如下：

1)TotalSystemGlobalArea：显示目前此SGA的大小，包括FixedSize、VariableSize、Databasebuffers和RedoBuffers的大小总和。

2)FixedSize：这里存储了SGA各部分组件的相关信息，主要是作为引导SGA创建的区域，Oracle通过这个区找到SGA其他区，类似一个SGA各个组件的索引。这部分是Oracle内部使用的一个区，包括了数据库与实例的控制信息、状态信息、字典信息等。当实例被打开时，此块区域就被固定住而不能做任何变动，此区域也可称为FixedSGA。不同平台和不同版本下这部分的大小可能不一样。

3)VlariableSize：包括SharedPool、JavaPool、LargePool、StreamsPool、游标区和其他结构。由于这些内存块都是可动态分配的，所以统称为VariableSize。

4)DatabaseBuffers：显示数据库高速缓冲区的大小，是SGA中最大的地方，决定数据库性能。为DB_CACHE_SIZE、DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE、DB_NK_CACHE_SIZE的总大小，当然这是SGA_TARGET为0的情况，也就是手动SGA管理模式下，如果是自动SGA管理(SGA_TARGET＞0)，那么这个值根据SGA的分配情况自动进行调整。

5)RedoBuffers：显示Redo日志缓冲区的大小，这部分是实际分配的RedoLogBuffer的大小，由初始化参数LOG_BUFFER根据SGA的最小分配单位granule向上取整得到。

(6)和内存相关的比较有用的视图

1)V$SGASTAT主要记录了有关SGA的统计信息以及内存分配的情况，对于发生ORA-04031错误有很重要的参考价值。其中的信息由3个字段组成，依序是：Name(SGA内存块的名称)、Bytes(内存块的大小)、Pool(内存所属的内存块)。

以下的语句可查询内存块还剩余多少使用空间：

以下的语句可查询共享池的使用情况比率：

2)V$SGA_DYNAMIC_COMPONENTS记录了SGA内各个组件的情况，包括SGA的各个内存块的放大和缩小情况，且所提供的信息是经过统计的。

3)V$LIBRARYCACHE的内容包含了关于库缓存的性能统计信息，对于共享池的性能优化有很大帮助。此视图中必须特别注意PIN和GET的命中率或未命中率，当命中率小于99%或未命中率大于1%时，说明HardParse过多，可能需要加大共享池或是使用绑定变量等优化的动作。

6.

什么是ASMM和AMM?正确答案：ASMM(AutomaticSharedMemowManagement，自动共享内存管理)是Oracle10g引入的概念。通过使用ASMM，就不需要手工设置相关内存组件的大小，而只为SGA设置一个总的大小，Oracle的MMAN进程(MemoryManagerProcess，内存管理进程)会随着时间推移，根据系统负载的变化和内存需要，自动调整SGA中各个组件的内存大小。ASMM的SGA包含的组件及对应参数见表。

在Oracle10g中，必须将STATISTICS_LEVEL参数设置为TYPICAL(默认值)或者ALL才能启用ASMM功能，如果将其值设置为BASIC，那么会禁用很多新特性，比如像AWR、ASMM等。如果使用SQL*Plus来设置ASMM，那么必须先把SGA中包含的组件的值设置为0。通过设置SGA_TARGET参数为非零值来启用ASMM功能。

可以通过以下SQL来计算SGA_TARGET的值：

设置SGA_TARGET的值，可以直接修改初始化参数文件后重启数据库，或者通过下面SQL命令进行修改：

示例如下：

在启用ASMM后，Oracle会自动调整SGA内部组件大小。若再手动指定某一组件值，则该值为该组件的最小值。例如，手动设置SGA_TARGET=8G，SHARED_POOL_SIZE=1G，则ASMM在自动调整SGA内部组件大小时，保证SharedPool不会低于1G。当设置了SGA_TARGET参数后，Oracle将会收集SGA相关的统计数据，并通过V$SGA_TARGET_ADVICE呈现出来，因此，可以根据这些指导SGA_TARGET做相关的调整，以达到最佳情况。

Oracle10g的ASMM实现了自动共享内存管理，但是具有一定的局限性。所以，在Oracle11g中，Oracle引入了AMM(AutomaticMemoryManagement，自动内存管理)的概念，实现了全部内存的自动管理。DBA可以仅仅通过设置一个目标内存大小的初始化参数(MEMORY_TARGET)和可选最大内存大小初始化参数(MEMORY_MAX_TARGET)就可以在大多数平台上实现AMM。AMM可以使实例总内存保持相对稳定的状态，Oracle基于MEMORY_TARGET的值来自动调整SGA和PGA的大小。如果内存发生变化，实例会自动在SGA和PGA之间做调整。若启用了AMM功能，而SGA_TARGET和PGA_AGGREGATE_TARGET没有显式的被设置，则默认SGA_TARGET为MEMORY_TARGET的60%，PGA_AGGREGATE_TARGET为MEMORY_TARGET的40%。

MEMORY_TARGET是动态初始化参数，可以随时修改该参数的值而不用重启数据库。MEMORY_MAX_TARGET作为一个内存上限值，是一个静态参数，它是MEMORY_TARGET可以被配置的最大值。

当LOCK_SGA初始化参数的值设置为TRUE时，不能启用AMM，该参数的值默认为FALSE。

如果在创建数据库的时候未启用AMM，那么可以在建库后启用它，启用AMM需要重启数据库，具体步骤如下：

1)查询SGA_TARGET和PGA_AGGREGATE_TARGET的值，从而确定MEMORY_TARGET的最小值。

2)确定白系统启动以来PGA的最大值，单位为bytes。

3)通过以下方法来计算出SGA_TARGET的最大值。

例如：在这里，SGA_TARGET的值为1648M，PGA_AGGREGATE_TARGET的值为409M，PGA的最大值为248586240/1024/1024=237M，所以，MEMORY_TARGET的值至少为1648+409=2057M。

4)设置系统参数启用AMM。

另外需要说明的一点是，使用AMM经常出现的一个错误是“ORA-00845:MEMORY_TARGETnotsupportedonthissystem”。

这个错误原因是操作系统不支持MEMORY_TARGET参数或/dev/shm的大小设置不正确。解决方法就是将/dev/shm的值增大，至少需要大于数据库参数MEMORY_MAX_TARGET的值。修改步骤如下：

再次启动数据库就可以正常启动了。

由于AMM不支持HugePage，而ASMM支持HugePage，所以，在生产库上强烈推荐使用ASMM。有关ASMM和AMM的区别见表。

7.

如何解决ORAL-04030和ORA-04031错误?正确答案：ORA-04030和ORA-04031都是典型的内存分配错误，下面分别讲解。

(1)ORA-04030错误

ORA-04030报错形如“ORA-04030'outofprocessmemorywhentryingtoallocate%sbytes(%s,%s)'”，该错误意味着OracleServer进程无法从操作系统分配更多内存。该内存由PGA组成，其内容取决于服务器配置。对于专用的服务器进程，内存包含堆栈以及用于保存用户会话数据、游标信息和排序区的UGA。在多线程服务器(共享服务器)中，UGA被分配在SGA中，所以在这种配置下UGA不是造成ORA-04030错误的原因。因此，ORA-04030表示进程需要更多内存(堆栈、UGA或PGA)来执行其任务。

ORA-04030错误可能是进程本身导致的，例如进程需要过多的内存，或者一些其他原因导致操作系统内存被耗尽，例如，SGA分配太大或系统虚拟内存(物理内存+交换空间)中要容纳的进程过多。许多操作系统会对单个进程能够获取的内存量加以限制，以便自我保护。在大部分情况下，发生ORA-04030错误的进程并非总是内存损耗的元凶，错误的发生仅仅是因为此进程无法取得所需的内存造成的。

MOS文档(ID1548826.1和199746.1)对该错误有非常详细的说明。如果发生了ORA-04030错误，那么会在告警日志中记录详细信息。若发生ORA-04030错误，则可以从以下几个方面去排查该错误：

1)是否仍然有足够的可用内存?

主要使用操作系统特定的工具(top、topas、vmstat、swapon-s)来检查内存使用情况。如果有足够的内存可用，那么就需要检查操作系统是否存在强制限制。如果内存已被耗尽，那么就需要找出内存被用到了哪些地方。

2)是否发置了操作系统限制?

如果仍有足够的内存可用，那么有可能是进程需要使用的内存量是不被允许的。使用命令“ulimit-a”查看操作系统限制。尤其对于“datasegsize”选项，应该设置为unlimited。在RAC或GRID环境中，由于数据库监听是通过CRS进行启动，所以监听继承了root用户的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制为1310KB，那么表示每个通过监听连接的进程能分配的内存资源不能超过1310KB。

3)是否设置了Oracle限制?

查询参数PGA_AGGREGATE_TARGET的大小，该参数限制一个实例可以分配的PGA总量。以下查询用于查找分配给所有会话的PGA区的内存总量：

4)哪个进程需要的内存过多?

一些操作会需要大量的进程内存，例如，大型的PL/SQL表或大量的排序操作。在这些情况下，在出现错误ORA-04030之前，进程将会运行一段时间，所以，可以在这段时间内找出内存分配的位置和原因。可以使用以下查询来查找所有Oracle进程的PGA和UGA大小：

通常，从操作系统的角度来确认进程内存使用情况，是一个好办法。毕竟，使用过多内存的不一定是OracleServet。进程。

5)如何收集有关进程实际正在执行的任务的信息?

可以做heapdump，然后分析dump结果：

或者使用event，如下：

设置系统级事件来产生ORA-04030错误时的trace文件，设置evem如下：

得到报错的dump文件后，关闭生成event：

(2)ORA-04031错误

SGA中的内存池由不同大小的内存块组成。当数据库实例启动时，大量的内存块被分配到不同的池中并且由空闲列表哈希Bucket追踪。随着时间推移，由于内存块被分配和回收，内存块会根据它们的大小在池中的不同空闲列表Bucket中移动。

当Oracle不能找到一个足够大的内存块来满足用户操作所带来的内部分配请求的时候，ORA-04031错误就可能在SGA的任何一个池中[LargePool、JavaPool、StreamsPool(10g新增)、SharedPool]出现。ORA-04031错误信息会指出哪个池出了问题。如果错误指出问题不在共享池中，那么这通常意味着对应用环境来说，出问题的池配置的太小了。可以将出问题的池增大，然后继续观察后续的问题。如果使用Oracle10g的ASMM功能，那么MMAN进程会随着时间推移，尝试根据内存需要收缩或者增大SGA中不同组件的大小。如果在LargePool、StreamsPool或者JavaPool中遇到了ORA-04031错误，那么可以通过增大SGA_TARGET，使MMAN可以管理更多的内存。

MOS文档(ID2016002.1和146599.1)对ORA-04031有非常详细的说明。

在Oracle9i和之后的版本，共享池可以被划分为子池。每个子池是一个小号的共享池，有它自己的空闲列表，内存结构条目和LRU列表。这是一个对共享池和大池的可扩展性的改变，现在每一个子池都由一个ChildLatch来保护，因此可以增加这些池的吞吐量。这意味着不再有之前版本的对于共享池和大池的单独Latch的竞争。共享池中的保留区域也被平均地划分到每个子池中。

在Oracle或者更高版本中，当发生ORA-04031错误时，会生成一个trace文件(通过_4031_dump_bitvec参数控制)。ORA-04031的报错形式如下：

在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在这里，请求SQLArea中4192bytes时失败，并且发生在第6个子池中。

子池的个数跟以下3个方面相关：

1)CPU的个数系统中每4个CPU可以分配一个子池，最多分配7个子池，由参数CPU_COUNT控制。

2)参数SHARED_POOL_SIZE大小若ASMM或AMM没有启用(即手动管理内存，SGA_TARGET和MEMORY_TARGET都没有设置)，则在子池的内存分配原则见表。

若启用了ASMM或AMM，参数SHARED_POOL_SIZE被显式地设置了值，则使用上表的分配原则。如果没有显式地设置该参数的值，那么SHARED_POOL_SIZE的值取SGA_TARGET的50%。如果使用的是AMM，则SGA_TARGET的值取MEMORY_TARGET的60%。

3)隐含参数“_KGHDSIDX_COUNT”的大小，默认为1，表示1个子池。设置的SQL命令为：“ALTERSYSTEMSET"_KGHDSIDX_COUNT"=2SCOPE=SPFILE;”。

在以上规则中，若设置了隐含参数“_KGHDSIDX_COUNT”为大于1的值，则以该参数为准，否则取1)和2)中的最小值。例如，如果在Oracle11g中，有16个CPU，MEMORY_TARGET为4.2G，SGA_TARGET和SHARED_POOL_SIZE的值都为0，那么16个CPU可以分配4个子池，SHARED_POOL_SIZE的值为1.26G(4.2×0.6×0.5)，可以分配2个子池，每个子池为630MB。由于没有设置隐含参数“KGHDSIDX_COUNT”，所以，该系统的子池个数为MIN(4,2)=2。

子池的创建是在启动过程中SGA创建时发生的，所以修改隐含参数“_KGHDSIDX_COUNT”、SHARED_POOL_SIZE和CPU_COUNT之后，需要重新启动数据库，子池的数量才能变动。

具体的子池分配、内存情况及剩余内存情况可以使用如下的SQL语句查询：

通过以下查询可以详细列举不同子池的Free内存块情况：

需要注意的是，在ASM环境中也用到了共享池。在Oracle10gR1版本的ASM实例上有ORA-04031的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。

当试图在共享池中分配大块连续内存而失败时，Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片(chunk：内存块)得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要，那么会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。

在日常维护上可以查询视图V$SHARED_POOL_RESERVED，当REQUEST_FAILURES＞0时，则说明SharedPool在内存分配上存在一定的问题。

如果问题可以重现，那么可在执行有问题的SQL语句前，在会话级别对事件进行设置：

从Oracle版本开始，除了在请求heapdump时使用1、2、3或32等级，还可以使用相同等级并加值536870912，这样将会在此等级上再进一步显示5个最大的subheaps同时每个subheap下显示相关5个最大的heapareas。

下面给出分析ORA-04031产生的TRACE文件的一般步骤：

1)首先需要确认报错的是SGA的哪个池、哪个子池、需要分配哪部分内存、需要分配多大内存等问题。

大部分信息可以从报错信息直接获取到。检查告警日志并查看错误是否记录，但是，不是所有的ORA-04031错误都会记录在告警日志中。如果错误被记录，请检查是SGA的哪部分收到此错误，共享池，大池，Java池或Streams池。找到发生ORA-04031错误时的trace文件。

2)在trace文件中搜索关键字“MemoryUtilizationofSubpool”(子池可以直接搜索Subpool)和granulesize(粒度大小)，确认子池的个数及粒度的大小等参数。

3)找到各个Subpool的使用情况。

从Subpool部分分析哪个组件用的内存最多，freememory还剩多少内存?可能有的子池的freememory已经为0了。可以从视图V$SGASTAT来检查是否有组件表现出非正常增长，查询SQL可以为“SELECT*FROMV$SGASTATAWHEREA.NAME='objstarmemo';”。如果V$SGASTAT中没有记录，那么也可以查询：DBA_HIST_SGASTAT视图。

①如果用的最多的是SQLA(SQLAREA)，那么很可能就是没有使用绑定变量。

②如果是不常见的组件(例如，objstatmemo)，那么很可能就是BUG。如果是BUG，那么可以拿关键字去MOS上进行搜索，基本上可以找到相关内容。

在产生的TRACE文件中还需要关注“LIBRARYCACHESTATISTICS”，它代表库缓存的信息，如下：

在LibraryCache统计信息里，找到“hitratio”百分比，这指示了碎片问题。目标是使得“hitratio”尽可能地接近100%。另外要查看reloads和invalids信息。reloads和invalids很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。

在AWR中，可以查看“LoadProfile”、“SharedPoolAdvisory”和“LibraryCacheActivity”等部分进行分别分析。

可以通过如下的SQL语句分析ORA-04031出现的次数和需要分配的字节数：

其中，KGHLUNFU表示出现ORA-04031的次数，这里是指出现了114次的4031错误。KGHLUNFS表示最后一次出现ORA-04031时，申请的分配大小，在以上结果中表示最后申请共享池的大小是4096字节。

综上所述，导致ORA-04031错误的原因有如下几点：

1)配置问题，表现为某些池配置过小或配置错误。解决办法为增大相应的池(例如，SharedPool)大小及使用AMM方式来管理内存。如果报错的是LargePool或JavaPool，那么可以简单地增大参数LARGE_POOL_SIZE和JAVA_POOL_SIZE即可。

2)内存中存在大量碎片，导致在分配内存的时候没有连续的内存可供分配从而导致ORA-04031错误。解决办法为刷新共享池(ALTERSYSTEMFLUSHBUFFER_CACHE;)，该操作在生产库上要慎用。也可以重启数据库，重启数据库会释放内存，还会清理内存碎片。

3)应用问题，主要表现在：①没有使用绑定变量，不使用绑定变量会导致库缓存的过度使用。②有多个子游标，每个子游标都会在共享池中分配空间。③高解析率，可能由于使用了动态PL/SQL或在高负载阶段执行DDL语句。每次DDL语句执行，都会导致所有引用了这个对象的语句失效。下次执行引用了这个对象的SQL语句时，则不得不重新解析并加载到共享池中。④过度的打开CURSOR而不关闭，一般会导致SharedPool中的ORA-04031错误。⑤SESSION_CACHED_CURSORS、OPEN_CURSOR设置过高。OPEN_CURSOR如果设置的过大，那么会导致LibraryCache中很多对象都处于pin状态，而不能释放，那么当申请SharedPool内存时，通过LRU依然不能找到可用空间，就会导致ORA-04031错误。遇见这种情况可以适当减少OPEN_CURSOR的值。

4)Subpool的不均衡使用。Subpool的不均衡使用是使用Subpool的一个缺点，对于这种情况可以设置隐含参数“ENABLESHARED_POOL_DURATIONS=FALSE”来改变SharedPool的Subpool内存结构的分配方式，或者完全使用AMM。

5)Oracle的BUG导致内存泄露。例如，在一些版本中查询V$SEGMENT_STATISTICS这样的视图导致内存泄露，使SharedPool内存耗光。同样的情形还有类似于“objstatmemory”、“gcsresources”、“gesresources”等。这类内存通常是在分配时就确定了固定的用途，不能用于其他用途，因此极容易产生碎片。

8.

简述Oracle数据库块的结构正确答案：操作系统块是操作系统读写的最小操作单元，也是操作系统文件的属性之一。当创建一个Oracle数据库时，选择一个基于操作系统块的整数倍大小作为Oracle数据库块的大小。Oracle数据库读写操作则是以Oracle块为最小单位，而非操作系统块。

数据库块也称逻辑块或Oracle块，它对应磁盘上一个或多个物理块，它的大小由初始化参数DB_BLOCK_SIZE决定，可以定义数据块为2K、4K、8K、16K、32K甚至更大，默认Oracle块大小是8K。若一旦设置了Oracle数据块的大小，则在整个数据库生命期间不能被更改。使用一个合适的Oracle块大小对于数据库的调优是非常重要的。

OS在每次执行I/O的时候是以OS的块为单位：Oracle在每次执行I/O的时候是以Oracle块为单位。Oracle块具有以下特点：

1)最小的I/O单元。

2)包含一个或多个OS块。

3)大小由参数DB_BLOCK_SIZE决定。

4)数据库创建时设置，数据库创建后不能更改。

在Oracle中，不论数据块中存储的是表(TABLE)、索引(INDEX)或簇表(CLUSTERTABLE)，其内部结构都是类似的。Oracle块结构如图所示。

Oracle块结构图

由上图可以看出，一个Oracle块大约