老熊的三分地-Oracle、UNIX、数据恢复

大家都知道Oracle 10g的dbms_stats包与Oracle 9i相比，功能增强了很多，比如增加了display_cursor这个过程，能够查看V$SQL_PLAN视图中的执行计划，如果在statistics_level参数设置为ALL，或者执行的sql使用了gather_plan_statistics hint，则在sql执行后，会在v$sql_plan_statistics_all视图中查到SQL的执行统计信息，例如逻辑读，物理读等等。这些数据对于性能诊断有着非常大的帮助。同时v$sql_plan中的执行计划，与通过EXPLAIN PLAN得到的执行计划相比，前者是oracle执行sql时真正使用的执行计划，而后者则可能不是真正的执行计划；同时有的时候，执行过的sql使用了绑定变量，而oracle在解析sql时通常会进行绑定变量窥探，这个时候我们不能使用EXPLAIN PLAN来得到那个sql的执行计划，就算得到的跟那个sql的真实的执行计划是不一样的，所以有时我们更愿意直接从v$sql_plan中得到执行计划。

但是在oracle 9i中的dbms_xplan包没有display_cursor这个过程。不过，本文根据一个开源软件SQLT中得到的一段脚本，经过修改后，能够显示v$sql_plan和v$sql_plan_statistics中的执行计划和sql的执行统计数据。点击此处下载display_cursor_9i代码

下面是使用这个代码的示例：

SQL> select /*+ sqla */ count(*) from t1 where a<13;

  COUNT(*)
----------
     40000

在另一个会话中，得到这个SQL的hash_value , child_number以及在v$sql_plan中的执行计划。

SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

HASH_VALUE CHILD_NUMBER
---------- ------------
1742773495            0

SQL> @display_cursor_9i 1742773495 0
原值  268:   s_hash_value := &1;
新值  268:   s_hash_value := 1742773495;
原值  269:   s_child_num := &2;
新值  269:   s_child_num := 0;

HASH_VALUE: 1742773495   CHILD_NUMBER: 0
---------------------------------------------------------------------------------------------
select /*+ sqla */ count(*) from t1 where a<13

Plan hash value: 3724264953

------------------------------------------------------------
| Id   | Operation           | Name |  Rows | Bytes | Cost |
------------------------------------------------------------
|    0 | SELECT STATEMENT    |      |       |       |   25 |
|    1 |  SORT AGGREGATE     |      |     1 |     3 |      |
| *  2 |   TABLE ACCESS FULL | T1   | 44444 |  133K |   25 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(”A”<13)

PL/SQL 过程已成功完成。

如果我们将statistics_level设置为ALL（注意：在oracle 9i中gather_plan_statistics这个hint无效），重新执行这个SQL：

SQL> alter session set statistics_level=all;

会话已更改。

SQL> select /*+ sqla */ count(*) from t1 where a<13;

  COUNT(*)
----------
     40000

在会话2中重新进行之前的查询，只不过由于参数的参数，这个SQL有两个子游标，这次执行的游标其child_number为1：

SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

HASH_VALUE CHILD_NUMBER
---------- ------------
1742773495            0
1742773495            1

SQL> @display_cursor_9i 1742773495 1
原值  268:   s_hash_value := &1;
新值  268:   s_hash_value := 1742773495;
原值  269:   s_child_num := &2;
新值  269:   s_child_num := 1;

HASH_VALUE: 1742773495   CHILD_NUMBER: 1
-------------------------------------------------------------------------------------------------------------------
select /*+ sqla */ count(*) from t1 where a<13

Plan hash value: 3724264953

----------------------------------------------------------------------------------------------------------------
| Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      0 |      1 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      0 |  44444 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(”A”<13)

PL/SQL 过程已成功完成。

不幸的是，在另一个会话中查询v$sql_plan_statistics_all的一些结果并不正确。只有在那个执行SQL的会话（就是例子中的会话1）中，才能得到正确的结果：

----------------------------------------------------------------------------------------------------------------
| Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      1 |      1 |      1 | 00:00:00.39 |     155 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      1 |  44444 |  40000 | 00:00:00.21 |     155 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------

