《深入浅出ORACLE》读书笔记

 

第1章 数据库的启动和关闭

 

oracle寻找参数文件的顺序：spfile<sid>.ora -> spfile.ora -> init<sid>.ora  这个顺序写在oracle应用程序的硬代码里，无法改变。

参数文件最少只需要db_name，实例就可以启动。在RMAN中甚至不需要参数文件就能启动实例，数据库的db_name会缺省命名为DUMMY。

控制文件在nomount，mount状态都可以查询，在nomount状态可以查询v$parameter视图获得控制文件的信息；在mount状态可以查询v$controlfile视图获得控制文件的信息。

数据库在mount的过程中，alert文件会有mount id的标志，这一步骤主要是数据库需要计算mount id并将其记录在控制文件中，然后开始启动心跳，每3秒钟更新一次控制文件，也就是说在mount状态下，控制文件改变的只有这个heartbeat。我们可一间隔三秒转储两次控制文件信息并用diff命令进行比较来进行验证。这里介绍转储控制文件的命令：

alter session set events 'immediate trace name CONTROLF level 10'

启动到mount状态还需要口令文件，oracle查找口令文件的顺序是：orapw<sid> -> orapw。口令文件可以用orapw工具进行重建，无需备份。跟口令文件相关的一个参数是：remote_login_passwordfile。

通常在UNIX平台下，在$ORACLE_HOME/dbs目录下还会存在另外一个文件lk<SID>。该文件在DB启动时创建，用于OS对DB的锁定，DB关闭时释放。有时在系统出现异常时，可能数据库已经关闭，但是锁定并未释放，或者应为后台进程未正常停止等原因，会导致下次数据库无法启动。

 

数据库的open阶段：oracle根据控制文件记录的数据文件&日志文件的位置信息，检查点等重要信息来找到这些文件，然后进行检查点及完整性检查。进一步说，数据库OPEN的过程中，oralce进行的检查包括以下两项：a.检查数据文件头的检查点计数（checkpoint cnt）是否和控制文件的检查点计数一致。此检查用来确认数据文件是否是同一版本，而不是从备份中恢复而来（热备期间SCN可能被冻结，但checkpoint cnt会一直修改。我们可以通过dump控制文件来进行观察。）b.检查数据文件头的开始SCN和控制文件中记录的该文件的结束SCN是否一致。当数据库打开后，数据文件的结束SCN设置为无穷大。当完成数据库的验证和恢复过程后，数据库处于一致状态，数据库还要进行一系列的处理过程：将UNDO段在线，让数据库可以访问，同时SMON可以开始进行事务回滚。每次数据库的启动过程中，oracle都要判断控制文件记录的字符集和数据库中的字符集是否相符，如果不符将用数据库的字符集更新控制文件的字符集记录。8i以前我们可以通过update props$修改字符集，8i开始不能用这种方法。

 

SCN:系统改变号，是oracle数据库的内部时钟机制。每个库都有个全局的SCN生成器。

SCN的获取方式：9i：SQL>select dbms_flashback.get_system_change_number from dual; 9i前：SQL>select max（ktuxescnw*power（2，32）+ktuxescnb）from x$ktuxe;

数据文件包含checkpoint SCN，stop SCN，checkpoint CNT等重要数据结构。

转储数据文件头的命令：SQL>alter session set events 'immediate trace name file_hdrs level 10';

日志文件头中包含了LOW SCN和NEXT SCN。current的日志文件的next SCN被置为无穷大也就是ffffffff。我们可以dump控制文件来查看有关日志文件部分的信息。

 

检查点：检查点的本质是减少数据库崩溃恢复的时间。检查点发生时oracle会通知DBWR进程把修改过的数据（此checkpoint SCN之前的脏数据)从buffer cache写入磁盘，写入完成后，CKPT进程更新控制文件和数据文件头，记录检查点信息。这个checkpoint SCN可以从数据库查询：

SQL>select file#,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss'）CPT from V$datafile;

SQL>select dbid,CHECKPOINT_CHANGE# from v$database;

检查点完成后，此检查点之前修改过的数据已写回磁盘，重做日志文件中的相应重做记录对于实例恢复不再有用。所以说检查点的频率对于数据库的恢复时间有极大的影响，频率高恢复时应用的重做日志少，恢复时间短。但频率过高也会带来性能问题。所以oracle在不同的版本一直在改进检查点的算法。

常规检查点与增量检查点：增量检查点是在oracle 8开始引入的。这时引入了检查点队列（checkpoint queue）的机制，每一个脏数据块都会被移动到检查点队列，按照Low RBA（第一次对此数据块修改时对应的Redo Byte Address。我理解为：修改这个数据块时对应的日志文件中的具体地址）的顺序排列，如果一个数据块被修改过多次，该数据块在检查点队列的顺序并不会发生变化。增量检查点可以连续的进行（DBWR可以持续进行写出），因此检查点RBA可以比常规检查点更接近数据库的最后状态，从而在实例恢复中极大减少恢复时间。

增量检查点是通过Fast-Start Checkpointing特性来实现的。在8i中主要通过参数FAST_START_IO_TARGET来实现；在9i中主要通过FAST_START_MTTR_TARGET来实现。从9i R2开始引入了一个新的视图：v$mttr_target_advice。用这个视图来评估FAST_START_IO_TARGET的设置。同时这个建议信息的收集受statistics_level参数的控制。想获得当前数据库的估计平均恢复时间可以查询v$instance_recovery视图。当ESTIMATED_MTTR接近或超过FAST_START_MTTR_TARGET时，系统就会触发检查点，执行写出后，系统恢复信息将会重新计算。在繁忙的系统中可能会看到ESTIMATED_MTTR>TARGET_MTTR,这可能是DBWR正忙于写出，或者是出现checkpoint不能及时完成的情况。

10g自动检查点调整：10g开始使用自动调整检查点，oracle可以利用系统的低I/O负载时段写出内存中的脏数据。即使DBA设置了不合理的检查点参数，oracle仍然可以通过自动调整将数据库的crash recovery时间控制在合理的范围之内。当FAST_START_MTTR_TARGET参数未设置时，自动检查点调整生效。

从控制文件获得检查点信息：转储控制文件可以看到检查点进程进度的记录：

low cache rba:(0x03.1d3.0) on disk rba:(0x11.1e2.0)

low cache rba指在cache中，最低的RBA地址。恢复就是从这里开始。（我的理解：在cache中还没有写入数据文件的block所对应的最低RBA)

on disk rba指需要恢复到这个值。（我的理解：当前日志的最后一个条目，还没有写入日志文件的所对应的改变将丢失。）

 

数据库的正常关闭：通过转储控制文件我们发现数据文件的检查点SCN等于stop SCN。

数据库异常关闭：各部分的checkpoint SCN都一致，但数据文件的stop SCN为无穷大。

数据库的实例恢复：经历两个阶段，前滚和回滚。注意没有提交的事务也是有可能写到日志的，所以需要回滚。没有提交的事务也可能写到数据文件，但是这些事务并没有完成，也就是说和这些事务相关的回滚段还是无法释放的。即使没有提交但是数据仍然可能被写进数据文件，而写进的这些数据文件，如果事务被回滚，那么回滚这些数据的代价是很大的。
oracle使用两个特点来增加回滚的效率：Fast-Start On-Demand Rollback和Fast-Start Parallel Rollback。

Fast-Start On-Demand Rollback：数据库打开后开始新事务，只有新事务视图访问那些被异常终止的事务锁定的记录时，才开始按需回滚。SMON进程充当调度员，使用多个服务进程并行回滚一个事务集。

Fast-Start Parallel Rollback：针对长时间运行未提交的事务，SMON进程自动决定何时开始并行回滚并且自动在多个进程间分散工作。取值有三种：

FALSE:禁用Fast-Start Parallel Rollback

LOW:限制恢复进程不能超过2倍的CPU_COUNT

HIGH:限制恢复进程不能超过4倍的CPU_COUNT

 

bootstrap$: 在数据库mount后alter session set sql_trace=true;获得数据库open的trace文件。tkprof跟踪文件可以看到第一个对象的创建，这个对象就是bootstrap$。oracle在内存中创建bootstrap$的结构，然后从数据文件读取数据到内存中，完成第一次初始化。bootstrap$中实际上是记录了一些数据库系统的基本对象的创建语句。但实际上不是物理的去创建对象。

 

破坏bootstrap$，使用BBED进行修复的案例请参考书的44页。

 

 

第2章 数据库的启动和关闭

 

v$parameter视图无法查询隐含参数。查看隐含参数的查询语句：

在9i从手工建库的脚本中，我们可以看到pfile到spfile的交换。

spfile的创建：SQL>create spfile from pfile； 如果spfile已经存在，创建会报错。oracle运行期间并不锁定spfile，所以这个文件可能丢失，但又无法创建，这时我们可以创建一个自定义名称的spfile，然后重命名为缺省名。

启动数据库可以指定pfile，但不能指定spfile。但在pfile可以链接spfile。举个例子：

pfile文件内容如下：

spfile=‘d：\oracle\ora92\spfilesjh.ora'

log_archive_start=false

--log_archive_start这个参数将覆盖spfile的设置。

 

scope三个可选值：

memory：立即生效，重启无效。   spfile：当前无效，重启后有效。    both：立即有效，重启后仍然有效。等同于不带参数的alter system。 修改静态参数需要指定spfile。

介绍一个视图v$spparameter:通过scope=spfile修改的参数虽然对当前实例无效，但参数值可以通过这个视图来查看。

注意：数据库在关闭的情况下spfile和pfile也可以互相转换。这在spfile出现错误无法启动数据库的情况下特别有用，我们只要通过create pfile from spfile来修改pfile，最后通过create spfile from pfile来用修正过的spfile来启动数据库。spfile是一个二进制文件，不能手工去修改。

 

修改spfile文件中的参数值为缺省值。举个例子：

SQL>alter system reset open_cursors scope=spfile sid='*';    --open_cursors 这个参数将在spfile文件中不存在。

 

spfile文件的备份与恢复：oracle把spfile纳入到了RMAN的备份恢复策略中，如果配置了控制文件的自动备份，那么数据库在发生重大变化时进行控制文件和spfile文件的自动备份。

v$rman_configuration可以看到非默认的RMAN参数值。

自动备份的控制文件及spfile的缺省格式及命名规则：c-IIIIIIIIII-YYYYMMDD-QQ

c:控制文件；  IIIIIIIIII:DBID；    YYYYMMDD:时间   QQ:序号00-FF,十六进制表示。

