一.介绍常驻连接池(Database Resident Connection Pool，Oracle DRCP)
数据库驻留连接池是Oracle Database 11g的一个新特性，专门为了解决在需要支持大量连接的环境对可扩性的迫切需求而设计的。数据库驻留连接池把数据库服务器进程和对话汇合起来(这样的组合称之为池服务器)，通过从单主机或不同主机发出的多个应用软件进程的连接进行共享。由一个连接代理(Connection Broker)进程控制着数据库后台进程中的池服务器。连接代理会持续的连接客户并对客户进行验证。当需要进行某种数据库活动时，客户将请求连接代理提供池服务器，使用完毕后再将它们释放以供其他客户重新使用。当池服务器处在使用当中时，相当于一台专用服务器。对于来自常驻通道中的客户端连接请求，连接代理会为其选择一个合适的池服务器，并把客户端请求交给该池服务器处理，不再干涉。此后客户通过和该池服务器的直接对话来完成所有的数据库活动。当客户完成请求任务释放池服务器后，连接代理将重新接管该池服务器。

二.什么时间使用DRCP
1 使用较小内存的、大量的客户端连接
2 客户端应用是相似的，可以共享或重用会话
3 客户端占用数据库连接的周期相当短
4 会话不需要跨客户请求
5 客户端有众多的主机与进程

三.Dedicated Servers,Shared Servers与DRCP的内存需求和区别对比
一般情况下，由于每个会话需要消耗400k的内存，每个进程需要消耗4m的内存，现在我们以DRCP的pool size是100，shared server的shared server进程是100为例，假如有5000个客户端连接到这些环境，则这些主机的内存分配如下：

A Dedicated Server
Memory used = 5000 X (400 KB + 4 MB) = 22 GB
B Shared Server
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA
.
C Database Resident Connection Pooling
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

四.使用DRCP时，当实例有活动pooled server，有以下限制：
1 不能shutdown database;
2 不能停掉DRCP;
3 不能用database link连接到不同实例的DRCP;
4 不能使用Advanced Security Option (ASO)，比如encryption等

五.客户端如何连接到DRCP
如果是专用服务器连接，则SERVER=DEDICATED，如果是DRCP连接，则SEVER=POOLED。如果要指定客户端请求到DRCP，则客户端的tnsnames.ora中的连接字符串必须指定连接类型是POOLED，配置方式如下所示：

ORA11G_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVER=POOLED)  --注意
      (SERVICE_NAME = ora11g)
    )
  )

说明：
1)如果在tnsnames.ora中指定了SERVER=POOLED，但并没有在实例中启动DRCP，则当客户请求连接时，DB会报ORA-12520错误。
2)11g的客户端才可以使用DRCP，如果10g的客户端在tnsnames.ora中指定了SERVER=POOLED，则连接时报ORA-56606。

六.DRCP配置/查询
1.DRCP启动/关闭

--查看当前DRCP状态
SQL> select CONNECTION_POOL,status from dba_cpool_info;
CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE
--启动DRCP
SQL> execute dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.
SQL> select CONNECTION_POOL,status from dba_cpool_info;
CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE
--关闭DRCP
SQL> exec dbms_connection_pool.stop_pool
PL/SQL procedure successfully completed.
SQL> select CONNECTION_POOL,status from dba_cpool_info;
CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

2.修改DRCP参数

--dbms_connection_pool.configure_pool
exec dbms_connection_pool.configure_pool(
	POOL_NAME=>'SYS_DEFAULT_CONNECTION_POOL',
	minsize=>10,
	maxsize=>100,
	INCRSIZE=>10,
	SESSION_CACHED_CURSORS=>50,
	inactivity_timeout=>3000,
	max_think_time=>100,
	MAX_USE_SESSION=>10000,
	MAX_LIFETIME_SESSION=>36000
   );
--dbms_connection_pool.alter_param
exec dbms_connection_pool.alter_param(
	POOL_NAME=>'SYS_DEFAULT_CONNECTION_POOL',
	PARAM_NAME=>'MINSIZE',
	PARAM_VALUE=>'2');

3.DRCP视图

DBA_CPOOL_INFO
displays configuration information about all Database Resident Connection Pools in the database.
V$CPOOL_STATS
displays information about the Database Resident Connection Pool statistics for an instance
V$CPOOL_CC_STATS
displays information about the connection class level statistics for
the Database Resident Connection Pool per instance.
V$CPOOL_CONN_INFO
displays connection information about each connection to the connection broker.
V$CPOOL_CC_INFO
displays information about the pool-to-connection class mapping for
the Database Resident Connection Pool per instance.