如果v$sql_plan_statistics_all有数据，则这个脚本会生成上面的第2个示例的结果，否则，会得到示例1的结果。从输出的结果来看，朋友们，是不是与dbms_xplan的输出惊人地相似啊！

在这里只是测试了最简单的SQL，实际上这个脚本对于并行，CPU成本，TEMP临时表空间使用等数据都能够显示。有兴趣的朋友可以自己试试。

前两篇文章（TAF PartI和TAF PartII）主要描述了在TAF发生故障转移时正在执行SELECT时的行为。本文将观察当故障转移发生时如果正在执行DML和DDL语句的行为。

本文将继续使用上一篇文章TAF PartII同样的测试环境。

我们先测试当会话update时，在另一个会话立即start force实例：

SQL> update t1 set object_name=lpad(’x',40,’x'),created=sysdate;
update t1 set object_name=lpad(’x',40,’x'),created=sysdate
*
ERROR 位于第 1 行:
ORA-25408: 无法安全重放调用

SQL> select * from dual;

D
-
X

SQL> select failed_over from v$session where sid=(select sid from v$mystat where rownum=1);

FAI
---
YES

可以看到，在数据库实例重启完成后，会话报“ORA-25408: 无法安全重放调用”错误。为什么会报这个错。我的猜测是，由于在FAILOVER后，会话实际上是重新来执行这个SQL，但是DML语句不像SELECT一样，前者是对当前时间点的数据进行修改，保证数据修改是最新的；而SELECT的机制是保证查询的时间点一致，DML语句在重新执行时，当前时间点的数据已经跟上次执行时的数据可能不相同了，不能保证是相同的，也就是“不安全”的。

从上面的输出可以看到，在会话报错之后，仍然可以执行查询，并且从结果来看表明会话是failover后的新会话。注意，ORA-25408这个错误是在failover之后才报的，并不是数据库实例DOWN下后马上就报错。

如果我们在数据库实例级别开启sql trace，在failover后我们可以从trace文件里面可以发现，UPDATE语句并没有再次被解析和执行，而不是在UPDATE重新执行然后报错。

下面我们来看看DDL语句的情况：
Read the rest of this entry »

接上文，在这一节中我们来观察一下使用TAF发生故障转移时，Oracle让Select语句能够继续执行的一些现象。

测试环境跟上文一样，但是TNSNAME配置有些变化，增加了“RETRY”和“DELAY”，增加的选项是为了避免在failover时，如果正在执行SQL，客户端报ORA-03113错误：

XTY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XTY)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180) (DELAY = 5)         
      )
    )
  )

首先，我们在TEST用户下建一个测试的表，同时将系统的sql_trace参数设置为TRUE：

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> alter system set sql_trace=true scope=spfile;

System altered.

在客户端，使用sqlplus连接数据库，连接的用户名为TEST。连接后，执行下面的语句：

SQL> spool 1.txt
SQL> select rownum,object_id,owner,object_name from t1 where rownum< =5000 order by object_id;

在客户端显示输出的过程中，在另一个会话重启数据库：

SQL> startup force;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 100666664 bytes
Database Buffers 62914560 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

正在查询T1表的会话，在数据库重启时，会短暂地停止，在我的测试中，停止时显示的输出为：

   1140       1186 SYS                            V_$WAITSTAT

在数据库重启完成后，sqlplus继续输出数据，直到完成所有输出：

1094       1140 SYS                            V_$LOGHIST 
1095       1141 PUBLIC                         V$LOGHIST  
1096       1142 SYS                            V_$SQLAREA 
……（输出很多，略过）…..

我们在生成的sql trace文件中，可以看到下面的内容：