使用自动备份恢复spfile文件：RMAN>restore spfile to '具体路径' from autobackup;

控制文件也可以这样恢复： RMAN>restore controlfile to '具体路径' from autobackup;

但是如果数据库无法mount，oracle需要用户提供数据库的DBID,才能找到相应的自动备份用以恢复。这时我们需要直接指定备份集：

RMAN>restore controlfile to '具体路径' from 'c-34535345345-20090101-00';

进一步分析：如果数据库无法nomount，那么用上面的方法就不能恢复参数文件了。怎么办？有两种方法：

1.临时编辑一个pfile启动实例，然后恢复spfile；2.用RMAN启动默认实例DUMMY，进行spfile恢复。

 

bug 1725012   ORA-00600:internal error code，arguments：[ttcgcshnd-1],[0],[],[],[],[],[],[]

解决方法：SQL>alter system set event=’10841 trace name context forever’ scope=spfile；

10841事件是解决oracle9i中JDBC Thin Driver问题的一个方法。

 

案例介绍：有人创建了第二个undo表空间，用scope=memory选项(没有修改pfile）将新创建的undo应用。结果数据库再次启动的时候报错。

 

 

 

第3章 数据字典

 

通常说的数据字典由4部分组成：内部表（X$），数据字典表，动态性能视图（V$)，数据字典视图。

3.1内部表（X$):

ORACLE的核心，X$表是加密命名的。oracle文档不作说明。oracle通过X$表建立起大量视图，供用户查询管理数据库之用。比较熟悉的有X$BH,X$KSMSP。X$表在数据库启动的过程中动态创建，且不允许SYSDBA之外的用户访问，显示授权不被允许。

介绍一个内部表x$kvit,这个表记录着一些和实例相关的内部参数设置。

SQL> SELECT KVITTAG,KVITVAL,KVITDSC FROM X$KVIT;

KVITTAG                                                             KVITVAL KVITDSC

kcbldq                                                              25 large dirty queue if kcbclw reaches this

kcbfsp                                                              40 Max percentage of LRU list foreground can scan for free

 

触发DBWR写动作的条件包含以下两个：1，dirty buffers threshold值。这个值是多少了？25%。 2，进程扫描LRU一定数量的block后，还找不到足够的free空间，则触发DBWR，这个扫描数量就是40%。从以上查询我们可以验证。

 

3.2数据字典表：

用以存储表，索引，约束以及其它数据库结构的信息，通常以$结尾，如tab$,obj$,ts$。建库的时候通过运行sql.bsq脚本来创建。sql.bsq在$ORACLE_HOME/rdbms/admin目录下，它包含了数据字典表的定义及注释说明，所以值得DBA去研究。

 

3.3动态视图（v$)

被实时更新反映当前数据库的状态，oracle有v$视图的详细说明文档。

3.3.1 GV$和V$视图

DB启动的时候创建X$表，在此基础上创建了GV$和V$视图，GV$在oracle 8被引进。除了一些特例以外，每个V$对应一个GV$。GV$是为了满足OPS/RAC环境的需要，在OPS环境中GV$返回所有的实例信息。

v$fixed_view_definition 可以用来查看其它视图的定义。

3.3.2 GV_$和V_$视图

在GV$和V$视图的基础上oracle又建立了GV_$和V_$视图，随后为这些视图建立了公用同义词。这部分工作由catalog.sql脚本来完成。oracle这样做的目的是通过V_$视图把V$视图和普通用户隔离开来。V_$可以授权给其它用户，但V$不能直接授权。

SQL> grant select on v$sga to sjh;

grant select on v$sga to sjh

ORA-02030: 只能从固定的表/视图查询。

对于x$,v$的限制，oracle是通过软件机制实现的。所以，大部分用户访问v$并不是视图，而是指向v_$视图的同义词。在进行数据访问时，oracle先访问view优先，然后是同义词。

 

3.4数据字典视图

这种视图是在X$表和数据字典表的基础上创建的。在创建数据库的时候由catalog.sql来创建。这种视图通常分三类：

user_类视图；all_类视图；dba_类视图

 

3.5一些说明

X$表和GV$,V$视图都可以从V$fixed_table中查到。

SQL> select count(*) from v$fixed_table where name like 'X$%';

 

  COUNT(*)

----------

       613

 

SQL> select count(*) from v$fixed_table where name like 'V$%';

 

  COUNT(*)

----------

       396

 

利用v$fixed_view_definition我们可以追踪V$PARAMETER的结构，发现源于x$ksppi和x$ksppcv两个表。这两个表基本上包含DB的所有参数，而V$PARAMETER不包含"_"开头的隐含参数。我们可以通过10046事件来进一步跟踪V$PARAMETER，看看oracle是如何通过同义词定位对象。最后总结一下oracle对于对象名的解析顺序：发出命令的用户模式中是否存在表或视图-》检查私有同义词是否存在-》检查公有同义词是否存在-》报错：ORA-00942:table or view does not exist.

 

 

 

第4章 内存管理

 

4.1SGA管理

SGA是一块用于加载数据，对象并保存运行状态和数据库控制信息的一块内存区域。

SGA的构成：buffer cache；shared pool；redo log buffer；large pool；java pool；streams pool。

buffer cache：9i之前由两个参数决定：db_block_buffers,db_block_size. 9i开始由db_cache_size来决定。db_cache_size为一个粒度（Granule），粒度是9i引进的新概念，是连续虚拟内存分配的单位，其大小取决与SGA_MAX_SIZE参数。如果这个参数值小于128MB，则值为4MB，否则为16MB。Granule大小受隐含参数_ksmg_granule_size的控制。以下这个脚本是获得隐含参数的值：

set linesize 120

col name for a30

col value for a20

col describ for a60

select x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc describ

from SYS.x$ksppi x,SYS.x$ksppcv y

where x.inst_id = USERENV('Instance')

and y.inst_id = USERENV('Instance')

and x.indx=y.indx

and x.ksppinm like '%&par%'

/

 

我在10gR2得到的结果不是这样：

SQL> show parameter sga_max

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------

sga_max_size                         big integer 272M

 

SQL> @getpar_descr.sql

输入 par 的值:  _ksmg_granule_size

原值    6: and x.ksppinm like '%&par%'

新值    6: and x.ksppinm like '%_ksmg_granule_size%'

 

NAME                           VALUE                DESCRIB

------------------------------ -------------------- -----------------------

--------------------------------

_ksmg_granule_size             4194304              granule size in bytes

 

看来10g又有所不同。

oracle提供了buffer cache的多缓冲池技术，主要分三个部分：default；keep；recycle。

keep：存放经常使用的数据。如某张表经常使用就可以将其放入keep池中。

recycle：存放一次性读取的数据，该池的数据倾于即使老化。

这些池的大小设置在9i可以动态修改。9i允许存在多种block_size的表空间，其中db_block_size定义的块的大小被称为主blcok_size.设置的参数为：db_nk_cache_size

缓冲池类型的设置可以通过v$buffer_pool查询。

 

large pool：通常用于MTS，并行计算或RMAN的备份恢复操作。

下面来解析一下SGA各部分的设置：

SQL> show sga

Total System Global Area  285212672 bytes   --受SGA_MAX_SIZE的影响

Fixed Size                  1248552 bytes   --SGA的固定部分，包含DB和instance的状态等通用信息，后台进程需要访问这些信息，不存储用户数据，需要内存很小。

Variable Size              75498200 bytes   --包括shared_pool,java_pool,large_pool的size，SGA_MAX_SIZE减去db_cache_size部分也被归入可变部分。

Database Buffers          201326592 bytes

Redo Buffers                7139328 bytes   --通常比log_buffer参数设置略大。因为log buffer并非按照数据块大小分配，在内存中通常需要设置保护页对log buffer进行保护。

在共享池内存分配和使用的过程中会存在一定的额外消耗，所以shared_pool_size参数设置的值可能和V$SGASTAT显示的值不一样。在10G中这部分消耗被单独列出，可以查询v$sgainfo.

 

SGA与共享内存段

SGA的设置在UNIX上和一个OS内核参数有关，这个参数是shmmax:系统允许的单个共享内存段的最大值。通常推荐将SGA限制在一个共享内存段。在windows系统中，所有oracle server process实际上都是一个进程的线程，所以不存在共享内存的问题，无需特殊设置。我们可以通过ipcs -sa来查看共享内存的分配，用pmap工具查看每个内存共享段的地址空间。如果SGA在多个内存共享段，那么DB在启动的时候会有警告信息。SGA设置的过大超过了物理内存，DB启动的时候也会有告警信息。有时这种告警信息也可能是因为DB异常关闭，后台进程未正常退出，共享内存未及时释放而引起的。这种情况可以通过ipcs命令找到共享内存段id，然后通过ipcrm命令强制释放该共享内存段。

 

SGA管理的变迁

8i：静态管理，需要修改参数文件，重启数据库。

9i：动态管理，但修改的SGA各组件的值的和不能超过SGA_MAX_SIZE的设置。在oracle9iR1中，动态减小内存会触发一些BUG，建议不要在繁忙的生产系统去改。9i推出了一些内存设置建议的功能，同时引进了一系列动态性能视图：SQL> select tname from tab where tname like '%ADVICE%';  db_cache_size的建议受db_cache_ad参数的影响。shared_pool_size的建议受statistics_level的影响。动态修改内存参数会触发等待事件，同时还会获得一个PE类型的锁定。

