Oracle11g温习-第一章 1、ORACLE实例
2013年4月27日 星期六
10:23
1、ORACLE 实例
System Global Area(SGA) 和 Background Process（后台进程） 称为数据库的实例。
2、ORACLE 数据库
|
一系列物理文件的集合： @数据文件：存储数据，以.dbf做后缀。一句话：一个表空间对多个数据文件，一个数据文件只对一个表空间。dba_data_files/v$datafile。 @控制文件：存储实例、数据文件及日志文件等信息的二进制文件。alter system set control_files=‘路径’。V$CONTROLFILE。 @日志文件：即Redo Log Files和Archivelog Files。记录数据库修改信息。ALTER SYSTEM SWITCH LOGFILE; 。V$LOG。 @参数文件：记录基本参数。spfile和pfile。 @警告文件：show parameter background_dump_dest---使用共享服务器连接 @跟踪文件：show parameter user_dump_dest---使用专用服务器连接 （Ps：专用服务器适用于开发阶段，简单，连接少。反之，共享服务器适用于实际应用环境，多应用，高并发） |
3、系统全局共享区System Global area. System Global Area 是一块巨大的共享内存区域，它被看做是Oracle 数据库的一个大缓冲池，这里的数据可以被ORACLE的各个进程共用。
包含：
■ Shared Pool (共享区)
■ Database Buffer Cache (数据库缓冲区)
■ Redo Log Buffer Cache (重做日志缓冲区)
Oracle 10g中，与内存相关的参数可以归为两类：
Ps. 自动调优的SGA参数：目前这些参数包括DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE和JAVA_POOL_SIZE。
Ps. 手动SGA参数：这些参数包括LOG_BUFFER、STREAMS_POOL、DB_NK_CACHE_SIZE、DB_KEEP_CACHE_SIZE和DB_RECYCLE_CACHE_SIZE。
其大小查看语句：
SYS @ prod > select name,value/1024/1024 "size: M" from v$sga;
NAME size: M
-------------------- ----------
Fixed Size 1.16228867
Variable Size 60.0017738
Database Buffers 168
Redo Buffers 2.8359375
SYS @ prod > desc v$sgastat;
Name Null? Type
----------------------------------------- -------- ----------------------------
POOL VARCHAR2(12)
NAME VARCHAR2(26)
BYTES NUMBER
SYS @ prod > desc V$buffer_pool;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
RESIZE_STATE VARCHAR2(10)
CURRENT_SIZE NUMBER
BUFFERS NUMBER
TARGET_SIZE NUMBER
TARGET_BUFFERS NUMBER
PREV_SIZE NUMBER
PREV_BUFFERS NUMBER
LO_BNUM NUMBER
HI_BNUM NUMBER
LO_SETID NUMBER
HI_SETID NUMBER
SET_COUNT NUMBER
主要包括以下几个部分：
- 共享池(Shared pool)
共享池是SGA中最关键的内存片段，特别是在性能和可伸缩性上。太大太小都会扼杀性能，使系统停止，将会消耗大量的CPU来管理这个共享池。
共享池可分为：Library Cache （库缓冲区） 和 Data Dictionary Cache （数据字典缓冲区）
【库缓冲区(Library Cache)】
library cache最主要的功能就是存放用户提交的SQL语句及相关的解析树（解析树也就是对SQL语句中所涉及的所有对象的展现）、执行计划、用户提交的PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被Oracle执行的代码等。
library cache中还存放了很多控制结构（用于管理这些内存结构），包括lock、pin、dependency table等。
library cache也存放了很多的数据库对象的信息，包括表、索引等。有关这些数据库对象的信息都是从dictionary cache中获得的。如果用户对library cache中的对象信息进行了修改，比如为表添加了一个列等，则这些修改会返回到dictionary cache中。
在library cache中存放的所有信息单元都叫做对象（object），可分两类：
一类叫存储对象，也就是上面所说的数据库对象。它们是通过显式的SQL语句或PL/SQL程序创建出来的，如果要删除它们，也必须通过显式的SQL命令进行删除。这类对象包括表、视图、索引、包、函数等；
一类叫做过渡对象，也就是上面所说的用户提交的SQL语句或者提交的PL/SQL匿名程序块等。这些过渡对象是在执行SQL语句或PL/SQL程序的过程中产生的，并缓存在内存里。如果实例关闭则删除，或者由于内存不足而被交换出去，从而被删除。
当一个用户提交一个SQL语句，Oracle会将这句SQL进行分析(parse)，这个过程类似于编译，会耗费相对较多的时间，即硬解析。在分析完这个SQL，Oracle会把他的分析结果给保存在Shared pool的Library Cache中，当数据库第二次执行该SQL时，Oracle自动跳过这个分析过程，从而减少了系统运行的时间，即软解析。这也是为什么第二次运行的SQL 比第一次运行的SQL要快 的原因。
|
下面举例说明parse的时间 SQL> select count(*) from scpass ; COUNT(*) ---------- 243 这是在Share_pool 和Data buffer 都没有数据缓冲区的情况下所用的时间 SQL> alter system flush SHARED_POOL; System altered. 清空Share_pool，保留Data buffer SQL> select count(*) from scpass ; COUNT(*) ---------- 243 Elapsed: 00:00:00.02
SQL> select count(*) from scpass ; COUNT(*) ---------- 243 Elapsed: 00:00:00.00 从两句SQL 的时间差上可以看出该SQL 的Parse 时间约为00:00:00.02
对于保存在共享池中的SQL语句，可以从V$Sqltext、v$Sqlarea中查询到，对于编程者来说，要尽量提高语句的重用率，减少语句的分析时间。一个设计差的应用程序可以毁掉整个数据库的Share pool，提高SQL语句的重用率必须先养成良好的编程习惯，尽量使用Bind变量。 |
对于保存在共享池中的SQL语句，可以从V$Sqltext、v$Sqlarea中查询到，对于编程者来说，要尽量提高语句的重用率，减少语句的分析时间。一个设计差的应用程序可以毁掉整个数据库的Share pool，提高SQL语句的重用率必须先养成良好的编程习惯，尽量使用Bind变量。
|
关于library cache的命中率： SYS @ PROD > desc V$librarycache NAMESPACE VARCHAR2(64) GETS NUMBER GETHITS NUMBER GETHITRATIO NUMBER PINS NUMBER PINHITS NUMBER PINHITRATIO NUMBER RELOADS NUMBER INVALIDATIONS NUMBER DLM_LOCK_REQUESTS NUMBER DLM_PIN_REQUESTS NUMBER DLM_PIN_RELEASES NUMBER DLM_INVALIDATION_REQUESTS NUMBER DLM_INVALIDATIONS NUMBER
计算library cache的命中率： NAMESPACE=SQL AREA Library Cache Hit Ratio = sum(pinhits) / sum(pins) 以下语句计算librarycache 命中率 SYS @ prod >select sum(pins),sum(pinhits) ,trunc(sum(pinhits)/sum(pins),4) from v$librarycache;
SUM(PINS) SUM(PINHITS) SUM(PINHITS)/SUM(PINS) ---------- ------------ ----------------------------------------- 72617 64108 0.8848 |
【数据字典缓冲区(Data Dictionary Cache) 】 用于存放Oracle系统管理自身所需要的所有信息，包括登录的用户名、用户对象、权限等。
|
查看 data dictionary cache 的命中率
SQL> desc v$rowcache; Name Null? Type ----------------------------------------- -------- ---------------------------- CACHE# NUMBER TYPE VARCHAR2(11) SUBORDINATE# NUMBER PARAMETER VARCHAR2(32) COUNT NUMBER USAGE NUMBER FIXED NUMBER GETS NUMBER GETMISSES NUMBER SCANS NUMBER SCANMISSES NUMBER SCANCOMPLETES NUMBER MODIFICATIONS NUMBER FLUSHES NUMBER DLM_REQUESTS NUMBER DLM_CONFLICTS NUMBER DLM_RELEASES NUMBER
SYS @ prod >select sum(gets),sum(getmisses) ,1-(sum(getmisses)/(sum(gets)+sum(getmisses))) from v$rowcache;
SUM(GETS) SUM(GETMISSES) 1-(SUM(GETMISSES)/(SUM(GETS)+SUM(GETMISSES))) ---------- -------------- --------------------------------------------- 82077 15211 .843649782
如果data dictionary cache的命中率小于95%，应该适当增加shared pool 的大小
查看data dictionary cache 的大小 SYS @ prod >select sum(sharable_mem)/1024/1024 "size: M" from v$sqlarea;
size: M ---------- 3.23309803
可使用以下语句修改该参数
SQL> alter system set shared_pool_size=***M； |
b、数据库高速缓冲区(Database Buffer Cache) 存放Oracle系统最近使用过的数据块。让他们能够在内存中进行操作。在这个级别里没有系统文件，用户数据文件，临时数据文件，回滚段文件之分。也就是任何文件的数据块都有可能被缓冲。数据库的任何修改都在该缓冲里完成，并由DBWR进程将修改后的数据写入磁盘。
这个缓冲区的块基本上在两个不同的列表中管理。一个是块的“脏”表(Dirty List)，需要用数据库块的书写器(DBWR)来写入，另外一个是不脏的块的列表(Free List)，一般的情况下，是使用最近最少使用 (Least Recently Used,LRU)算法来管理。
数据库高速缓冲区又可以细分为以下三个部分：Default pool,Keep pool,Recycle pool。ORACLE将默认为Default pool。
由于操作系统寻址能力的限制，不通过特殊设置，在32位的系统上，数据库高速缓冲区最大可以达到1.7G，在64位系统上，块缓冲区高速缓存最大可以达到10G。
c、重做日志缓冲区(Redo log buffer)
重做日志文件的缓冲区，对数据库的任何修改都按顺序被记录在该缓冲，然后由LGWR进程将它写入Redo log files。这些修改信息可能是DML语句，如(Insert,Update,Delete)，或DDL语句，如(Create,Alter,Drop等)。 重做日志缓冲区的存在是因为内存到内存的操作比内存到硬盘的速度快很多，所以重作日志缓冲区可以加快数据库的操作速度，但是考虑到数据库的一致性与可恢复性，数据在重做日志缓冲区中的滞留时间不会很长。所以重作日志缓冲区一般都很小，大于3M之后的重作日志缓冲区已经没有太大的实际意义。
d、Java程序缓冲区(Java Pool)
Java的程序区，Oracle 8.1.5以后，Oracle在内核中加入了对Java的支持。该程序缓冲区就是为Java 程序保留的。如果不用Java程序没有必要改变该缓冲区的默认大小。
e、大池(Large Pool)
大池的得名不是因为大，而是因为它用来分配大块的内存，处理比共享池更大的内存，在8.0开始引入。
下面对象使用大池：
MTS（多线程服务器即共享服务器）——在SGA的Large Pool中分配UGA
语句的并行查询(Parallel Executeion of Statements)——允许进程间消息缓冲区的分配，用来协调 并行查询服务器。
备份(Backup)——用于RMAN磁盘I/O缓存
4、后台进程(Background process)---V$BGPROCESS
后台进程是Oracle的程序，用来管理数据库的读写，恢复和监视等工作。Server Process主要是通过它和user process进行联系和沟通，并由它和user process进行数据的交换。在Unix机器上，Oracle后台进程相对于操作系统进程，也就是说，一个Oracle后台进程将启动一个操作系统进程；在Windows机器上， Oracle后台进程相对于操作系统线程，打开任务管理器，我们只能看到一个ORACLE.EXE的进程，但是通过另外的工具，就可以看到包含在这些进程中的线程。
在Unix上可以通过如下方法查看后台进程：
[root@ogg ~]# ps -ef |grep ora_
|
Oracle系统有5 个基本进程： DBWR(数据文件写入进程) LGWR(日志文件写入进程) SMON(系统监护进程) PMON(用户进程监护进程) CKPT(检查点进程,同步数据文件, 日志文件,控制文件) |
|
a、 DBWR 将修改过的数据缓冲区的数据写入对应数据文件 维护系统内的空缓冲区 这里指出几个容易错误的概念: 错误1.当一个更新提交后,DBWR把数据写到磁盘并返回给用户提交完成. 错误2.DBWR会触发CKPT 后台进程 错误3.DBWR不会触发LGWR 进程 上面的概念都是错误的. DBWR是一个很底层的工作进程，他批量的把缓冲区的数据写入磁盘。和任何前台用户的进程几乎没有什么关系，也不受他们的控制。 DBWR工作的触发条件：
|
|
b、LGWR 将重做日志缓冲区的数据写入重做日志文件，LGWR是一个必须和前台用户进程通信的进程。当数据被修改的时候，系统会产生一个重做日志并记录在重做日志缓冲区内。这个重做日志可以类似的认为是以下的一个结构: SCN=000000001000 数据块ID 对象ID=0801 数据行=02 修改后的数据=0011
提交的时候，LGWR必须将被修改的数据的重做日志缓冲区内数据写入日志文件，然后再通知前台进程提交成功，并由前台进程通知用户。从这点可以看出LGWR承担了维护系统数据完整性的任务。
LGWR工作触发条件：
LGWR将重做条目写入联机日志文件的情况分两种：后台写（background write）和同步写（sync write）。 触发后台写的条件有四个： 1）每隔三秒钟，LGWR启动一次； 2）在DBWR启动时，如果发现脏数据块所对应的重做条目还没有写入联机日志文件，则DBWR触发LGWR进程并等待LRWR写完以后才会继续； 3）重做条目的数量达到整个日志缓冲区的1/3时，触发LGWR； 4）重做条目的数量达到1MB时，触发LGWR。 而触发同步写的条件就一个：当用户提交（commit）时，触发LGWR。
日志缓冲区是一个循环缓冲区。当LGWR将日志缓冲区的日志条目写入日志文件后，服务器进程可将新的日志条目写入到该日志缓冲区。LGWR 通常写得很快，可确保日志缓冲区总有空间可写入新的日志项。 注意：有时候当需要更多的日志缓冲区时，LWGR在一个事务提交前就将日志项写出，而这些日志项仅当在以后事务提交后才永久化。 ORACLE使用快速提交机制，当用户发出COMMIT语句时，一个COMMIT记录立即放入日志缓冲区，但相应的数据缓冲区改变是被延迟，直到在更有效时才将它们写入数据文件。当一事务提交时，被赋给一个系统修改号（SCN），它同事务日志项一起记录在日志中。由于SCN记录在日志中，以致在并行服务器选项配置情况下，恢复操作可以同步。 |
|
c. SMON 工作主要包含： 清除临时空间; 在系统启动时，完成系统实例恢复； 聚结空闲空间； 从不可用的文件中恢复事务的活动； OPS中失败节点的实例恢复； 清除OBJ$表； 缩减回滚段; 使回滚段脱机； |
d、PMON
主要用于清除失效的用户进程，释放用户进程所用的资源。如PMON将回滚未提交的工作，释放锁，释放分配给失败进程的SGA资源。
e、CKPT
|
同步数据文件，日志文件和控制文件，由于DBWR/LGWR的工作原理，造成了数据文件，日志文件，控制文件的不一致，这就需要CKPT进程来同步。 CKPT会更新数据文件/控制文件的头信息。 CKPT工作触发条件： 在日志切换的时候；
|
以下进程的启动需要手工配置
|
1、 ARCH 当数据库以归档方式运行的时候，Oracle会启动ARCH进程，当重做日志文件被写满时，日志文件进行切换，旧的重做日志文件就被ARCH进程复制 到一个/多个特定的目录/远程机器。这些被复制的重做日志文件被叫做归档日志文件。 2、RECO 负责解决分布事物中的故障。Oracle可以连接远程的多个数据库，当由于网络问题，有些事物处于悬而未决的状态。RECO进程试图建立与远程服 务器的通信，当故障消除后，RECO进程自动解决所有悬而未决的会话。 3、LCKn 是在具有并行服务器选件环境下使用，可多至10个进程（LCK0，LCK1……，LCK9），用于实例间的封锁。 4、服务进程Server Process 服务进程的分类 专用服务进程(Dedicated Server Process) 一个服务进程对应一个用户进程 共享服务进程(MultiTreaded Server Process) 一个服务进程对应多个用户进程，轮流为用户进程服务。 |
5、PGA，UGA，CGA
PGA: 程序全局区，服务器进程使用的内存区域，包括特定服务器进程的数据和控制信息，例如私有信息和使用的操作系统资源等。
UGA: 用户全局区，特定Session使用的内存区域，例如Session的SQL工作区、登陆认证信息等。Session的SQL工作区大小对查询性能的影响比较关键
shared server模式时UGA从SGA的large pool（如果有设置）或者shared pool（没有设置large pool）中分配，dedicated server模式时UGA从PGA中分配。
CGA: 调用全局区，存放调用过程中需要的数据，例如parse调用、executive调用、fetch调用等。调用过程中实际需要的数据，例如SQL Area、Sort Area等位于UGA中，CGA存放的只是调用过程中的临时处理数据，例如I/O缓存、临时堆栈空间等。CGA位于PGA中（PGA是集中管理这些资源的地方），在调用开始时创建，调用执行过程中动态分配，调用结束后释放。
WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
{ ALTER SYSTEM | ALTER SESSION } SET WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
设为AUTO时，内存使用比较多的SQL操作，例如sort, group-by, hash-join, bitmap merge和bitmap create等，由Oracle自动分配；
设为MANUAL时，这些操作将使用相应的*_AREA_SIZE参数设置的内存值。
*_AREA_SIZE作用于每个Session，分配的过小，很多Session可能没有足够的内存而效率低下，分配过多，大量Session可能浪费掉很多内存空间，9i开始的PGA自动管理功能改善了这个状况。使用PGA_AGGREGATE_TARGET设置整个PGA大小，Oracle将为每个Session按照实际需要为其分配PGA，并尽量维持PGA总量不超过PGA_AGGREGATE_TARGET值。
PGA_AGGREGATE_TARGET = integer [K | M | G]
设定总的PGA内存大小。Oracle将尽量保证PGA内存总合不超过这个值，但不是绝对的，为了避免磁盘操作，有时可能会超过这个值。可以设置为总的内存数减去SGA的剩余值。
将该初始化参数设置为大于0的值，Oracle自动将WORKAREA_SIZE_POLICY设为AUTO；将该初始化参数设置为0，Oracle自动将WORKAREA_SIZE_POLICY设为MANUAL。设置了PGA_AGGREGATE_TARGET之后，会忽略所有*_AREA_SIZE设置。
HASH_AREA_SIZE = Integer
单位为字节数。
SORT_AREA_SIZE = Integer
单位为字节数。如果没有设置SORT_AREA_RETAINED_SIZE，分配的内存在排序结束，开始返回记录之前释放，如果设置了SORT_AREA_RETAINED_SIZE，分配的内存在最后一条记录返回后释放。位图索引上的插入、更新也会用到SORT_AREA_SIZE（因为扫描完索引之后必须对各个位图进行排序，然后再合并成一个位图）。
SORT_AREA_RETAINED_SIZE = Integer
单位为字节数。指在UGA中保留的排序内存大小，排序操作结束，返回最后一条记录之后释放排序内存，只是释放给UGA，而不是操作系统。
BITMAP_MERGE_AREA_SIZE，CREATE_BITMAP_AREA_SIZE，位图索引创建、更新时使用的内存配置。
_pga_max_size: Maximum size of the PGA memory for one process。默认值200M。
查看_pga_max_size值的方法:
SYS @ prod >select ksppinm "Name", ksppstvl/1024/1024 ||'M' "Value", ksppdesc "Desc" from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm ='_pga_max_size';
Name Value Desc
-------------------------------------------------- ----- --------------------------------------------------
_pga_max_size 200M Maximum size of the PGA memory for one process
_smm_max_size: Maximum work area size in auto mode (serial)。{ALTER SESSION | ALTER SYSTEM}。
网上有很多描述_smm_max_size默认值的说法，其实都不对，追究其算法也没多少意义，反正如果你没有手动设置过_smm_max_size的值，一旦设置_pga_max_size或者PGA_AGGREGATE_TARGET，Oracle就会自动计算出_smm_max_size的值，如果手动设置过_smm_max_size则不会变化，Oracle自动计算出的_smm_max_size值，跟PGA_AGGREGATE_TARGET和_pga_max_size都有关系。查看
_smm_max_size值的方法如下:
SYS @ prod > select ksppinm "Name", ksppstvl/1024 ||'M' "Value", ksppdesc "Desc" from x$ksppi x, x$ksppcv y
where x.indx = y.indx and ksppinm ='_smm_max_size';
Name Value Desc
-------------------------------------------------- ----- --------------------------------------------------
_smm_max_size 15.39 maximum work area size in auto mode (serial)
94140
625M
使用PGA_AGGREGATE_TARGET让Oracle自动分配PGA时，为了防止单个Session占用过多内存而导致整个服务器性能下降，这两个隐藏参数控制了单个进程的最大内存使用量。
这两个参数为隐藏参数，Oracle官方不进行技术支持，如果系统中存在大量的HASH JOIN、SORT等耗费内存的操作，可以考虑、测试这些参数对系统性能的影响，但注意其副作用。
没有使用并行查询时，单个Session SQL工作区内存使用最大值为min( 5%*PGA_AGGREGATE_TARGET , 50%*_pga_max_size , _smm_max_size)。
设置_pga_max_size: Alter System Set "_pga_max_size"=1024M。
设置_smm_max_size: Alter System Set "_smm_max_size"=307200，单位默认是K，只能以这种单位进行设置，数字后面不能使用K、M、G这些单位字符了。
*******************************************************************************************************************************
监控、改善PGA设置
监控PGA的视图：v$sql_workarea_active、v$sql_workarea、v$sesstat、v$process、v$sysstat、v$sql_workarea_histogram等。
|
使用下面的视图查看Oracle建议的PGA 评估设置:
SYS @ prod > desc v$pga_target_advice; Name Null? Type ----------------------------------------- -------- ---------------------------- PGA_TARGET_FOR_ESTIMATE NUMBER PGA_TARGET_FACTOR NUMBER ADVICE_STATUS VARCHAR2(3) BYTES_PROCESSED NUMBER ESTD_EXTRA_BYTES_RW NUMBER ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER ESTD_OVERALLOC_COUNT NUMBER
SQL> Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target" ,estd_pga_cache_hit_percentage "Cache Hit(%)" , estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write" ,estd_overalloc_count "Over alloc count" From v$pga_target_advice;
Estimate PGA Target Cache Hit(%) Extra Read/Write Over alloc count ----------------------------------------- ------------ ----------------------------------------- ---------------- 11.25M 100 0M 1 22.5M 100 0M 1 45M 100 0M 1 67.5M 100 0M 0 90M 100 0M 0 108M 100 0M 0 125.9990234375M 100 0M 0 144M 100 0M 0 162M 100 0M 0 180M 100 0M 0 270M 100 0M 0 360M 100 0M 0 540M 100 0M 0 720M 100 0M 0
选出的4个列中，Over alloc count指示Oracle SQL工作区内存分配的三种情况： optimal完全可以在内存中完成操作； onepass需要进行一次磁盘交换； multipass需要进行多次磁盘交换。 第四列的值就是需要进行磁盘交换的数量。 PGA_AGGREGATE_TARGET的值最好选择: Over alloc count为0、 Cache Hit(%)尽可能高、Extra Read/Write尽可能低的Estimate PGA Target值。 |
6、一个贯穿数据库全局的概念----系统改变号SCN(System Change Number)
系统改变号，一个由系统内部维护的序列号。当系统更新时自动增加，是系统中维持数据的一致性和顺序恢复的重要标志。
SYS @ prod > select current_scn from v$database;
CURRENT_SCN
-----------
1254942