PARSING IN CURSOR #3 len=87 dep=0 uid=55 oct=3 lid=55 tim=1208634047724259 hv=533322034 ad=’2999e344′
select rownum,object_id,owner,object_name from t1 where rownum< =5000 order by object_id
END OF STMT
PARSE #3:c=40994,e=476949,p=301,cr=172,cu=0,mis=1,r=0,dep=0,og=1,tim=1208634047724244
EXEC #3:c=0,e=131,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1208634047724505
FETCH #3:c=12998,e=12276,p=29,cr=64,cu=0,mis=0,r=1,dep=0,og=1,tim=1208634047736908
FETCH #3:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,tim=1208634047745324
FETCH #3:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,tim=1208634047745861

可以看到，发生会话failover重新连接之后，又重新执行了我们测试的那个SQL。但是输出的结果表明，客户端自动跳过了前面已经得到的数据。

我们继续做下一个测试。这一次我们将SQL修改一下：
Read the rest of this entry »

一套运行在AIX 5.3，HACMP 5.4上的双节点RAC数据库,CRS和数据库的版本均为10.2.0.3；CRSD.BIN自动重启，并产生很多的CORE DUMP。
进入$ORA_CRS_HOME/log/<nodename>/crsd目录下，查看crsd日志文件，可以看到CRSD在重启时的日志如下：

2009-03-13 13:59:49.692: [ OCRRAW][11400]proprdc: backend_ctx->prop_sctx=[0x1017ed10]
2009-03-13 13:59:49.692: [ OCRRAW][11400]proprdc: backend_ctx->prop_sltsmx=[0x0]
2009-03-13 13:59:49.692: [ OCRRAW][11400]proprdc: backend_ctx->prop_sclsctx=[0x10365848]
2009-03-13 13:59:49.692: [ OCRRAW][11400]proprdc: backend_ctx->prop_ctx_ocrctx=[0x10362940]
2009-03-13 13:59:49.692: [ OCRSRV][11400]th_snap:8:failed to take backup prop_retval=26
2009-03-13 13:59:51.480: [ CRSOCR][11240]32OCR api procr_open_key failed for key CRS.CUR.ora!wydb3!ons. OCR error code = 3 OCR error msg:
2009-03-13 13:59:51.480: [ CRSOCR][11240][PANIC]32Failed to open key: CRS.CUR.ora!wydb3!ons(File: caaocr.cpp, line: 319)

2009-03-13 14:00:06.282: [ default][1][ENTER]32
Oracle Database 10g CRS Release 10.2.0.3.0 Production Copyright 1996, 2004, Oracle. All rights reserved
2009-03-13 14:00:06.282: [ default][1]32CRS Daemon Starting
2009-03-13 14:00:06.282: [ CRSMAIN][1]32Checking the OCR device
2009-03-13 14:00:06.285: [ CRSMAIN][1]32Connecting to the CSS Daemon
2009-03-13 14:00:06.705: [ CRSD][1]32Daemon Version: 10.2.0.3.0 Active Version: 10.2.0.3.0
2009-03-13 14:00:06.705: [ CRSD][1]32Active Version and Software Version are same
2009-03-13 14:00:06.705: [ CRSMAIN][1]32Initializing OCR
2009-03-13 14:00:06.713: [ OCRRAW][1]proprioo: for disk 0 (/dev/rwbsdb_ocr1_128m), id match (1), my id set (550012785,1028247821) total id sets (1), 1st set (550012785,1028247821), 2nd set (0,0) my votes (2), total votes (2)
2009-03-13 14:00:06.809: [ CRSD][1]32ENV Logging level for Module: allcomp 0

列出目录中的所有文件可以看到，最近几天都有core dump文件。仔细查看可以发现，每个core dump文件的间隔周期为固定的8.5小时。这是一个周期性的现象。

仔细查看上面的日志，”红色字体“的部分，引起了我的注意。这个错误，表明是在做某个东西的备份的时候报了错。应该是在备份OCR。core dump产生的周期和crsd自动重启的周期，都是固定的，这跟CRS会自动周期性地备份OCR比较吻合。

在METALINK上一番搜索，找到了BUG 5893629与这个故障现象比较符合。METALINK对这个BUG的描述称，CRS在备份OCR时，会在$ORA_CRS_HOME/cdata/<cluster_name>目录下生成一个temp.ocr，如果这个文件之前已经存在，会试图删除这个文件，而如果这个文件由于不是root用户所有，则就会导致crsd crash。这个BUG要在10.2.0.4才会修复。