10g：引入了ASMM(自动共享内存管理），由参数SGA_TARGET参数决定是否开启。可自动调整的SGA组件有：buffer cache；shared pool；java pool；large pool。10g引进了一个新的后台进程MMAN来管理动态调整内存组件。调整的建议来自系统不间断收集的内存建议。我们可以查询v$process来查看这个mman进程（注意v$process的spid字段对应OS的pid）。如果想回到以前手工管理的模式，可以设置SGA_TARGET=0，这时内存各组件值会计入spfile，作为手工管理的初始值。值得注意的是，启用了ASMM后，相关内存参数值会处于未设置状态。在原来的内存参数名称前加两个下划线构成了10g新的参数，同时也决定了当前SGA的分配。这些参数值会被记录到spfile中，在数据库的下次启动依然有效。这些新引入的参数可以通过以下查询获得：

select x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc describ  FROM SYS.x$ksppi x,SYS.x$ksppcv y

WHERE x.inst_id=USERENV('Instance') AND y.inst_id=USERENV('Instance') AND x.indx=y.indx AND x.ksppinm like '%pool_size%';

最后我们可以查询10g新增加的视图v$sga_dynamic_components来获得各动态组件调整的时间和调整类型等信息。

 

4.2PGA管理

PGA概述：程序全局区，是服务器进程（server process）使用的一块包含数据和控制信息的内存区域，PGA是非共享的内存，在server process启动或创建时分配。PGA的内容根据专用模式和共享服务器模式而不同，通常来说PGA包含私用SQL区（存放绑定信息，运行时内存结构等）和session信息等内容。所有server process分配PGA的总和称为Aggregated PGA。

8i：由一些*_area_size参数来控制，比如：sort_area_size;hash_area_size;bitmap_area_size等。可以控制这些参数来控制PGA的使用。

9i:提供了PGA管理的新方法：自动化SQL执行内存管理。oracle可以在不关闭数据库的情况下自动调整SQL内存区。为此oracle也引入了几个新的初始化参数：PGA_AGGREGATE_TARGET;WORKAREA_SIZE_POLICY.

PGA_AGGREGATE_TARGET:所有session可以使用最大的PGA。这个参数仅对专用服务器模式有效，10g开始才对共享服务器连接也有效。

WORKAREA_SIZE_POLICY：是否启用PGA自动管理功能的开关。

对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：MIN(5% PGA_AGGREGATE_TARGET,100MB）。对于并行操作：30% PGA_AGGREGATE_TARGET/DOP  (DOP代表并行度）。

5% PGA_AGGREGATE_TARGET,100MB这样的一个限制可以通过这样的实验来证明：

1.为 PGA_AGGREGATE_TARGET设置不同的大小（alter system set。。。）

2.set autotrace traceonly

3.运行一个有大量排序操作的SQL（select distinct * from table where rownum<1000000)

4.set autotrace off

5.select sql_text,operation_type,POLICY,last_memory_used/1024/1024,last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hashvalue=a.hash_value

  and sql_text='select distinct * from table where rownum<1000000';

6.select  name,value/1024/1024 MB from v$pgastat where name in ('aggregate PGA target parameter','global memory bound');

其实100M这个限制跟隐含参数_pga_max_size有关，也就是说修改这个参数（默认是200M）可以突破100M的限制。

 

PGA内存包含可调整内存和不可调整内存，可调整内存是SQL工作区使用的，其余部分是不可调整内存。

在CBO模式下，PGA_AGGREGATE_TARGET对SQL的执行计划会产生影响。oracle在评估执行计划的时候会根据这个参数评估在sort，hash-join或bitmap操作时能够使用的最大/最小内存，从而选择最优的执行计划。对于这个参数的调整，oracle提供这样一个建议方案：

OLTP：PGA_AGGREGATE_TARGET=（<物理内存>*80%)*20%         DSS: PGA_AGGREGATE_TARGET=（<物理内存>*80%)*50%

9i中v$process视图增加了相应字段来记录进程的PGA耗用。注意：在AIX上单个进程使用的真实内存可能远大于在oracle中看到的PGA大小。

SQL在工作区中以三种方式执行：

Optimal：所有处理在内存中完成。

Onepass：大部分操作在内存中完成。

Multipass：大量操作需要产生磁盘交互，性能极差。

以下这SQL语句可以评判SQL工作区的性能：

select name,value,100*(value/decode((select sum(value) from v$sysstat where name like 'workarea executions%'),0,null,(select sum(value) from v$sysstat where name like 'workarea

executions%'))) pct from v$sysstat where name like 'workarea executions%'

PGA的调整建议可以通过两个视图来查看：v$pga_target_advice  --通过对不同PGA的设置，给出在不同设置下PGA命中率和OverAlloc等信息。

                                   v$pga_target_advice_histogram  --通过不同工作区大小的采样评估提供统计信息供分析使用。

 

4.3 几个诊断案例的介绍

1.SGA&SWAP:sga设置过高，swap空间设置较小，会导致无法建立数据库连接，alert提示无法fork新的数据库进程。对于RMAN小于1G的系统，专用模式下，通常建议oracle的sga一般不超过二分之一物理内存。

SunOS的几个命令：dmesg：查看系统日志信息；top；增加swap分区：mkfile -v 1g swapfile1  ->swap -a swapfile1

在unix中可以通过oerr工具获得oracle的错误号信息：比如 oerr ora 01555

2.SGA设置过高导致OS大量交换，数据库性能严重下降，用户请求得不到及时响应。新的连接不断建立导致连接数很快用完。要注意OS共享内存段的设置，最好不要让SGA超过这个设置。

9.2.0.3这个版本在solaris平台上广泛存在一个bug，即缺省情况下，oracle在异步I/O出现问题时，会连续WARNING 100次，每次间隔10min，1000min后才会给出ORA-27083的错误。9.2.0.4版本后引入一个隐含参数:_aiowait_timeouts,缺省值为100.

3.解决CPU高度消耗的通用办法：

CPU消耗过高往往是由SQL语句引起的。可以通过以下这个SQL语句将问题的SQL抓出来。

select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_value,a.address) IN (

select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,sql_address) from v$session b

where b.paddr=(select addr from v$process c where c.spid='&pid'))

order by piece ASC

--这个查询主要是通过OS的PID和v$process.spid关联，通过v$process.addr和v$session.paddr关联，最后可以跟v$sqltext关联便可获得SQL。如果想获得这个PID的详细跟踪信息，我们还可以使用dbms_system包。如：SQL>exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);

 

 

第5章 Buffer Cache与Shared Pool原理

 

5.1 Buffer Cache原理

数据访问和修改都需要通过buffer cache来完成。一个进程需要访问数据时，首先需要确定数据在内存中是否存在，如果在buffer中存在，则需要根据数据的状态来判断是否可以直接访问还是需要构造一致性读取；如果数据在buffer中不存在，则需要在buffer cache中寻找足够的空间以装载需要的数据，如果找不到足够的内存空间，则需要触发DBWR去写出脏数据，释放buffer空间。

 

5.1.1 LRU与Dirty list

oracle通过几个链表对buffer cache进行管理，这其中包括LRU，Dirty List。各种list上存放指向具体buffer的指针。数据库初始化时，所有的buffer都被hash到LRU list上进行管理，当需要从数据文件上读取数据时，首先要在LRU list上寻找free的buffer，然后读取数据到buffer cache中。当数据被修改后，状态变为dirty，就可以被移动到dirty list（检查点队列)。一个buffer要么在LRU list上，要么在dirty list上。

server进程读数据到buffer cache时，判断数据是否在buffer cache中，如果存在且可用，获取数据，同时根据LRU算法在LRU list上移动该block。不存在，则需要从数据文件获取。 ----》

读取数据前，server进程需要扫描LRU list寻找free的buffer，扫描过程中会把所有已经修改过的buffer移动到dirty list上。                                              ----》

dirty list超过了设定值（25%），server进程就会通知DBWR去写出脏数据。如果server进程扫描LRU超过一个设定值（40%）仍然找不到足够的free buffer，将停止寻找，转而通知DBWR去写脏数据，释放内存空间。同时由于增量检查点的引入，DBWn也会主动扫描LRU list将发现的dirty buffer移到dirty list。这个扫描也受一个内部约束，ORACLE 9iR2中是25%。                       ----》

找到足够的buffer后，可以将数据读入buffer cache                                                                                                             ----》

如果读取的block不满足读一致性需求，则通过回滚段构造前镜像返回给用户。

 

从8i开始，LRU list和Dirty list又分别增加了辅助list，用于提高管理效率。这时，数据库初始化时，buffer首先存放在LRU的辅助list上，当被使用后移动到LRU主list上，这样当用户进程搜索free buffer时，就可以直接从辅助LRUlist上开始，而DBWR搜索dirty buffer时，则可以从主LRU list上开始。我们可以通过以下命令来转储buffer cache的内容：

alter session set events 'immediate trace name buffers level 4';  level共有1-10级。trace文件可能会非常大，建议max_dump_file_size参数设置为UNLIMITED。通过查看buffer cache的内容，除了刚才我们提到的list外，还有很多其他分类的list。

 

5.1.2 cache buffers Lru Chain闩锁的竞争与解决。

当用户进程读数据到cache buffers时，或cache buffers 按照LRU算法进行管理时，就需要扫描LRU list获取可用的buffer或更改buffer状态。所以在搜索的过程中必须获得latch（一种串行锁机制，用于保护共享内存结构),锁定内存结构，防止并发访问损坏内存中的数据。我们可以从v$latch获得cache buffers lru chain的一些信息。cache buffers lru chain latch存在多个子latch，其数量受隐含参数'_db_block_lru_latches'控制。

如果该latch竞争激烈，通常用如下办法解决：

a.适当增大buffer cache，减少读数据到buffercache的机会，减少扫描LRU list的竞争。

b.适当增加LRU latch的数量，修改'_db_block_lru_latches'参数。一般不推荐修改，除非有oracle support支持。

c.使用多缓冲池技术。

 

5.1.3 cache buffer chain闩锁的竞争与解决。

介绍另外两个在cache buffer中的数据结构：Hash Bucket和Cache buffer Chain。

这两个数据结构引入的意义：当oracle需要确定某个block在buffer中是否存在时，如果cache buffer中的所有buffer都通过同一个数据结构管理，那么意味着要遍历整个结构，性能会相当低下。为了效率oracle引入了Bucket的数据结构：把所有的buffer通过一个内部hash算法运算后，存放到不同的Bucket中。这样用户在Bucket中寻找buffer时，只需要通过同样的算法获得hash值就能找到要找的buffer。在Bucket内部，通过cache buffer chain（一个双向链表）将所有的buffer通过buffer header（数据块的文件号，块地址，状态）信息联系起来。

举个例子把这些概念活化：去图书馆（老图书馆）找书，首先看到一排柜子，柜子又分很多小抽屉，抽屉上按照不同的分类方法标注了相关信息，比如按字母顺序，如果要查oracle的书籍，就需要找到有o标记的抽屉。打开抽屉，看到一系列的卡片，这些卡片被一根铁闩串起来，每张卡片会记录书籍的信息（书籍名称，作者，ISBN号，还有一个重要的信息就是书籍存放的书架位置）。这样通过翻阅这些卡片我们就能快速的找到想要的书籍。oracle在buffer中找相应block的原理跟图书馆是一样的。图书馆=cache buffer； 抽屉=Bucket； 抽屉的分类方式=hash运算； 卡片=buffer header； 铁闩=cache buffer chain。

 

产生的问题：如果多个读者都想翻阅这个链上的卡片，那么就产生了cache buffer chain的竞争。从9i开始，对于cache buffer chain的只读访问，其latch可以被共享，如果是写就只能独占了。

 