七.DRCP相关进程

oracle   11715     1  0 21:38 ?        00:00:00 ora_n000_ora11g
oracle   11719     1  0 21:38 ?        00:00:00 ora_l000_ora11g
oracle   11723     1  0 21:38 ?        00:00:00 ora_l001_ora11g
oracle   11727     1  0 21:38 ?        00:00:00 ora_l002_ora11g
oracle   11731     1  0 21:38 ?        00:00:02 ora_l003_ora11g
oracle   12490     1  0 21:57 ?        00:00:00 ora_l004_ora11g
oracle   12494     1  0 21:57 ?        00:00:00 ora_l005_ora11g
oracle   12498     1  0 21:57 ?        00:00:00 ora_l006_ora11g
oracle   12502     1  0 21:57 ?        00:00:00 ora_l007_ora11g
oracle   12506     1  0 21:57 ?        00:00:00 ora_l008_ora11g
oracle   12510     1  0 21:57 ?        00:00:00 ora_l009_ora11g
oracle   12514     1  0 21:57 ?        00:00:01 ora_l010_ora11g
oracle   12518     1  0 21:57 ?        00:00:00 ora_l011_ora11g
oracle   12522     1  0 21:57 ?        00:00:00 ora_l012_ora11g
oracle   12526     1  0 21:57 ?        00:00:00 ora_l013_ora11g
oracle   12530     1  0 21:57 ?        00:00:00 ora_l014_ora11g
oracle   12534     1  0 21:57 ?        00:00:00 ora_l015_ora11g
oracle   12538     1  0 21:57 ?        00:00:00 ora_l016_ora11g
oracle   12542     1  0 21:57 ?        00:00:00 ora_l017_ora11g
oracle   12546     1  0 21:57 ?        00:00:00 ora_l018_ora11g
oracle   12550     1  0 21:57 ?        00:00:00 ora_l019_ora11g
ora_n000_ora11g
Connection Broker Process
ora_l000_ora11g
Pooled Server Process(Handles client requests in Database Resident Connection Pooling)

数据库版本

--目标端
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--源端
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

目标端创建dblink

SQL> create database link dblink_xff connect to test identified by
  2  test using 'ip/mcrm';
数据库链接已创建。

dblink查询操作测试

--目标端
SQL> select count(*) from t_xifenfei@dblink_xff;
  COUNT(*)
----------
     50645
--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';
SQL_TEXT
-------------------------------------------------------------------
SELECT COUNT(*) FROM "T_XIFENFEI" "A1"
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建空表测试

--目标端
SQL> create table  chf.t_xifenfei as select * from t_xifenfei@dblink_xff where 1=0;
表已创建。
--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';
SQL_TEXT
----------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建表插入数据

--目标端
SQL> create table  chf.t_xifenfei_new as select * from t_xifenfei@dblink_xff;
表已创建。
--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",
"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

dblink insert select插入数据测试

--目标端
SQL> insert into chf.t_xifenfei
  2  select * from t_xifenfei@dblink_xff;
已创建 50645 行。
--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%'
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID
","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS",
"TEMPORARY","GENERATED","SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

除掉OPAQUE_TRANSFORM 提示

--目标端
SQL> alter session set events '22825 trace name context forever, level 1' ;
会话已更改。
SQL> insert into chf.t_xifenfei
  2  select * from t_xifenfei@dblink_xff;
已创建 50645 行。
--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' and sq
l_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",
"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

通过dblink的相关实验可以得出,在 insert-as-remote-select的时候,源端库上会出现/*+ OPAQUE_TRANSFORM */的hint提示.该hint的作用是:给出源端目标端要求的数据类型的明确信息(The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database).屏蔽盖hint的方法是设置event:22825 trace name context forever, level 1(官方文档还提供了另外两种hint的方式屏蔽这个,但是我测试均未成功)

在9i中因为某个执行计划因为Oracle Peeking绑定变量的控制导致现有的执行计划不正确,需要清除掉这条sql语句的执行计划.在10g中提供了dbms_shared_pool.purge(见:清除掉shared pool中某条sql语句方法),但是在9i中未提供好的方法,一般来说可以通过对相关表的DDL操作,收集统计信息,授权操作可以实现清除对于表执行计划.注:这些操作不会只清空特定SQL执行计划,而是会清除该表相关的所有执行计划,所以操作需要慎重(影响肯定比flush shared_pool小)
模拟测试数据