根据对这个BUG的描述，进入到$ORA_CRS_HOME/cdata目录，这个目录下面有两个目录，一个是“crs”，另一个是”localhost“，一般来说，crs就是CRS集群的名称。进入这个目录，没有发现任何文件，看权限，root用户也可以建立文件。

问题在哪里呢？换一个角度思考，既然BUG描述中称不能删除旧的temp.ocr文件会导致crsd crash，那要是没有$ORA_CRS_HOME/cdata/<cluster_name>这个目录会怎么样，一样不能建立temp.ocr这个文件，那这种情况也会不会crash？这里$ORA_CRS_HOME/cdata目录下只有crs和localhost目录，如果按这种推理，如果cluster_name不是通常的”crs“呢？

不过当时有其他事情，离开了现场，不能得到CRS集群名字。不过幸运的是，我在日志文件中，在上面贴出的那段信息的前面，经过了一大段一大段的无用的信息之后，找到了下面的日志：

2009-03-13 13:59:49.391: [ OCRRAW][11400]rbkp:1: could not create the temp backup file [/oracle/crs/cdata/wydb_crs/temp.ocr]
2009-03-13 13:59:49.391: [ OCRRAW][11400]proprseterror: Error in accessing physical storage [26] Marking context invalid.

这里的日志就更明显了，就是不能创建”/oracle/crs/cdata/wydb_crs/temp.oc“这个文件。而前面的推断却不幸言中，CRS集群名字真的不是默认的”crs”，/oracle/crs/cdata这个目录下也的确没有wydb_crs这个目录。

让数据库维护人员在/oracle/crs/cdata目录中新建一个目录wydb_crs，经过一段时间的观察，故障不再出现，问题解决。

从这个故障中，我们得到一个教训，CRS有时真的很傻，为什么在备份时像这种不能创建文件的错误，不是写个日志忽略过去而是直接将进程crash了，并且还产生了core dump？为什么在安装CRS时，安装软件不自动建好这个目录，而只是按默认的”crs”名字建立目录？看来安装ORACLE软件的时候，CRS集群名字取默认值就行了。严重怀疑那个BUG是不是真的会解决这个问题（根据METALINK的描述，在备份时会随机生成一个文件名，而不再是固定的temp.ocr，但如果像这个案例中的故障，仍然会得不到解决。）

TAF，透明应用故障转移（Transparent Application Failover），是Oracle数据库提供的一项高可用特性，普遍应用于RAC环境中，当然也可以用于Data Guard和传统的HA实现的主从热备的环境中。

TAF中的Transparent和Failover，点出了这个高可用特性的两大特点：

• TAF是用于故障转移的，也就是切换。当Oracle连接的会话由于数据库发生故障不可用时，会话能够自动切换到RAC中的其他可用的节点上，或者切换到Standby上面，或者切换到HA方式中的另一个可用的节点上面。
• TAF的故障转移，对应用来说是透明的，应用系统不需要进行特别的处理就能够自动进行故障转移。

但是，TAF是完美的吗？是不是使用了TAF，应用就能真的无缝地进行切换呢？对应用和数据库有没有其他什么要求？要回答这些问题，我们需要全面地了解、掌握TAF。我始终认为，要用好一个东西，首先得掌握这个东西背后的工作原理与机制。

首先来看看Failover。Failover有两种，一种是连接时Failover，另一种则是运行时Failover。前者的作用在于，应用（客户端）在连接数据库时，如果由于网络、实例故障等原因，连接不上时，能够连接数据库中的其他实例。后者的作用在于，对于一个已经在工作的会话（也就是连接已经建立），如果这个会话的实例异常中止等，应用（客户端）能够连接到数据库的其他实例（或备用库）。

首先，TAF是ORACLE客户端提供的一项特性。使用TAF，对客户端的环境有一定的要求。比如JAVA的JDBC驱动、Oracle客户端的版本等（8i开始支持TAF）。这个问题将在本系统文章的后面部分详细描述。