buffer根据buffer header进行散列，从而最终决定存入哪个hash bucket。hash bucket的数量在一定程度上决定了每个bucket中buffer数量的多少，就就间接影响了搜索性能。oracle在不同的版本中一直在修改算法，优化bucket的数量。bucket数量多，意味着同一时间读者可以拿到不同的抽屉，进行数据访问。但更多的抽屉需要更多的存储空间，更多的管理成本。hash backet的数量受参数_db_block_hash_buckets影响。在oracle7和oracle8中，该参数缺省值为db_block_buffers/4的下一个素数。8i缺省的参数值为db_blcok_buffers*2

8i之前，对于每个hash backet，使用一个独立的hash latch来维护。由于过于严重的热点块竞争，从8i开始_db_block_hash_buckets的数量比以前增加了8倍，而_db_block_hash_latch的增长数量有限，这意味着bucket上的blcok数量减少，少量的latch需要管理更多的bucket。我们可以通过以下命令来转储buffer的内容，查看一下这个chain的数据结构：

SQL>alter session set events 'immediate trace name buffers level 10';

某些chain上可能没有buffer header的信息（标记为NULL)。找一个有信息的chain，注意BH的信息，其中包含“hash:[45432fd0,45432fd0] lru:[53ff3ff2,54ff3e5e]"

hash:[45432fd0,45432fd0] 中的两个数据分别代表X$BH中的NEXT_HASH和PRV_HASH,也就是指同一个hash chain的下一个BH地址和上一个buffer地址。如果chain只包含一个BH，这两个值将同时指向该chain的地址。lru:[53ff3ff2,54ff3e5e]的两个数据分别代表X$BH中的NXT_REPL和PRV_REPL，也就是LRU的下一个buffer和上一个buffer。这里buffer header的双向链的结构就体现出来了。

其实所谓的热点块就是大量进程对相同blcok进行操作，这样必然引发cache buffer chain的竞争，通过增加bucket，打散了buffer的分布，cache buffer chain也增多了。

 

X$BH与buffer header：每个buffer在X$BH都存在一条记录。通过desc x$bh我们可以看到buffer header存储的信息（文件号，块地址，状态），结合dba_extents视图，而已很容易的找到每个buffer对应的对象信息。x$bh还有一个重要字段TCH，表示一个buffer的访问次数越多，该buffer越抢手。TCH值也越高。

 

查找数据库最繁忙的buffer：

select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch DESC) where rownum<11;

 

查询这些热点buffer来自哪些对象：

select e.owner,e.segment_name,e.segment_type from dba_extents e,

(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch DESC) where rownum<11) b

where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk and e.block_id + e.blocks > b.dbablk;

 

oracle 10g之前，数据库的等待事件中，所有latch都归入latch free 等待事件中。

 

热点块竞争的解决：

在top5的等待事件中，latch free是最严重的竞争。但latch free是一个汇总的等待事件，所以我们要从v$latch视图获得具体的latch，到底谁是主要的竞争者。同时statspack report也会存在这样的报告。最后结果得知是cache buffer chains是主要的latch竞争。从v$session_wait中我们也可以看到都是latch free的事件。我们可以从v$latch_children查询具体的子latch的信息，这里是cache buffers chains。我们还可以通过X$BH的字段HLADDR(hash chain latch address）与v$latch_child的addr字段进行关联从而得到latch竞争与数据块的关系，再结合dba_extents可以获得具体的热点竞争对象，最后结合v$sqlarea或v$sqltext找到频繁操作这些对象的SQL。

查找热点块对应的对象，并将操作该对象的SQL找出来：

select sql_text

from v$sqltext a,

(select distinct a.owner,a.segment_name,a.segment_type from

dba_extents a,

(select dbarfil,dbablk

from (select dbarfil,dbablk

from x$bh order by tch desc) where rownum < 11) b

where a.RELATIVE_FNO = b.dbarfil

and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b

where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'

order by a.hash_value,a.address,a.piece;

 

5.2 shared pool的基本原理

shared pool的本质功能就是实现共享（代码的共享和重用）。我们来看看oracle是怎样管理shared pool的：

Shared Pool通过free list管理free块,Free List按不同size划分Bucket

在Oracle8i中,不同bucket的size范围如下所示(size显示的是下边界):

Bucket 0 size=44

Bucket 1 size=76

Bucket 2 size=140

Bucket 3 size=268

Bucket 4 size=524

Bucket 5 size=1036

Bucket 6 size=2060

Bucket 7 size=4108

Bucket 8 size=8204

Bucket 9 size=16396

Bucket 10 size=32780

我们注意,在这里，小于76的块都位于Bucket 0上;大于32780的块,都在Bucket 10上。初始的,数据库启动以后,shared pool多数是连续内存块。当空间分配使用以后,内存块开始被分割,碎片开始出现，Bucket列表开始变长。Oracle请求shared pool空间时，首先进入相应的Bucket进行查找。如果找不到,则转向下一个非空的bucket,获取第一个chunk。分割这个chunk,剩余部分会进入相应的Bucket,进一步增加碎片。

最终的结果是,Bucket 0上的内存块会越来越多,越来越碎小通常如果每个Bucket上的chunk多于2000个，就被认为是share pool碎片过多。Shared Pool的碎片过多，是Shared pool产生性能问题的主要原因。

碎片过多会导致Search Free List的时间过长，从而使shared pool latch被长时间持有（9i之前这latch只有一个），导致更多的Latch竞争。而在大多数情况下,我们请求的都是相对小的chunk,这样搜索Bucket 0往往消耗了大量的时间以及资源。这可能导致share pool Latch被长时间的持有,导致更多的share pool竞争。所以在Oracle9i之前,如果盲目的增大shared_pool_size或设置过大的shared_pool_size，往往会适得其反。这就是经常听到的“过大的shared_pool会带来管理上的负担”

 

在Oracle9i中,Free Lists被划分为0~254,共255个Bucket。每个Bucket容纳的size范围

Bucket 0~199 容纳size以 4 递增。Bucket 200~249 容纳size以 64 递增。从Bucket 249开始,Oracle各Bucket步长进一步增加:

Bucket 249: 4012 ~4107 = 96

Bucket 250: 4108 ~8203 = 4096

Bucket 251: 8204 ~16395 = 8192

Bucket 252: 16396~32779 = 16384

Bucket 253: 32780~65547 = 32768

Bucket 254: >=65548

在Oracle9i中,对于小的chunk,Oracle增加了更多的Bucket来管理。0~199共200个Bucket,size以4为步长递增;200~249共50个Bucket,size以64递增.

这样每个Bucket中容纳的chunk数量大大减少,查找的效率得以提高.这就是Oracle9i中shared pool管理的增强,通过这个算法的改进Oracle8i中,过大shared pool带来的栓锁争用等性能问题在某种程度上得以解决.

其实从8.1.7这个版本开始，oracle就开始分配了255个bucket，只不过递增的细度没有9i这么明显。同时9i增加了对大共享池的支持，shared pool latch已经增加到7个。如果你的系统有4个或4个以上的CPU，并且shared_pool_size大于250MB，那么oracle可以把shared pool分割成多个子缓冲池进行管理，每个subpool都有其独立的结构。我们可以查询v$latch_children来证实，where条件为“name=‘shared pool’。子缓冲的数量由隐含参数_KGHDSIDX_COUNT来决定，我们可以修改这个参数来看效果。

 

注意：9i与shared pool多缓冲池相关的bug有3316003，该bug在9205中得到了修正。

 

其实我们可以从数据库内部监控shared pool的空间碎片情况.这涉及到一个内部视图x$ksmsp。

X$KSMSP的名称含义为: [K]ernal [S]torage [M]emory Management [S]GA Hea[P] 其中每一行都代表着shared pool中的一个chunk。以下介绍几个关键的字段含义：

KSMCHCOM是注释字段,每个内存块被分配以后，注释会添加在该字段中.x$ksmsp.ksmchsiz代表块大小

x$ksmsp.ksmchcls列代表类型，主要有四类，说明如下:

free

Free chunks--不包含任何对象的chunk,可以不受限制的被分配.

recr

Recreatable chunks--包含可以被临时移出内存的对象,在需要的时候，这个对象可以被重新创建.例如,许多存储共享sql代码的内存都是可以重建的.

freeabl

Freeable chunks--包含session周期或调用的对象,随后可以被释放.这部分内存有时候可以全部或部分提前释放.但是注意，由于某些对象是中间过程产生的，这些对象不能临时被移出内存(因为不可重建).

perm

Permanent memory chunks--包含永久对象.通常不能独立释放.

 

我们可以通过查询x$ksmsp视图来考察shared pool中存在的内存片的数量。不过注意:Oracle的某些版本(如:10.1.0.2)在某些平台上(如:HP-UX PA-RISC 64-bit)查询该视图可能导致过度的CPU耗用,这是由于bug引起的.如果数据库系统中存在大量的硬解析，不停请求分配free的shred pool内存，除了必须的shared pool latch等竞争外，还不可避免的会导致shared pool中产生更多的内存碎片。

 

诊断和解决ORA-04031错误。

shared pool的真正问题只有一个：碎片过多带来的性能影响。

什么是ORA-04031错误？当尝试在共享池分配大块的连续内存失败（很多时候是碎片过多，而并非是内存不足）时，oracle首先清除共享池中当前没有使用的所有对象，使空闲内存合并。如果仍然没有足够大的单块内存可以满足要求，就会产生这个错误。几种带来这种错误的情况：

A.内存泄露：9iR2之前很多这种错误都跟内存泄露的BUG有关，如果shared pool size不是设置的很小，就应该是否符合一下bug的特征：

BUG:1397603;1640583;1318267;1193003;2104071;263791.1  详见书的201页。

B.绑定变量和cursor_sharing：共享池中的大量SQL代码导致了过多的内存碎片。主要原因有：SQL没有足够共享；大量不必要的解析调用；没有使用绑定变量。从8.1.6开始提供了一个新的初始化参数用来强行使用绑定变量，这个参数是cursor_sharing。8i的版本使用这个参数可能会带来性能上的提升，但同时也会有一定的副作用，比如优化器无法生成精确的执行计划。所以使用这个参数前最好做完整的测试。到了9i cursor_sharing参数有了三个选项：exact;force;similar。在9.2.0.5中，设置cursor_sharing为similar可能导致SQL的version_count过高的bug。

C.flush shared pool:alter system flush shared_pool;强制刷新共享池。尽量在系统空闲的时候使用，9i共享池的算法发生了改变，已经推荐不再使用。

D.shared_pool_reserved_size和shared_pool_reserved_min_alloc参数。保留共享池空间，用于满足大的连续的共享池空间请求。v$shared_pool_reserved视图记录了保留共享池的使用信息。

E.其它的原因：某些特定的SQL,较大的指针或大的package都可能导致这个错误。我们可以考虑把这个大的对象pin到共享池中。使用DBMS_SHARED_POOL.KEEP。引发ORA-04031错误的因素还有很多，通过设置相关参数如session_cached_cursors,cursor_space_for_time等也可以解决一些性能问题。

 

ORA-04031错误的模拟：

9201-9206的版本在查询v$segstat的时候会引发shared pool的内存泄露，可以利用这个问题来模拟内存泄露。详情参考205页。

 

library cache pin和library cache lock

Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin。Lock比pin具有更高的级别。Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.

锁定主要有三种模式: Null,share,Exclusive.在读取访问对象时,通常需要获取Nullshare(共享)模式的锁定.在修改对象时,需要获得Exclusive(排他)锁定.

在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.同样pin有三种模式,Null,sh(空)模式以及ared和exclusive.只读模式时获得共享pin,修改模式获得排他pin.

通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待.

library cache pin通常发生在编译或重新编译PL/SQL，view,types等object时，因为其他session可能已经把相关对象pin到library cache中了。

举个例子来说明一下library cache pin：

SQL> create or replace PROCEDURE pining

  2  IS

  3  BEGIN

  4          NULL;

  5  END;

  6  /

 

Procedure created.

 

SQL>

SQL> create or replace procedure calling

  2  is

  3  begin

  4          pining;

  5          dbms_lock.sleep(3000);

  6  end;

  7  /

 

Procedure created.

 

--首先执行calling过程,在calling过程中调用pining过程。此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待，直到calling执行完毕.

session 1:

SQL> exec calling

session 2:

SQL> grant execute on pining to eygle;

--此时session 2挂起

 

从v$session_wait入手,我们可以得到哪些session正在经历library cache pin的等待

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state

  2  from v$session_wait where event like 'library%';

 

 

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3 WAIT_TIME SECONDS_IN_WAIT STATE

---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------

   8        268 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301         0               2 WAITING

 

等待3秒就超时,seq#会发生变化

 

SQL>

 

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3 WAIT_TIME SECONDS_IN_WAIT STATE

---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------

   8        269 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301         0               2 WAITING

 

SQL>

 

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3 WAIT_TIME SECONDS_IN_WAIT STATE

---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- --------

   8        270 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301         0               0 WAITING

 

在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示

我们看到,library cache pin等待的对象的handle地址为:52D6730C

通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:

Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

 

select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ

from X$KGLOB

where KGLHDADR ='52D6730C'

/

 

 

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ

-------- -------- -------- ---------- -------------------- ---------- --------

404F9FF0 52D6730C 52D6730C SYS        PINING               2300250318 52D65BA4     

 

这里KGLNAHSH代表该对象的Hash Value 由此我们知道,在PINING对象上正经历library cache pin的等待.然后我们引入另外一个内部视图X$KGLPN:

Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

 

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,

b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ

from v$session a,x$kglpn b

where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0

/

 

  SID USERNAME    PROGRAM                                  ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK

  KGLPNMOD   KGLPNREQ

----- ----------- ---------------------------------------- -------- -------- -------- -------- -------- --------

---------- ----------

   13 SYS         [e-mail] (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8

       2          0

 

通过联合v$session,可以获得当前持有该handle的用户信息.对于我们的测试sid=13的用户正持有该handle

那么这个用户正在等什么呢?

 

SQL> select * from v$session_wait where sid=13;

 

       SID       SEQ# EVENT               P1TEXT            P1 P1RAW    P2TEXT          P2 P2RAW    P3TEXT 

        P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ------------------- --------- ---------- -------- ------- ---------- -------- -------

 ---------- -------- ---------- --------------- -------

        13         25 PL/SQL lock timer   duration      120000 0001D4C0                  0 00             

          0 00                0            1200 WAITING

     

      

Ok,这个用户正在等待一次PL/SQL lock timer计时.得到了sid,我们就可以通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作.

 

 

SQL> select sql_text from v$sqlarea where v$sqlarea.hashvalue='3045375777';

 

SQL_TEXT

--------------------------------------------------------------------------------

BEGIN calling; END;

     

      

这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.

我们这个calling作的工作是dbms_lock.sleep(3000) 也就是PL/SQL lock timer正在等待的原因。至此就找到了Library Cache Pin的原因.

 

简化一下以上查询:

 

1.获得Library Cache Pin等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj

  FROM x$kglob

 WHERE kglhdadr IN (SELECT p1raw

                      FROM v$session_wait

                     WHERE event LIKE 'library%')

/

 

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ

-------- -------- -------- ---------- -------------------- ---------- --------

404F2178 52D6730C 52D6730C SYS        PINING               2300250318 52D65BA4

      

 

2.获得持有等待对象的session信息

SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,

       b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq

  FROM v$session a, x$kglpn b

 WHERE a.saddr = b.kglpnuse

   AND b.kglpnmod <> 0

   AND b.kglpnhdl IN (SELECT p1raw

                        FROM v$session_wait

                       WHERE event LIKE 'library%')

/

SQL>

 

       SID USERNAME   PROGRAM                                          ADDR     KGLPNADR KGLPNUSE

KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ

---------- ---------- ------------------------------------------------ -------- -------- --------

-------- -------- -------- ---------- ----------

        13 SYS        [e-mail] (TNS V1-V3)         404F6CA4 52B2A518 51E2013C

51E2013C 52D6730C 52B294C8          2          0

      

 

3.获得持有对象用户执行的代码

SELECT sql_text

  FROM v$sqlarea

 WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (

          SELECT sql_address, sql_hash_value

            FROM v$session

           WHERE SID IN (

                    SELECT SID

                      FROM v$session a, x$kglpn b

                     WHERE a.saddr = b.kglpnuse

                       AND b.kglpnmod <> 0

                       AND b.kglpnhdl IN (SELECT p1raw

                                            FROM v$session_wait

                                           WHERE event LIKE 'library%')))

/

 

SQL_TEXT

--------------------------------------------------------------------------------

BEGIN calling; END;

      

 

在grant之前和之后我们可以转储一下shared pool的内容观察比较一下:

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';Session altered.

 

在grant之前:从前面的查询获得pining的Handle是52D6730C:

 

******************************************************

BUCKET 67790:

  LIBRARY OBJECT HANDLE: handle=52d6730c

  name=SYS.PINING

  hash=891b08ce timestamp=09-06-2004 16:43:51

  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]

  kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1

--在Object上存在共享pin

--在handle上存在Null模式锁定,此模式允许其他用户继续以Null/shared模式锁定该对象

  lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]

  pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]

  ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]

    LIBRARY OBJECT: object=52d65ba4

    type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0

    DATA BLOCKS:

    data#     heap  pointer status pins change    alloc(K)  size(K)

    ----- -------- -------- ------ ---- ------     -------- --------

        0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55

        4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48

      

 