SQL> create table t_xifenfei (id number,name varchar2(100));
Table created.
SQL> insert into t_xifenfei values(1,'www.xifenfei.com');
1 row created.
SQL> commit;

清除执行计划1:修改表结构

SQL>  alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> alter table t_xifenfei  add fei varchar2(10);
Table altered.
SQL> alter table t_xifenfei drop COLUMN fei;
Table altered.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL>  select count(*) from v$sql_plan where hash_value=1067507827;
  COUNT(*)
----------
         0

清除执行计划2:重新收集统计信息

--DBMS_STATS收集统计信息
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected
--analyze收集统计信息(不推荐)
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> analyze table  t_xifenfei compute statistics;
Table analyzed.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

清除执行计划3:创建INDEX

SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL>  select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> create index i_txifenfei on t_xifenfei(id) online;
Index created.
SQL> drop index i_txifenfei ;
Index dropped.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

清除执行计划3:GRANT/REVOKE操作

SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------
         1 www.xifenfei.com
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> GRANT SELECT ON T_XIFENFEI TO SYSTEM;
Grant succeeded.
SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei';
SQL_TEXT                                           HASH_VALUE
-------------------------------------------------- ----------
select * from t_xifenfei                           1067507827
SQL> select OPERATION from v$sql_plan where hash_value=1067507827;
no rows selected

近期有朋友对于单个表上的index各种情况比较模糊,这里对于单个表上,单个index出现的大多数情况进行了总结性测试,给出了测试结果,至于为什么出现这样的试验结果未做过多解释,给读者留下思考的空间.本篇文章仅仅是为了测试hint对index的影响,而不是说明走各种index方式的好坏.参考: INDEX FULL SCAN vs INDEX FAST FULL SCAN
创建表模拟测试

SQL> create table t_xifenfei as select object_id,object_name from dba_objects;
Table created.
SQL>  create index i_t_object_id on t_xifenfei(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade=>true);
PL/SQL procedure successfully completed.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

TABLE ACCESS FULL

SQL> SET AUTOT TRACE EXP STAT
SQL> SELECT OBJECT_ID FROM T_XIFENFEI;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3544  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT /*+ INDEX(T i_t_object_id) */ OBJECT_ID FROM T_XIFENFEI;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3544  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

从上面的执行计划中可知,此时走了全表扫描. 由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢？ 这是因为NULL值与索引的特性所决定的.即null值不会被存储到B树索引.因此应该为表 t_xifenfei 的列 object_id 添加 not null 约束.

INDEX FAST FULL SCAN

SQL> alter table t_xifenfei modify(object_id not null);
Table altered.
SQL> SELECT  object_id from t_xifenfei;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               | 49838 |   243K|    27   (4)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3432  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

INDEX FAST FULL SCAN:当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操作.对于索引的分支结构只是简单的获取,然后扫描所有的叶结点.其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序.INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件.

INDEX RANGE SCAN

SQL> select object_id from t_xifenfei where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2197008162
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     2 |    10 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T_OBJECT_ID |     2 |    10 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"<10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> select /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     2 |    10 |    27   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID |     2 |    10 |    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        118  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

这里可以看出index_ffs已经生效,但是对于这样的情况hint index_ffs效率一般来说不会太高.
<br>
<strong>INDEX FULL SCAN</strong>

SQL> SELECT /*+ INDEX(T i_t_object_id) */ object_id  from t_xifenfei t;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3426  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

INDEX FULL SCAN:完全按照索引存储的顺序依次访问整个索引树.当访问到叶结点之后,按照双向链表方式读取相连节点的值.换言之,对于索引上所有的数据是按照有序的方式来读取的.如果索引块没有在高速缓存中被找到时,则需要从数据文件中单块进行读取.对于需要读取大量数据的全索引扫描而言,这将使其变得低效.INDEX FULL SCAN使用single read,故产生db file sequential reads事件.新版的Oracle支持db file parallel reads方式.
HINT INDEX不会使用INDEX FAST FULL SCAN功能.

INDEX列ORDER BY

SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id ;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3426  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id  desc;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2808014233
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3427  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT  /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t order by object_id;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2527678987
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 49838 |   243K|       |   185   (4)| 00:00:03 |
|   1 |  SORT ORDER BY        |               | 49838 |   243K|  1192K|   185   (4)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|       |    27   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      49838  rows processed

对于index 列排序,默认情况下会使用INDEX FULL SCAN/INDEX FULL SCAN DESCENDING而不选择使用INDEX FAST FULL SCAN,因为INDEX FAST FULL SCAN获得数据后,还需要做一次SORT ORDER BY操作

INDEX FAST FULL SCAN+SORT AGGREGATE

SQL> SELECT  count(object_id) FROM T_XIFENFEI;
Execution Plan
----------------------------------------------------------
Plan hash value: 3095383276
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    27   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |    27   (4)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> SELECT  /*+ INDEX(T i_t_object_id) */ count(object_id) FROM T_XIFENFEI t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3079973526
--------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |   113   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE  |               |     1 |            |          |
|   2 |   INDEX FULL SCAN| I_T_OBJECT_ID | 49838 |   113   (2)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        111  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sort aggregate通常发生在使用一些聚合函数的时候,sum(),avg(),min(),max(),count()等等,实际上sort aggregate不做真正的sort,并不会用到排序空间,而是通过一个全局变量＋全表或全索引扫描来实现.这样的操作在默认情况下使用INDEX FAST FULL SCAN

INDEX FULL SCAN (MIN/MAX)

SQL> SELECT  max(object_id) FROM T_XIFENFEI;
Execution Plan
----------------------------------------------------------
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 |   243K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>  SELECT /*+ index_ffs(t i_t_object_id) */ max(object_id) FROM T_XIFENFEI t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |     5 |    27   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

对于这样的查询INDEX FULL SCAN (MIN/MAX)明显是最优,但是此处奇怪的是使用了index_ffs提示无效,如果有知道的朋友,麻烦告知原因.

Data Guard主库出现如下错误
导致归档日志不同通过LOG_ARCHIVE_DEST_2传输到备库

Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:00:26 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 3113 when pinging standby standby.
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:33:27 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
[oracle@localhost ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup.
//          Also, look in the alert.log file for any errors. Finally, test to
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
提示连接错误

orcl_ora_6756.trc文件内容
这里没有得任何重要的有效信息

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      fcdb1
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6756, image: oracle@fcdb1 (TNS V1-V3)
*** 2012-04-19 19:51:32.033
*** ACTION NAME:(0000045 STARTED16) 2012-04-19 19:51:32.026
*** MODULE NAME:(backup incr datafile) 2012-04-19 19:51:32.026
*** SERVICE NAME:(SYS$USERS) 2012-04-19 19:51:32.026
*** SESSION ID:(1518.294) 2012-04-19 19:51:32.026
*** ACTION NAME:(0000062 STARTED68) 2012-04-19 19:58:40.083
*** MODULE NAME:(backup full datafile) 2012-04-19 19:58:40.083
*** 2012-04-19 19:58:40.083
*** ACTION NAME:(0000068 STARTED16) 2012-04-19 19:58:40.156
*** 2012-04-19 20:18:18.436
*** ACTION NAME:(0000118 STARTED16) 2012-04-19 20:18:18.436
*** MODULE NAME:(backup incr datafile) 2012-04-19 20:18:18.436

查看相关参数

SQL> show parameter archive;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary,standby)
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES) DB_UNIQUE_NAME=primary
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      ENABLE

测试TNS

[oracle@fcdb1 bdump]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-APR-2012 20:47:51
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@fcdb1 bdump]$ sqlplus sys/oracle@standby as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 20:49:05 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

问题原因分析
从log_archive_dest_2 参数上可以看出默认是配置lgwr进程传输日志，但是因为备库没有配置standby redo log，所以使得启动arch传输日志，然后出现该问题，因为在传输过程中出现异常，导致arch不能继续和备库建立连接。

解决方法
查看了很多资料，给出的解决方法都是重启主库或者备库解决，我想既然是ARCH建立连接的失败，那么重启log_archive_dest_state_2参数，让arch进程重启。

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
System altered.
SQL> alter system switch logfile;
System altered.
--alert日志
Thu Apr 19 20:51:12 2012
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Thu Apr 19 20:51:32 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
LNS1 started with pid=35, OS id=7012
Thu Apr 19 20:51:47 2012
Thread 1 advanced to log sequence 2025
  Current log# 2 seq# 2025 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thu Apr 19 20:51:48 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Apr 19 20:52:02 2012