下面看一个有趣的例子：
Read the rest of this entry »

Oracle数据文件的坏块，可分为物理坏块和逻辑坏块。物理坏块（也可以称为介质坏块）指的是块格式本身是坏的，块内的数据没有任何意义。而逻辑坏块，指的是块内的数据在逻辑是存在问题。比如说索引块的索引值没有按从小到大排列。物理坏块一般是由于内存问题、OS问题、IO子系统问题和硬件引起，逻辑坏块一般是是由于Oracle Bug等原因引起。

Oracle数据文件的每个块，其块头为20字节。其定义如下：（来自于DSI401）

struct kcbh
{
    ub1 type_kcbh; /* block type */
    ub2 frmt_kcbh; 
    ub1 spare1_kcbh;
    ub1 spare2_kcbh;
    krdba rdba_kcbh; /* relative DBA */
    ub4 bas_kcbh; /* base of SCN */
    ub2 wrp_kcbh; /* wrap of SCN */
    ub1 seq_kcbh; /* sequence # of changes at the same scn */
    ub1 flg_kcbh; 
    ub2 chkval_kcbh;
};  
  

在块头中，seq_kcbh（占用1字节，块头偏移14)有着特殊的含义，如果该值为0xff，则表示该块被标记为corruption。

下面我们做一个测试：

SQL> create table test.t1 as select * from dba_objects;

表已创建。

SQL> select header_file,header_block from dba_segments where segment_name=’T1′ and owner=’TEST’;

HEADER_FILE HEADER_BLOCK
----------- ------------
         10         1445

修改db_block_checksum参数值为TRUE，关闭数据库，我们用ultraedit修改10号文件的1447块的check sum（一个随便>0的数）及flag=0×04。然后再打开数据库。再执行下面的查询：

SQL> select count(*) from test.t1;
select count(*) from test.t1
*
ERROR 位于第 1 行:
ORA-01578: ORACLE 数据块损坏（文件号10，块号1447）
ORA-01110: 数据文件 10: ‘D:\ORACLE\ORADATA\XJ\TEST01.DBF’

由于非系统表空间在db_block_checksum参数设为FALSE时，会忽略checksum的检查。所以这里为了测试的方便设置为TRUE。
从上面的错误信息来看，块号1447这个块已经坏了，报的错误是经典的ORA-01578错误。
Read the rest of this entry »

某省电信在做批扣（批销）时，出现严重的性能问题，发现下面这一条SQL性能非常低下：