在发出grant命令后:

 

******************************************************

BUCKET 67790:

  LIBRARY OBJECT HANDLE: handle=52d6730c

  name=SYS.PINING

  hash=891b08ce timestamp=09-06-2004 16:43:51

  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]

  kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1

--由于calling执行未完成,在object上仍让保持共享pin

--由于grant会导致重新编译该对象,所以在handle上的排他锁已经被持有

--进一步的需要获得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出现.

  lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]

  pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]

  ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]

    LIBRARY OBJECT: object=52d65ba4

    type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0

    DATA BLOCKS:

    data#     heap  pointer status pins change    alloc(K)  size(K)

    ----- -------- -------- ------ ---- ------     -------- --------

        0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55

        4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48      

 

实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的:

1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的

exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.

2.以shared模式pin该对象,以执行安全和错误检查.

3.共享pin被释放,重新以排他模式pin该对象,执行重编译.

4.使所有依赖该过程的对象失效

5.释放exclusive lock和exclusive pin

 

library cache lock

如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:

session 3:

 

 

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

 

SQL> alter procedure pining compile;

 

此进程挂起,我们查询v$session_wait视图可以获得以下信息:

 

SQL> select * from v$session_wait;

 

 SID SEQ# EVENT               P1TEXT                  P1 P1RAW    P2TEXT               P2 P2RAW   

P3TEXT              P3 P3RAW     WAIT_TIME SECONDS STATE 

---- ---- ------------------- --------------- ---------- -------- ------------ ---------- --------

---------------- -------------- ---------- ------  ---

  11  143 library cache pin   handle address  1390239716 52DD5FE4 pin address  1387617456 52B55CB0

100*mode+namespace 301 0000012D          0       6 WAITING

  13   18 library cache lock  handle address  1390239716 52DD5FE4 lock address 1387433984 52B29000

100*mode+namespace 301 0000012D          0       3 WAITING

   8  415 PL/SQL lock timer   duration        120000     0001D4C0                       0 00      

                    0 00                0      63 WAITING

....

 

13 rows selected     

 

由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.

我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现.所以应该尽量避免在高峰期进行以上操作.另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现.所以在应用开发的过程中,我们也应该注意这方面的内容.

 

诊断案例1：version_count过高造成的latch竞争解决。

消息队列拥堵的时候开始诊断：

查询v$session_wait发现大量的latch free等待。通过v$latch查看latch free等待的是哪些latch，最后得知竞争最严重的是shared pool和library cache。先来说一下SQL的执行过程中，这两个latch在SQL解析过程中起的作用。

1.首先需要获得library cache latch，根据SQL的HASH_VALUE值在library cache中寻找是否存在可共享代码，如果找到则为软解析，转向第4步，否则是硬解析。

2.释放library cache latch，获取shared pool latch，查找并锁定自由空间。

3.释放shared pool latch ，重新获得library cache latch，将SQL及执行计划插入到library cache中。

4.释放library cache latch，保持NULL模式的library cache pin/lock.

5.开始执行。

可以通过v$sysstat视图获得数据库解析的详细信息。过多的shared pool和library cache竞争，极有可能是SQL的过度解析造成的。进一步检查v$sqlarea，条件设置为version_count>1000;最后发现version_count有7K多个，也就是说这个SQL有7k多个子指针。这意味着这些子指针都将存在于同一个bucket的链表上。也就是说同样的SQL再次执行，oracle将不得不搜索这个链表以寻找可以共享的SQL，这将导致大量的library cache latch的竞争。举个简单的例子：两个用户有两张相同名称的表，现在同时查询表，这条SQL将存在两个指针，显然代码是不能共享的。所以应该尽量避免。最后发现这个问题是一个bug引起的。