Thread 1 advanced to log sequence 2026
  Current log# 3 seq# 2026 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 2027

这个时候，查看备库日志也已经传输过去，通过修改log_archive_dest_state_2解决

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

怎么找出通过dblink访问的用户信息?这个问题困惑了很久,今天在朋友的帮助下,终于通过基表实现了这个功能,记录下来

SELECT /*+ ORDERED */
 S.KSUSEMNM "O_HOSTNAME",S.KSUSEPID "O_SPID",--操作dblink用户信息
 G.K2GTITID_ORA "O_TXID",
 S.INDX "S_SID",S.KSUSESER "S_SERIAL#",--dblink session信息
 DECODE(BITAND(KSUSEIDL, 11),
               1,
               'ACTIVE',
               0,
               DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
               2,
               'SNIPED',
               3,
               'SNIPED',
               'KILLED') "S_STATUS",
                S.KSUUDNAM "DBLINK_USER"
  FROM SYS.X$K2GTE G, SYS.X$KTCXB T, SYS.X$KSUSE S
 WHERE G.K2GTDXCB = T.KTCXBXBA
   AND G.K2GTDSES = T.KTCXBSES
   AND S.ADDR = G.K2GTDSES;

查询结果如下

O_HOSTNAME             O_SPID       O_TXID                      S_SID  S_SERIAL#  S_STATUS   DBLINK_USER
----------------------- ---------- -----------------------  ---------- ---------- --------  ------------
localhost.localdomain  2571         MCRM.757120d4.8.31.21425     5462         20  INACTIVE   TEST1
localhost.localdomain  1021         MCRM.757120d4.6.17.21298     5467        664  INACTIVE   TEST
localhost.localdomain  1385         MCRM.757120d4.10.2.16138     5473        155  INACTIVE   TEST

如果需要获取更加信息的信息,可以通过结合两端的v$session和v$process视图获得

ROWID知识补充
Rowid改为：OOOOOOFFFBBBBBBRRR，占用10个字节（32bit data_object_id +10bit rfile# +22bit block +16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID。

Rowid对应值对应10十进制值

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