SELECT A.ACCT_BALANCE_ID,
       A.BALANCE_TYPE_ID,
       A.ACCT_ID,
       NVL(A.SERV_ID, -1) SERV_ID,
       NVL(A.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
       A.OBJECT_TYPE_ID,
       F.PRIORITY,
       A.BALANCE,
       NVL(A.CYCLE_UPPER, -1) CYCLE_UPPER,
       NVL(A.CYCLE_LOWER, -1) CYCLE_LOWER,
       NVL(A.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
       NVL(A.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
       B.ADJUST_FLAG ADJUST_FLAG,
       B.ALLOW_TRANS ALLOW_TRANS,
       B.CORPUS_FLAG,
       NVL(TO_CHAR(A.EFF_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EFF_DATE,
       NVL(TO_CHAR(A.EXP_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EXP_DATE,
       A.STATE,
       TO_CHAR(A.STATE_DATE, ’YYYYMMDDHH24MISS’) STATE_DATE,
       B.BALANCE_TYPE_NAME,
       NVL(C.ACCT_NAME, ’ ’) ACCT_NAME,
       NVL(D.ACC_NBR, ’ ’) SERV_NAME,
       NVL(E.ITEM_GROUP_NAME, ’ ’) ITEM_GROUP_NAME
  FROM (SELECT ACCT_BALANCE_ID,
               BALANCE_TYPE_ID,
               ACCT_ID,
               NVL(SERV_ID, -1) SERV_ID,
               NVL(ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
               OBJECT_TYPE_ID,
               BALANCE,
               NVL(CYCLE_UPPER, -1) CYCLE_UPPER,
               NVL(CYCLE_LOWER, -1) CYCLE_LOWER,
               NVL(CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
               NVL(CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
               EFF_DATE,
               EXP_DATE,
               STATE,
               STATE_DATE,
               0 SHARE_RULE_PRIORITY
          FROM ACCT_BALANCE
         WHERE ACCT_ID = :LACCTID
           AND BALANCE > 0
           AND BALANCE_TYPE_ID != 1
           AND STATE = ’10A’
        UNION
        SELECT A1.ACCT_BALANCE_ID,
               A1.BALANCE_TYPE_ID,
               B1.ACCT_ID,
               NVL(B1.SERV_ID, -1) SERV_ID,
               NVL(B1.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
               A1.OBJECT_TYPE_ID,
               A1.BALANCE,
               NVL(B1.UPPER_AMOUNT, -1) CYCLE_UPPER,
               NVL(B1.LOWER_AMOUNT, -1) CYCLE_LOWER,
               NVL(A1.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
               NVL(A1.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
               A1.EFF_DATE,
               A1.EXP_DATE,
               A1.STATE,
               A1.STATE_DATE,
               B1.PRIORITY SHARE_RULE_PRIORITY
          FROM ACCT_BALANCE A1, BALANCE_SHARE_RULE B1
         WHERE A1.ACCT_BALANCE_ID = B1.ACCT_BALANCE_ID
           AND B1.ACCT_ID = :LACCTID
           AND A1.BALANCE > 0
           AND A1.BALANCE_TYPE_ID != 1
           AND A1.STATE = ’10A’
           AND NVL(B1.EFF_DATE, SYSDATE) < = SYSDATE
           AND NVL(B1.EXP_DATE, SYSDATE) >= SYSDATE) A,
       BALANCE_TYPE B,
       ACCT C,
       SERV D,
       A_BALANCE_ITEM_GROUP E,
       A_BALANCE_OBJECT_TYPE F
 WHERE A.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
   AND A.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
   AND A.ACCT_ID = C.ACCT_ID
   AND A.SERV_ID = D.SERV_ID(+)
   AND A.ITEM_GROUP_ID = E.ITEM_GROUP_ID(+)
 ORDER BY F.PRIORITY,
          B.PRIORITY,
          A.SHARE_RULE_PRIORITY ASC,
          A.EXP_DATE ASC,
          A.EFF_DATE ASC,
          A.BALANCE ASC

查看执行计划：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    |  Name                       | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                             |  2723G|   696T|       |  7776M|       |       |
|   1 |  SORT ORDER BY                               |                             |  2723G|   696T|  1503T|  7776M|       |       |
|*  2 |   HASH JOIN                                  |                             |  2723G|   696T|    59M| 39355 |       |       |
|*  3 |    HASH JOIN                                 |                             |   228K|    56M|       | 23918 |       |       |
|   4 |     TABLE ACCESS FULL                        | BALANCE_TYPE                |     8 |   184 |       |     7 |       |       |
|*  5 |     HASH JOIN                                |                             |   228K|    51M|       | 23907 |       |       |
|   6 |      TABLE ACCESS FULL                       | A_BALANCE_OBJECT_TYPE       |     4 |    16 |       |     7 |       |       |
|*  7 |      HASH JOIN OUTER                         |                             |   228K|    50M|    43M| 23896 |       |       |
|*  8 |       HASH JOIN OUTER                        |                             |   228K|    40M|    38M| 23199 |       |       |
|   9 |        VIEW                                  |                             |   228K|    36M|       |  2043 |       |       |
|  10 |         SORT UNIQUE                          |                             |   228K|    11M|    38M|  2043 |       |       |
|  11 |          UNION-ALL                           |                             |       |       |       |       |       |       |
|* 12 |           TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE                |   228K|    11M|       |    50 | ROWID | ROW L |
|* 13 |            INDEX RANGE SCAN                  | IDX_ACCT_BALANCE_ACCT_ID42  |   121K|       |       |     3 |       |       |
|  14 |           NESTED LOOPS                       |                             |     1 |   146 |       |     4 |       |       |
|* 15 |            TABLE ACCESS FULL                 | BALANCE_SHARE_RULE          |     1 |   109 |       |     2 |       |       |
|* 16 |            TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE                |     1 |    37 |       |     2 | ROWID | ROW L |
|* 17 |             INDEX UNIQUE SCAN                | PK_P_ACCT_BALANCE2          |     1 |       |       |     1 |       |       |
|  18 |        PARTITION RANGE ALL                   |                             |       |       |       |       |     1 |    63 |
|  19 |         TABLE ACCESS FULL                    | SERV                        |    12M|   258M|       | 14070 |     1 |    63 |
|  20 |       TABLE ACCESS FULL                      | A_BALANCE_ITEM_GROUP        |   244 | 11224 |       |     7 |       |       |
|  21 |    PARTITION RANGE ALL                       |                             |       |       |       |       |     1 |    63 |
|  22 |     TABLE ACCESS FULL                        | ACCT                        |    11M|   239M|       |  8505 |     1 |    63 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(”A”.”ACCT_ID”=”C”.”ACCT_ID”)
3 - access(”A”.”BALANCE_TYPE_ID”=”B”.”BALANCE_TYPE_ID”)
5 - access(”A”.”OBJECT_TYPE_ID”=”F”.”OBJECT_TYPE_ID”)
7 - access(”A”.”ITEM_GROUP_ID”=”E”.”ITEM_GROUP_ID”(+))
8 - access(”A”.”SERV_ID”=”D”.”SERV_ID”(+))
12 - filter(”ACCT_BALANCE”.”BALANCE”>0 AND “ACCT_BALANCE”.”BALANCE_TYPE_ID”<>1 AND “ACCT_BALANCE”.”STATE”=’10A’)
13 - access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))
15 - filter(”B1″.”ACCT_ID”=TO_NUMBER(:Z) AND NVL(”B1″.”EFF_DATE”,SYSDATE@!)< =SYSDATE@! AND
NVL("B1"."EXP_DATE",SYSDATE@!)>=SYSDATE@!)
16 - filter(”A1″.”BALANCE”>0 AND “A1″.”BALANCE_TYPE_ID”<>1 AND “A1″.”STATE”=’10A’)
17 - access(”A1″.”ACCT_BALANCE_ID”=”B1″.”ACCT_BALANCE_ID”)

Read the rest of this entry »

在安装oracle时，oracle会提示输入sysdba用户组名，凡是以操作系统认证方式的连接，如果连接的用户在这个指定的用户组中，就可以用sysdba用户登陆。一般情况下，也是建议的sysdba用户组为dba。

有的时候，也存在这样一种情况，安装oracle软件的时候，输入了错误的sysdba用户组名（主要是由于创建oracle用户时指定到了错误的其他用户组中，现实中这种情况曾经出现过）。这样一来，就会导致不规范的安装，或者oracle后来修改回正确的用户组(dba），将导致oracle用户不能以sysdba权限连接数据库，甚至不能启动数据库）。不规范的安装，如果将安装的oracle软件tar到其他机器，会导致那个机器的oracle也出现oracle不能以sysdba连接的问题（有的人安装oracle时，如果有现成的，他就喜欢用tar的方式）。

如果指定了错误的sysdba用户组名，怎么样快捷修改而又不用重新安装oracle软件呢？既然在安装时可以指定，那么一定会有一个地方，存储了这个配置，这个存储的文件就是$ORACLE_HOME/rdbms/lib/config.c。

我们看看config.c这个文件的内容：
[oracle@xty lib]$ cat config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User’s Guide for further information. */

#define SS_DBA_GRP “dba”
#define SS_OPER_GRP “dba”

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

从这个文件中，可以看到SYSDBA的用户组为”dba”，而SYSOPER的用户组也为”dba”。如果我们把#define SS_DBA_GRP “dba”这一行改为#define SS_DBA_GRP “adm”，然后执行下面的命令：relink all，再用oracle用户连接数据库时：

[oracle@xty ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Jan 10 15:30:17 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL> connect / as sysoper
Connected to an idle instance

可以看到，oracle用户已经不能够用sysdba权限连接到数据库，也就更谈不上启动数据库了。但是由于sysoper的用户组没有改变，所以oracle仍然是可以用sysoper权限连接数据库并启动数据库的。（不过如果我们不使用操作系统认证，比如通过网络连接的方式用SID连接，并提供sys用户的密码，使用密码文件认证，也一样能够使用sysdba权限连接）

如果修改用户oracle的用户组为adm，则可以使用sysdba用户连接了。
[root@xty ~]# usermod -G adm oracle
oracle用户重新登陆到服务器上

[root@xty ~]# su - oracle
[oracle@xty ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Jan 10 15:42:47 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

最后，我们把config.c修改回原来的值(”dba”)，再relink all，再把oracle的用户组修改回dba，oracle也就恢复了原状。

在数据库服务器上，如果采用操作系统认证，而oracle用户又不能以sysdba权限连接(ORA-01031错误），在其他可能都排除后，有必要检查一下config.c，以确认sysdba用户组是正确的。

再过1小时，2008年就要过去了。
别了，2008
这一年里，发生了太多的事。
咱平民小百姓，不谈国家大事。谈谈自己的就好了。

2008年，在成都安定了下来。上班搞自己喜欢搞的ORACLE，下班回家陪陪父母、老婆和孩子。这日子，挺不错。
2009年，我会干什么呢？大家都会说计划没有变化快。看起来，我不用计划那么多事情了。唯一计划的是，为以后的发展储备更多的能量吧。

别了，2008
欢迎你，2009

QQ群里有位兄弟提出一个很有意思的问题，在一个db_cache_size为1.6G，几乎没有什么活动的数据库（版本为9.2.0.1)里面，一个简单的查询SELECT * FROM T，反复执行，这个查询的物理读始终很高。被查询的表不大，完全可以容纳在cache里面。根据直觉，频繁被读取的块，是应该cache在内存中的，不应该会有物理读，并且当时数据库没有其他的活动，cache中的块也不会被挤出去。

这个现象是违反直觉的，但注意，直觉的东西不一定不是正确的东西。那么为什么那个简单的SQL，在反复执行的情况下，怎么会有那么高的物理读呢？

这还得从Oracle的buffer cache管理说起。一个oracle进程在做全表扫描时，buffer会放置到LRU-AUX链表的尾端。如果在读块时，如果已经没有free buffer，那么进程就会查找可以被age out的块，这个查找过程是从LRU-AUX链表的尾端开始的。也就是说LRU-AUX链表尾端的buffer，总是第一个被替换的。

基于Oracle的这种LRU算法，我们假设，现在系统中已经没有free buffer，也就是buffer cache已经用完。实际上这种情况是最常见的，必竟现在的库相对于db_cache_size来说，总是大很多倍，buffer cache很快就会被填满。那么，在做全表扫描时，第1次多块读(multiblock read)的buffer会放置到LRU-AUX链表的尾端（不管这个buffer是从哪里得到的），第2次多块读时，需要buffer时，oracle也会从LRU-AUX链表的的尾端开始进行查找可以被替换的buffer，而刚好LRU-AUX链表的尾端的buffer正是上一次多块读时的buffer。也就是说前一次读到的块，很快就会被后面读的块替换掉了。

因此，不难理解，为什么在数据库活动很少时，反复扫描同一个表（这里不是同时扫描），其物理读仍然很高。并不是我们直觉的那样，这个表已经很“热”，应该全部在内存中命中。

下面我们通过一个测试来验证我们的推断。

测试环境：
Oracle 10.2.0.3 32Bit for Linux
我们设置如下的参数，以便让buffer cache的大小在我们控制的范围内：

SQL> alter system set shared_pool_size=100m;

System altered.

SQL> alter system set db_cache_size=30m;

System altered.

SQL> alter system set sga_target=200m;

Read the rest of this entry »