bug：1210242  修改timed_statistics为false。还有个方法是设置隐含参数_sqlexec_progression_cost.具体可见metalink：note 62143.

对于9i来说，version_count过高的问题，可以查询v$sql_shared_cursor视图。如果是正常因素导致的，相应的字段会标记为Y，对于异常的情况，查询的结果可能是N。另外cursor_sharing参数也很重要，本案例中它被设置为similar，这导致了大量的指针不能共享。当cursor_sharing为similar时，并且数据库存在相关的柱状图信息时，对于每条新执行的SQL，oracle都会通过硬解析以获得更为精确的执行计划。

 

诊断案例2：临时表引发的竞争。

问题：DB里的几个存储过程总是跑不完，同样的存储过程在其他地方的数据库都很正常，数据库没有锁。正常的话几分钟就能运行完，现在都N个小时了还没运行完。

解决思路：查看v$session_wait.发现DB目前正经历library cache pin和library cache lock的等待和竞争。运行上面提到的SQL找出持有pin的用户在执行truncate talbe ppt的操作。ppt是公共的临时表。truncate临时表时不适当的请求了排他锁（理论上是无需排他锁定的）。该问题主要在多用户交叉访问时引起的，所以建议不同的用户改用独立的临时表。

 

 

 

第6章 Redo

 

以一个update语句来看看redo所要记录的内容： update emp set sal=4000 where empno=100;

1.检查empno=100这条记录在buffer cache中是否存在，如果不存在则要读取到buffer cache中。

2.在回滚段表空间的相应回滚段段头的事务表上分配事务槽，这个操作需要记录redo日志，这是改变向量1.

3.从回滚段读入或在buffer cache中创建sal=2000的前镜像，这个操作也需要记录redo日志，这是改变向量2.

4.修改sal=4000，这是改变向量3.

5.用户提交时，在redo log buffer记录提交信息，并在回滚段标记该事务为inactive。这是改变向量4.

 

可以转储日志文件来获得详细的信息，在日志文件的内容中我们可以看到这4个改变向量对应CHANGE #1-4.重做日志相对于数据块的修改并写出的量显得相当精简。它只记录重构事务的必须信息（事务号，文件号，块号，行号，字段）。

 

查看redo的生成量：

1.在sql*plus中使用autotrace。

2.查询v$mystat获得当前session的统计信息。 select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name='redo_size';

3.查询v$sysstat获得DB启动以来全局的redo生成量。根据总的生成量估算每天产生的日志量。

select(select value/1024/1024/1024 from v$sysstat where name='redo size')/(select round(sysdate - (select startup_time from v$instance)) from dual) redo_per_day from dual;

 

查看归档的生成量：

DB如果在归档模式下，由于其他因素的影响，redo的生成量并不代表归档日志的大小，但也可以做为参考。关于归档的生成量，可以查询V$archived_log.例如查询某天的归档量：

select trunc(COMPLETION_TIME),sum(Mb)/1024 DAY_GB from (select name,COMPLETION_TIME,BLOCKES*BLOCK_SIZE/1024/1024 Mb from v$archived_log

where COMPLETION_TIME between trunc(sysdate) - 2 and trunc(sysdate) - 1) group by trunc(COMPLETION_TIME);

如果查看近期的归档量，直接去掉where条件就可以了。

 

redo写的触发条件：

1.3S超时。LGWR执行写操作，log file parallel等待事件出现。

2.redo log buffer 1/3满。

3.redo log buffer具有1MB的脏数据。

后两个阈值受隐含参数_log_io_size的影响。缺省为1/3 log buffer大小，上限值为1MB，此参数在X$KSPPSV中显示为0，表示缺省值。这就是为什么经常有人推荐log buffer的设置建议为3M，因为两个条件都能满足。

 

用户的提交：

当事务提交时，redo会写入磁盘。在事务返回成功标志给用户之前，必须等待LGWR写完成。在递归调用中仅需要同步返回给用户调用之前的最后一次commit操作。存在一个SGA变量记录redo线程需要同步的log buffer number。如果在唤醒LGWR之前发生多个提交，此变量记录最高的log block number，在此之前的redo都将写入磁盘，这就是组提交。

 

DBWn写之前：

如果DBWR将要写出的数据的高RAB超过LGWR的on-disk RBA，则DBWR将通知LGWR去执行写出。8i之前这种情况会产生log file sync等待事件。从8i开始DBWR将这些block放入一个Defer队列，同时通知LGWR执行redo写出，DBWR可以继续执行无需等待的数据写出。

 

log buffer的大小设置：以3M为调整开端，如果log buffer space等待事件比较严重，就需要增大log buffer。

 

获得日志的块大小：redo block size是在源码种固定的，与OS相关。获得日志块的大小有多种方法：

1.select max(lebsz) from x$kccle;

2.$dbfsize redo01.log

3.转储日志文件。

 

日志文件大小在不同版本的演变：oracle8i-》1M  oracle9iR2-》100M  oracle10g R1 -》10M。

 

为什么热备期间产生的redo要比正常的多？

主要是要在热备期间解决split block的问题，需要在日志文件中记录修改的行所在的数据块的前镜像，而不仅仅是修改信息。

split block：oracle的数据块由多个OS块组成，热备时，使用的是文件系统的cp命令进行拷贝，所以用的FS的blocksize读取数据文件。那么就有这样一种情况：当copy数据文件的同时，数据库正好向数据文件写数据。这使得拷贝的文件中包含这样的DB block，它的一部分来自数据库向数据文件写操作前，另一部分来自写操作后。对于DB来说这个block本身是不一致的，是一个分裂块。所以热备状态下，对于变更的数据，日志文件需要记录这个变化数据块的前image。这样在恢复的过程中如果出现split block，oracle就可以通过日志文件中的数据块前image覆盖备份，完成恢复。

在数据库内部有个隐含参数_log_blocks_during_bakcup来决定热备期间是否允许redo中记录数据块的信息，默认是true。当然如果数据库块的大小等于OS块的大小，可以设置为false（很少有这种情况）。RMAN在热备份的时候因为会反复读取获得一致的block，从而可以避免split block的生成，不会产生额外的redo。建议在繁忙的生产库中采用RMAN备份。

 

 

能否不生成redo？

可以使用nologging的环境非常有限，以下操作中，可以增加nologging子句：

1.创建索引或重建索引。

2./*+ APPEND */提示，使用直接路径批量insert操作或sql*loader直接路径加载数据。

3.CTAS方式创建数据表时。

4.大对象（LOB)的操作.

5.一些alter table操作，如move，split等。

是否生成redo跟数据库模式，表模式，插入模式都有关系。通常建议在进行了nologging的操作后，需要对数据库进行备份。oracle内部有个隐含参数_disable_logging，用来控制是否关闭日志记录。主要为了测试也其它一些特殊目的。这个参数是动态的。值得注意的是在9.2.0.6.0版本中，设置该参数会触发BUG 3868748，该bug导致数据库无法启动。修正这个bug的是在oracle10gR2。另外如果在归档模式下，设置该参数会导致日志文件损坏，因为在设置该参数后，归档进程无法识别该日志文件格式。通常在生产库和DG中建议启用FORCE LOGGING：SQL>alter database force logging;

 

redo故障恢复

1.非活动日志组的故障恢复

SQL>alter database clear logfile group 1;  --日志组1的恢复

SQL>alter database clear unarchived logfile group 1;   --还未完成归档的情况。

 

2.活动或当前的日志文件丢失的恢复

2.1丢失当前日志时，数据库是正常关闭的。

8i：对所有的日志组执行这个操作：SQL>alter database clear logfile group n;

9i: 无法对当前日志clear。可用通过until cancel恢复后，用resetlogs方式打开数据库： SQL>recover database until cancel;

 

2.2丢失当前日志时，数据库是异常关闭的。

只能用不完全恢复，丢失的当前日志文件对应的数据会丢失。如果没有备份怎么办？可以设置隐含参数_allow_resetlogs_corruption来强制打开数据库，忽略一致性问题，打开库后，oracle建议导出数据，然后重新建库，导入数据。这个参数将使用所有所有数据文件最旧的SCN打开数据库，所以通常要保证system表空间拥有最旧的SCN。如果不幸，在open的过程中会遇到一系列的ORA-600错误。

 

诊断案例一：通过clear日志恢复数据库。

数据库不能归档，但空间足够。查询v$archive_dest发现当前归档的路径是错误的（error），检查报警日志发现是日志的损坏，由于多次归档不成功导致数据库将归档路径标记为error，使得后续的日志同样无法归档。由于该日志组并非current日志，可以通过clear的方法清除该日志内容。

 

诊断案例二：日志组过度激活的诊断。

响应缓慢，应用请求已经无法返回。通过查看v$log发现除current外都是active，显然DBWR的写已经无法跟上log switch触发的检查点。检查DBWR的繁忙程度，该进程并不消耗CPU。用IOSTAT检查IO，发现数据库所在的卷的写速度只有500K/s左右，这是不正常的。正常情况下数据库的随机写都在1-2M/s左右，检查阵列发现是RAID5坏了一块盘。

 

 

 

 

第6章 回滚与撤销

 

6.1基础知识：

平均事务回滚率，如果过高说明数据库经历了太多无效操作，极大的影响了数据库性能。每个数据块头部都会记录一个提交的SCN，当数据更改提交后，提交SCN同时被修改，这个SCN在查询时可以用来进行一致性读判断。当我们进行大规模数据删除的时候，推荐分批删除分次提交，以减少对回滚段的冲击。

9i之前回滚段表空间创建后，oracle随后创建回滚段供数据库使用，也可以手工创建或删除回滚段进行维护。也可以在开始事务前，通过以下命令指定用户想用的回滚段：

set transaction use rollback segment <rollback_segment_name>

可以通过查询dba_rollback_segs来查询这些回滚段的状态。

从9i开始引入自动管理的undo表空间，如果使用这个特性,用户不再能够创建或删除回滚段，也不需为事务指定回滚段。

undo_suppress_errors: 当使用自动管理模式时，如果使用不再支持的操作时（如为事务指定回滚段）是否返回出错信息。true表示不返回错误信息，操作无效但可以继续，false表示操作不能继续。在10g的版本这个参数已被舍弃。

undo_retention:自动管理模式下，当回滚段变得非激活之后，回滚段中的数据在被覆盖前保留的时间，单位是秒。9iR2中默认是10800（3小时）。缺省下DB创建时初始化10个回滚段，可以通过v$rollname查询得到。回滚段的动态创建和释放，这是AUM的优势之一。

 