模拟环境

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t_xff (id number,name varchar2(100));
Table created.
SQL> insert into t_xff values(1,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL>  alter table t_xff move;
Table altered.
SQL> select rowid,a.* from t_xff a;
ROWID                      ID NAME
------------------ ---------- ------------------------------
AADye6AAEAAAtCcAAA          1 www.xifenfei.com

相关值计算

Data Object number = AADye6
File               = AAE
Block              = AAAtCc
ROW                = AAA
DATA_OBJECT_ID
AADye6
58*64^0+30*64^1+50*64^2+3*64^3+0*64^4+0*64^5
58+1920+204800+786432+0+0=993210
RFILE#
AAE
4*64^0+0*64^1+0*64^2
4+0+0=4
BLOCK
AAAtCc
28*64^0+2*64^1+45*64^2+0*64^3+0*64^4+0*64^5
28+128+184320+0+0+0=184476

验证结果

SQL> select object_id,data_object_id from dba_objects where object_name='T_XFF';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    993209         993210
SQL> select dbms_rowid.rowid_object('AADye6AAEAAAtCcAAA') data_object_id#,
  2         dbms_rowid.rowid_relative_fno('AADye6AAEAAAtCcAAA') rfile#,
  3         dbms_rowid.rowid_block_number('AADye6AAEAAAtCcAAA') block#,
  4     dbms_rowid.rowid_row_number('AADye6AAEAAAtCcAAA') row# from dual;
DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
         993210          4     184476          0

dump方式分析

SQL> select rowid,dump(rowid) from t_xff;
ROWID              DUMP(ROWID)
------------------ ------------------------------------------
AADye6AAEAAAtCcAAA Typ=69 Len=10: 0,15,39,186,1,2,208,156,0,0
DATA_OBJECT_ID
0,15,39,186
15*256*256+39*256+186=993210
RFILE#
1,2(取前10位)
000000001 00 =4
BLOCK
2,208,156(取后22位)
000010 11010000 10011100
2^17+208*256+156=184476

1.发现多个ora_j0**进程
可以发现进程重启非常频繁,大概1分钟重启一次，启动ora_j0**的个数为20个

[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 213120 1 0 11:01:55 - 0:00 ora_j019_prmdb2
oracle 282808 1 0 11:01:55 - 0:00 ora_j012_prmdb2
oracle 299178 1 0 11:01:54 - 0:00 ora_j004_prmdb2
oracle 434212 1 0 11:01:55 - 0:00 ora_j014_prmdb2
oracle 475238 1 0 11:01:54 - 0:00 ora_j011_prmdb2
oracle 487562 1 0 11:01:54 - 0:00 ora_j008_prmdb2
oracle 614612 1 0 11:01:55 - 0:00 ora_j017_prmdb2
oracle 717008 1 0 11:01:54 - 0:00 ora_j009_prmdb2
oracle 721012 1 0 11:01:54 - 0:00 ora_j006_prmdb2
oracle 749618 1 0 11:01:54 - 0:00 ora_j007_prmdb2
oracle 770268 1 0 11:01:54 - 0:00 ora_j005_prmdb2
oracle 811114 1 0 11:01:55 - 0:00 ora_j015_prmdb2
oracle 831550 1 0 11:01:55 - 0:00 ora_j016_prmdb2
oracle 847940 1 0 11:01:55 - 0:00 ora_j013_prmdb2
oracle 938076 1 0 11:01:54 - 0:00 ora_j010_prmdb2
oracle 942224 1 0 11:01:54 - 0:00 ora_j002_prmdb2
oracle 974968 1 0 11:01:55 - 0:00 ora_j018_prmdb2
oracle 434602 1 0 11:01:54 - 0:00 ora_j000_prmdb2
oracle 668104 1 0 11:01:54 - 0:00 ora_j003_prmdb2
oracle 983424 1 0 11:01:54 - 0:00 ora_j001_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 454676 1 1 11:02:56 - 0:00 ora_j012_prmdb2
oracle 696366 1 1 11:02:56 - 0:00 ora_j011_prmdb2
oracle 749628 1 1 11:02:56 - 0:00 ora_j019_prmdb2
oracle 770276 1 1 11:02:56 - 0:00 ora_j017_prmdb2
oracle 794824 1 1 11:02:56 - 0:00 ora_j010_prmdb2
oracle 827464 1 1 11:02:55 - 0:00 ora_j009_prmdb2
oracle 831560 1 1 11:02:56 - 0:00 ora_j016_prmdb2
oracle 847946 1 1 11:02:56 - 0:00 ora_j014_prmdb2
oracle 888972 1 1 11:02:55 - 0:00 ora_j007_prmdb2
oracle 934064 1 1 11:02:55 - 0:00 ora_j008_prmdb2
oracle 938080 1 1 11:02:56 - 0:00 ora_j013_prmdb2
oracle 942232 1 0 11:02:55 - 0:00 ora_j001_prmdb2
oracle 962760 1 1 11:02:55 - 0:00 ora_j006_prmdb2
oracle 434608 1 1 11:02:55 - 0:00 ora_j004_prmdb2
oracle 528810 1 2 11:02:56 - 0:00 ora_j015_prmdb2
oracle 549228 1 1 11:02:55 - 0:00 ora_j005_prmdb2
oracle 668112 1 1 11:02:55 - 0:00 ora_j003_prmdb2
oracle 709090 1 1 11:02:55 - 0:00 ora_j002_prmdb2
oracle 905720 1 2 11:02:56 - 0:00 ora_j018_prmdb2
oracle 971040 1 1 11:02:55 - 0:00 ora_j000_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep |wc -l
      20

2.其他参数

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> show parameter job;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     20
SQL> select count(*) from DBA_JOBS_RUNNING;
  COUNT(*)
----------
         0
SQL> SELECT count(*) FROM v$session_wait where event='jobq slave wait';
  COUNT(*)
----------
        20
SQL> select count(*) from dba_jobs;
  COUNT(*)
----------
         2
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _JOB_QUEUE_INTERVAL
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_JOB_QUEUE_INTERVAL%')
NAME                             VALUE
-------------------------------- ------------------------
DESCRIPTION
----------------------------------------------------------------------
_job_queue_interval              5
Wakeup interval in seconds for job queue co-ordinator

3.对cjq进程做10046

[prmsvr2:/oracle]ps -ef|grep cjq
  oracle  327870       1   0   Feb 10      - 770:39 ora_cjq0_prmdb2
  oracle  929872  794774   0 13:24:59  pts/2  0:00 grep cjq
SQL> oradebug setospid 327870
Oracle pid: 19, Unix process pid: 327870, image: oracle@prmsvr2 (CJQ0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL>  oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/admin/prmdb/bdump/prmdb2_cjq0_327870.trc

4.查看cjq的10046文件
发现大量的process startup等待，而且两次批量运行之间的时间间隔在1分钟左右。

WAIT #0: nam='process startup' ela= 59247 p1=74 p2=0 p3=0
WAIT #0: nam='process startup' ela= 51486 p1=74 p2=1 p3=0
WAIT #0: nam='process startup' ela= 51629 p1=74 p2=2 p3=0
WAIT #0: nam='process startup' ela= 48205 p1=74 p2=3 p3=0
WAIT #0: nam='process startup' ela= 47625 p1=74 p2=4 p3=0
WAIT #0: nam='process startup' ela= 55945 p1=74 p2=5 p3=0
WAIT #0: nam='process startup' ela= 47633 p1=74 p2=6 p3=0
WAIT #0: nam='process startup' ela= 51809 p1=74 p2=7 p3=0
WAIT #0: nam='process startup' ela= 57371 p1=74 p2=8 p3=0
WAIT #0: nam='process startup' ela= 50249 p1=74 p2=9 p3=0
WAIT #0: nam='process startup' ela= 51683 p1=74 p2=10 p3=0
WAIT #0: nam='process startup' ela= 47933 p1=74 p2=11 p3=0
WAIT #0: nam='process startup' ela= 47699 p1=74 p2=12 p3=0
WAIT #0: nam='process startup' ela= 47800 p1=74 p2=13 p3=0
WAIT #0: nam='process startup' ela= 47947 p1=74 p2=14 p3=0
WAIT #0: nam='process startup' ela= 52071 p1=74 p2=15 p3=0
WAIT #0: nam='process startup' ela= 47776 p1=74 p2=16 p3=0
WAIT #0: nam='process startup' ela= 47764 p1=74 p2=17 p3=0
WAIT #0: nam='process startup' ela= 47684 p1=74 p2=18 p3=0
WAIT #0: nam='process startup' ela= 47790 p1=74 p2=19 p3=0

通过O记的大力帮助,终于找出了该问题的原因:Bug 4339922: CJQ PROCESS WAKE UP JOB QUEUE PROCESSES EVERY 1 MINUTES.(THERE IS NO JOBS).因为9i的版本oracle不再提供新补丁支持,ora_j0**相关进程不停重启不太占用系统和数据库资源,在不能升级数据库的情况下,可以考虑设置job_queue_processes到一个合适值,然后忽略该问题。

模拟环境
创建一个表，含有位图index和b-tree index

SQL> create table t_xifenfei as
  2  select object_id,object_name from dba_objects;
Table created.
SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);
Index created.
SQL> CREATE BITMAP INDEX  bitmap_t_xifenfei on t_xifenfei(object_name);
Index created.
SQL> BEGIN
  2  dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => true);
  3  END;
  4  /
PL/SQL procedure successfully completed.

无index hint

SQL> SET AUTOT TRACE EXPL STAT
SQL> SELECT OBJECT_ID FROM t_xifenfei;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   841K|  4109K|   886   (3)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI |   841K|  4109K|   886   (3)| 00:00:11 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      60525  consistent gets
          0  physical reads
          0  redo size
   15543305  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，预料之中事件

index hint b_tree_t_xifenfei

SQL> SET  LINESIZE 150
SQL> SELECT /*+ INDEX(T b_tree_t_xifenfei) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，这里的疑惑是：
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index，因为使用这个的cost会大于全表扫描

index hint 一个无效index

SQL> SELECT /*+ INDEX(T abc) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里使用了一个无效的index，也使用了BITMAP_T_XIFENFEI，让人更加的感觉奇怪

原因分析
If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index，优化器会扫描表中所有可以使用的index，然后选择cost最小的index或者index组合，而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候，因为object_id可能有值为空(列没定义为not null)，所以不能使用该index，从而也就是相当于一个无效的index，从而扫描该表的其他可以使用的index，导致使用了位图索引(该类型index不排除null)，而不是全表扫描.
温馨提示:使用hint指定index的时候需要慎重，如果不合适或者无效，可能导致程序效率更低