6.2深入研究

获得事务的信息：v$transaction,v$rollstat.  XIDUSN字段代表几号回滚段，XIDSLOT代表几号事务槽。通过v$rollname和USN字段可以查询回滚段的名称。通过alter system dump undo header '<回滚段名>'来转储回滚段头的信息。其中state为10代表了是活动事务，DBA指向的是这个事务单位前镜像的数据块地址（0x00800055），转为二进制就是32位二进制数。其中前10位代表文件号，后22位代表block号。所以DBA信息可以告诉我们数据文件号和block号。为了深入研究，我们继续update两条记录。将前镜像所在的数据块dump出来：alter system dump datafile 2 block 85;

注意参数：irb:0x3f。irb指的是回滚段中记录的最近未提交变更开始之处，回滚的起点。查看回滚的偏移量，最后一个偏移地址正是0x3f。在trace文件中找到Rec #0x3f的信息：rci 0x3e ... col 5：[3]

c2 1d 33。c2 1d 33转换成十进制就是2850，这是最后更新记录的前镜像。rci代表的就是undo chain的下一个偏移量，此处为0x3e，找到0x3e的信息，这里也有col信息转换为十进制就是2450，第二条更新的数据，继续观察rci得到第一条更新的记录。到最后undo chain的指针为0x00，表示这是最后一条记录。这些数据块也可以通过x$bh来查询：

select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state from x$bh a,dba_extents b where b.RELATIVE_FNO=a.dbarfil and b.BLOCK_ID<=a.dbablk and b.block_id+b.blocks>a.dbablk and b.owner='SCOTT' and b.segment_name='EMP';

class为4的是段头，class为1，块号为23642的是数据块，此时查询为提交的数据，oracle需要构造一致性读，通过前镜像把变化前的数据展现给用户。再次运行上面的查询，可以看到buffer cache有多一个数据库，其中state为3，这就是前镜像。

我们继续来分析数据块：在前镜像信息中记录了更改的数据块地址：bdba：0x00405c5a，转换成二进制然后得知正是file 1 block 23642。dump这个block，我们可以获得ITL事务槽信息（事务必须获得ITL事务槽才能对数据块进行修改）。ITL内容主要包括：Xid--Transaction ID；  Uba--Undo block address；  Lck--Lock status。 其中xid的组成是这样的：回滚段号.slot.wrap#

对Uba进行分解：前镜像的地址.seq.irb    到这里我们发现在数据块上同样存在指向回滚段的事务信息。

 

块清除：由于oracle在数据块上存储了ITL和锁定等事务信息，所以oracle必须在事务提交后清除这些事务数据，这就是块清除。块清除主要清除的数据有行级锁，ITL信息（包括提交标志，SCN等）。如果提交时修改过的数据块仍然在buffer cache中，那么oracle可以清除ITL信息，这叫快速块清除，快速块清除还有个限制，当修改的块的数量超过buffer cache的10%,则对超出的部分不再进行快速清除，而是等到下次访问这些block的时候再清除。如果提交事务时，修改过的数据块已经被写回到数据文件上（或超过10%的那种情况），再次读出block进行修改，显然成本过高，这种情况，orale将选择延迟块清除。

接着上面那个实验我们验证一下：继续更新记录，不提交然后强制刷新buffer cache：alter session set events='immediate trace name flush_cache'; oracle这时将执行延迟块清理，dump出数据块，发现ITL事务信息仍然存在。提交事务，事务表释放，如果此时查询记录，数据库将产生延迟块清除。注意这时产生了物理读和redo，这个redo就是因为延迟块清除导致的，再次查询，则不会产生redo。再次dump该block，发现ITL事务信息已清除，但Xid，Uba信息还存在。行的锁定信息也清除了。再来看提交后undo的信息：回滚段事务表标记该事务为非活动，再看irb指向了一个新的值（偏移量），但前镜像的信息仍然存在。这说明通过某种手段我们依然可以获得这个信息（闪回查询）。

 

一点猜测：在commit前，强行将buffer cache的内容刷新到数据文件。当查询触发延时块清除时，oracle需要去查询回滚段获得该事务的提交SCN，如果前镜像被覆盖且查询SCN小于回滚段中记录的最小提交SCN，那么oracle将无法判断查询SCN和提交SCN的大小，这时就会出现错误。但

 

使用errorstack进行错误跟踪：可以在session级或系统级设置errorstack事件，设置这个事件后，oracle将出错信息记录跟踪文件。

SQL>alter system set events '1555 trace name errorstack level 4';

 

ORA-01555错误：引起这个错误的原因：1.过长的查询；2.延迟块清除；3.使用sqlldr直接方式加载。

9i引入undo_retention参数，使当事务提交后，回滚段中的数据保留时间，但如果undo表空间不足，oracle会置undo_retention参数不顾。

10g开始默认引入自动的undo_retention调整，oracle满足最大允许长时间的查询，不需要用户调整。这个新特性的引入伴随两个隐含参数：_undo_autotune; _collect_undo_stats; 10g开始增加了guarantee控制，如果因undo表空间不足，那么新事务会因空间不足而失败，而不是选择之前的覆盖。

另外，oracle提供了10203事件来跟踪数据库的块清除操作：alter system set event="10203 trace name context forever" scope=spfile;

 

怎样重建undo表空间：在进行DB迁移的时候（同平台），由于undo空间过大，不打算迁移，准备新建。

启动的时候会报找不到undo表空间的数据文件的错误。可以删除文件启动数据库：alter database datafile 'undo.dbf' offline drop;

然后alter database open;重建undo表空间，并切换：create undo tablespace undotbs datafile '*.dbf' size 100M;

alter system set undo_tablespace='undotbs';

 

通过切换释放过度扩展的表空间：创建新的undo表空间undo2 -》通过v$rollstat来确认原来的回滚表空间所有的回滚段都正常的offline。 -》删除原来的undo表空间drop tablespace undo1 including contents;

 

很多情况我们使用隐含参数强制打开数据库，可能会出现ORA-00600 [4194]的错误。4194通常说明undo段出了问题，如果没有备份我们可以通过特殊的初始化参数强制启动。首先确认当前回滚段的名称，从alert文件获得：undo segment 11 onlined undo segment 12 onlined。对应AUM下的回滚段名称为：'_SYSSMU11$','_SYSSMU12$' 修改参数文件，使用隐含参数 _corrupted_rollback_segments将回滚段标记为损坏，强制启动数据库。._corrupted_rollback_segments=：'_SYSSMU11$','_SYSSMU12$'  数据库正常open后，重建UNDO表空间，删除出问题的表空间。最后修改参数文件，变更undo表空间，取消_corrupted_rollback_segments参数。再次启动数据库。最后建议exp全库导出。

 

 

第8章 等待事件

 

8.1系统有多少等待事件？

SQL>select count(*) from v$event_name;

v$event_name中的parameter1 2 3非常重要，对于不同的等待事件参数的意义各不相同。可以通过v$system_wait_class来获得主要等待事件的等待时间和等待次数信息：

SQL>select * from v$system_wait_class order by time_waited;

 

8.2空闲等待事件有哪些？

SQL>select * from stats$idle_event;

 

8.3获取各进程等待事件：当系统运行缓慢的时候可以用下列语句，看看系统正在等待什么。

SQL>select sid,event,p1,p1text from v$session_wait;

 

从v$session和v$session_wait可以发现系统的瓶颈。10g中oracle对v$session视图进行了加强，把原来v$session_wait的中的所有字段都整合到了v$session中。同时还将v$dba_waiters中的blocking_session字段加入进来。sql_trace等字段内容也加了进来。

 

8.4捕获进程等待事件的SQL，通过session的sid获得SQL

SQL>select sql_text from v$sqltext a where a.hashvalue=

    (select sql_hash_value from v$session b where b.SID='&sid')

    order by piece ASC;

 

8.5查询某个表索引的键值

SQL>select index_name,column_name from user_ind_columns where table_name='表名';

 

v$session_wait记录的信息很重要，但它的信息随session的消失而消失，想获得DB的历史状态及session的历史等待信息是不可能的。然而从10g开始，这一切发生改变。

v$session_wait_history:记录活动session最近10次的等待事件。

ASH的介绍：ASH以v$session为基础，每秒采样一次，记录活动会话等待的事件，采用工作由后台进程MMNL完成。ASH的启用和采用间隔受两个隐含参数的影响：_ash_enable; _ash_sampling_interval;

ASH的信息在内存中滚动，可以通过v$active_session_history来访问，对于每个活动的session，每次采样会在这个视图记录一行信息。这部分内存在SGA中分配,ASH buffers的最小值为1M，最大不超过30M。

产生ASH报告的方式有两种：脚本；OEM。

脚本：$ORACLE_HOME/rdbms/admin/ashrpt.sql    OEM:点击“运行ASH报告”

 

AWR的介绍：内存中的ASH信息最后写入磁盘。AWR以固定时间间隔（默认1小时）为重要统计信息和负载信息执行一次快照，并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间（默认一周），然后被清除。

AWR的采样由后台进程MMON每60分钟执行一次，ASH的信息同样会被采样写出到AWR负载库。虽然ASH buffers被设计成保留1小时的信息，但这个内存是不够的。当ASH buffer写满后，MMNL进程会将ASH信息写出，写出的时候需要过滤，数据量一般是采样数据的10%，写出通过direct path insert完成，减少日志生成。

 

WRH$_ACTIVE_SESSION_HISTORY是v$active_session_history在awr的存储地，它是一个分区表。v$active_session_history中记录的信息会定期（每小时）的刷新到AWR，并缺省保留一个星期用于分析。DBA_HIST_ACTIVE_SESS_HISTORY是WRH$_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现，通过这个视图进行历史数据的展现。AWR的信息在10g中存储在SYSAUX表空间。

 

ADDM的介绍：根据AWR的信息自动进行诊断。

 

关于等待事件的一些信息可以参考我的另一篇笔记：性能调整心得。这里主要介绍enqueue，latch。enqueue等待常见的有ST,HW,TX,TM等。ST enqueue用于空间管理和字典管理的表空间的区间分配。在DMT中典型的是对与uet$和fet$数据字典表的争用。推荐使用LMT或手工预分配一定数量的extent，减少动态扩展时发生的严重队列竞争。

redo copy latch：一个进程在修改数据时产生redo，redo首先在PGA中保存，当进程需要将redo信息copy进入redo log buffer时，需要获得redo copy latch。

redo allocation latch：分配redo空间需要。在一个繁忙的生产系统，该latch通常也是竞争激烈的latch之一。在9iR2中，通过log_parallelism定义oracle中的redo allocation的并发级别。如果大于1，数据库将分配多个redo log buffer区域，每个区域都按log_buffer大小分配。并行redo的生成能够增加更新密集型数据库的吞吐量。通过以下查询来获得redo allocation latch竞争的累计等待时间：

select substr(ln.name,1,20),gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln

where ln.name in('redo allocation','redo copy') and ln.latch#=l.latch#;

如果misses对gets的比率超1%，或者immediate_misses 对（immediate_gets+immediate_misses ）的比率超1%，那么通常认为存在latch竞争。

当主机拥有16-64个CPU时，oracle公司推荐设置log_parallelism在2-8之间。9iR2的缺省值为1。10g中，log_parallelism变为隐含参数，且引入了另外两个参数并允许log_parallelism动态调整，缺省_log_parallelism_dynamic为true。_log_parallelism_max设置为不同于_log_parallelism的参数值。

 

8.6段级统计信息的收集－》v$segment_statistics

   收集的统计信息类数－》v$segstat_name (9iR2为11个，10g15个)

 

8.7通过v$sql_plan可以获得大量的有用信息（记录Library Cache中SQL语句的执行计划）

   获取全表扫描的对象：

   SQL>select distinct object_name,object_owner from v$sql_plan p

       where p.operation='TABLE ACCESS' and p.options='FULL'

       and object_owner='CHERVON80'

 

8.8获取全索引扫描对象：

   SQL>select distinct object_name,object_owner from v$sql_plan p

       where p.operation='INDEX' and p.options='FULL SCAN';

 

8.9查找全表扫描的SQL语句

   select sql_text from v$sqltext t,v$sql_plan p

   where t.hashvalue=p.hash_value

   and p.operation='TABLE ACCESS' AND p.options='FULL'

   order by p.hash_value,t.piece;

 

8.10查找FAST FULL INDEX扫描的SQL语句可以参考以下语句：

    select sql_text from v$sqltext t,v$sql_plan p

    where t.hashvalue=p.hash_value

    and p.operation='INDEX' AND p.options='FULL SCAN'

    order by p.hash_value,t.piece;

 

 

 

第9章 性能诊断与SQL优化

 

9.1使用AUTOTRACE

9i：运行utlxplan脚本，该脚本用来创建plan_table表。为该表创建public同义词，并授权给public: SQL>create public synonym plan_table for plan_table; SQL>grant all on plan_table to public;

运行plustrce.sql创建plustrace角色。DBA用户首先被赋予了plustrace角色，然后可以手工把plustrace赋予public，这样所有用户也具有了使用autotrace的权限。SQL>grant plustrace to public;

set autotrace的几个常用选项：

set autotrace off:不生成autotrace报告。

set autotrace on explain：只显示优化器执行路径报告。

set autotrace on statistics: 只显示执行统计信息。

set autotrace on: 显示执行计划和统计信息。

set autotrace traceonly: 同autotrace on，但不显示查询输出。

 

为了获得格式化或更友好的输出，可以使用dbms_xplan。举例：

SQL>explain plan for select count(*) from dual;

SQL>@?/rdbms/admin/utlxplp;

实质上utlxplp调用了dbms_xplan，从脚本内容就可以看出：select * from table(dbms_xplan.display());

 

10g:增加了一个PLAN_TABLE$字典表，然后基于这个字典表创建公用同义词供用户使用。9i的上述操作对10g来说都是自动的。

 

使用autotrace功能的另一个好处就是能发现各种视图的底层表，可以作为深入研究oracle的一个手段。启动autotrace在数据库内部，实际上启动了2个session连接，一个用于查询操作，另一个用于记录执行计划和输出最终结果。结合v$session,v$process，这通常可以作为一个进程可能对应多个session的范例来讲。通过10046事件的设置，我们可以看到详细的过程。

 

使用autotrace辅助优化SQL的例子： select * from sys_user where user_code='zhangyong' or user_code in (select grp_code from sys_grp where sys_grp.user_code='zhangyong')

通过autotrace发现执行计划的逻辑读很高。改写后的SQL如下：

select * from sys_user where user_code='zhangyong'

union all

select * from sys_user where user_code<>'zhangyong' and user_code in (select grp_code from sys_grp where sys_grp.user_code='zhangyong')

 

一个诊断案例：系统运行缓慢，已影响到了正常的使用。

vmstat发现CPU已经耗尽。--》top没有发现明显过高的CPU使用进程，排除单进程异常CPU消耗的问题。 --》观察当前系统进程的数量：ps -ef|grep ora|wc -l 在持续增加。--》基本判断数据库或应用出现问题导致进程任务无法完成。--》查看各进程等待事件：select sid,event,p1,p1text from v$session_wait; 发现大量的db file scattered read，db file sequential read等待。--》找出引起这些全表扫描的SQL。这里用到一个脚本：select sql_text from v$sqltext a where a.hashvalue=(select sql_hash_value from v$session b where b.SID='&sid') order by piece ASC

-->获得相关SQL后，用autotrace检查SQL的执行计划，发现使用了全表扫描，而这个表有22w行记录。--》获得整个系统全表扫描的情况：select name,value from v$sysstat where name in('table scans(

short tables)','table scans(long tables)'); -->观察表的索引和索引键值，主要通过user_indexes,user_ind_columns来获得。发现有个字段有很好的区分度，但没有建立索引。 --》建立索引，系统恢复正常。

 

对于大小表的定义，oracle内部提供了一个参数_small_table_threshold。缺省值为2%的buffer数量。小于这个值为小表，反之为大表。区分大小表的目的是全表扫描可能引起buffer cache的抖动，缺省下，大表的全表扫描被置于LRU的末端，以尽快老化。从8i开始，ORACLE有了多缓冲池管理技术，使内存使用更加有效。

 

使用SQL_TRACE/10046事件进行数据库诊断。

可以启用实例级别的SQL_TRACE，也可以启用session级别的SQL_TRACE，还可以对特定的session启用SQL_TRACE。实例级别的SQL_TRACE要谨慎使用，需要保证以下条件：

a.至少25%的cpu idle。b.为user_dump_dest分配足够的空间。c.条带化磁盘减轻IO负担。

使用sql_trace前，有两个参数要设置一下：tiemd_statistics=true;  max_dump_file_size=unlimited（9i已经默认是unlimited了）

 

当前session的设置：SQL>alter session set sql_trace=true;  要跟踪的语句；  SQL>alter session set sql_trace=false;

跟踪其他session的设置： SQL>exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,true)   session执行任务  SQL>exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,false)

 

如果要对其他用户session的参数进行设置，需要用到dbms_system的另外的过程。举例：

SQL>begin

    sys.dbms_system.set_bool_param_in_session(SID,SERIAL#,'timed_statistics',true);

    sys.dbms_system.set_int_param_in_session(SID,SERIAL#,'max_dump_file_size',unlimited);

    dbms_system.set_sql_trace_in_session(SID,SERIAL#,true)

    end;

    /

 

10046事件的设置： 10046是oracle的内部事件，是对SQL_TRACE的增强。它分4个级别：

level 1: 等价于sql_trace

level 4: 等价于level 1+绑定值

level 8: 等价于level 1+等待事件跟踪

level 12:等价于level 1+level 4 +level 8

 

全局设置：在参数文件增加：event="10046 trace name context forever,level 12"

当前session的设置：SQL>alter session set events '10046 trace name context forever';   SQL>alter session set events '10046 trace name context forever,level 8';

SQL>alter session set events '10046 trace name context off';

对其他session的设置：SQL>exec dbms_system.set_ev(SID,SERIAL#,10046,8,'USERNAME');   SQL>exec dbms_system.set_ev(SID,SERIAL#,10046,0,'USERNAME');  

 

获取跟踪文件的脚本：

SELECT    d.VALUE

       || '/'

       || LOWER (RTRIM (i.INSTANCE, CHR (0)))

       || '_ora_'

       || p.spid

       || '.trc' trace_file_name

  FROM (SELECT p.spid

          FROM v$mystat m, v$session s, v$process p

         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

       (SELECT t.INSTANCE

          FROM v$thread t, v$parameter v

         WHERE v.NAME = 'thread'

           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

       (SELECT VALUE

          FROM v$parameter

         WHERE NAME = 'user_dump_dest') d

 

 

获取当前session设置的参数： 当通过alter session的方式设置了SQL_TRACE，这个设置是不能通过show parameter方式获得的。需要通过dbms_system.read_ev来获得，如下：

SQL>set feedback off

SQL>set serveroutput on

SQL>declare

    event_level number;

    begin

     for event_number in 10000..10999 loop

     sys.dbms_system.read_ev(event_number,event_level);

     if(event_level>0) then

       sys.dbms_output.put_line(

     'Event'||

     to_char(event_number)||'is set at level'||to_char(event_level));

     end if;

   end loop;

  end;

 /

 

案例1：SQL中存在潜在的数据类型转换导致索引失效。使用函数也能导致索引失效。这些是我们需要注意的。

案例2：利用sql_trace可以跟踪后台递归操作。

案例3：10046比sql_trace强，它能提供非常有用的等待事件。提到等待事件，有几个视图需要提到：v$session_wait;v$system_event.

 

增大db_file_multiblock_read_count的设置，会使全表扫描成本降低，CBO会更倾向于是用全表扫描而不是索引访问。

 

案例4：物化视图的一个案例。

系统出现临时表空间不能扩展。尝试捕获引发排序的SQL：

select /*+ rule */ distinct a.SID,a.process,a.serial#,TO_CHAR(a.logon_time,'YYYYMMDD HH24:MI:SS') LOGON,a.osuser,TABLESPACE,b.sql_text

from v$session a,v$sql b,v$sort_usage c

where a.sql_address=b.address(+) AND a.sql_address=c.sqladdr

查看排序SQL的执行计划，发现该SQL调用了3个底层表，逻辑读很高，排序查询的表实际是个视图。通过DBA_VIEWS获得这个视图的定义。这个视图调用了三张记录很多的表。结合业务逻辑根据视图定义创建

物化视图。创建物化视图后，依然对其进行了全表扫描。我们可以根据orader by后面的子句创建降序索引（直接在字段后加入desc）。（降序索引本质上基于函数索引的，只有在CBO下才能被用到）降序索引的定义可以在$DBA_IND_EXPRESSIONS或USER_IND_EXPRESSIONS视图中获得。

 

案例5：当出现checkpoint not complete时，需要关注一下IO,用iostat观察一下，读写速率是否正常。