分类目录归档：数据库

crs 10.2.0.1 bug（公网ip为私有ip时）

发表于 2011 年 7 月 28 日 由 惜分飞

当crs10.2.0.1公用网卡IP段使用10、172、192段（非路由网段）的时候
1、会出现以下两种情况：
1）最后检查时出现如下错误
Checking existence of VIP node application (required)
Check failed.
Check failed on nodes:
rac1,rac2

2）在rac2上执行root.sh脚本最后
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
rac2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
……
Timed out waiting for the CRS stack to start.

2、解决方法：
在root.sh在第二个节点执行完之后，以 root 用户身份在第二个节点上手动调用 VIPCA
# /u01/app/oracle/product/10.2.0/crs_1/bin/vipca
配置虚拟ip

发表在 Oracle RAC, Oracle安装升级 | 评论关闭

Oracle 10g RAC之配置时间同步（NTP）

发表于 2011 年 7 月 27 日 由 惜分飞

所有节点的时间必须同步，这通常是通过配置NTP服务器实现的。如果用户的网络中已经有一台时间服务器，那么可以所有节点都指向它，否则可以从集群中挑选一个节点作为时间服务器，让其他节点与它同步。下面分别演示这两种方法：

(1)如果公司网络中已经有一台时间服务器
如：192.168.11.10是一台ntp时间服务器
此时NTP服务器的配置文件是/etc/ntp.conf，在每个节点编辑这个文件。
[root@cc-svr-a ~]# vi /etc/ntp.conf
编辑后的内容如下，首选公司的时间服务器。
server 192.168.11.10 prefer
driftfile /var/lib/ntp/drift
broadcastdelay 0.008

(2)如果没有外部时间服务器
这时公司选择集群中某个节点作为时间服务器，NTP服务只需要很少的系统资源。假设选择主节点作为时间服务器，从节点向它同步，其配置方法如下。
编辑主节点的/etc/ntp.conf文件，编辑后的内容如下：

server 127.127.1.0
fudge 127.127.1.0 stratum 11
driftfile /var/lib/ntp/drift
broadcastdelay 0.008

编辑从节点的/etc/ntp.conf文件，编辑后的内容如下：

server 192.168.11.12 prefer #注意192.168.11.12为主节点的IP地址
driftfile /var/lib/ntp/drift
broadcastdelay 0.008

配置完成后，启动NTP服务。
[root@cc-svr-a ~]# /etc/init.d/ntpd start
[root@cc-svr-a ~]# chkconfig –level 345 ntpd on

(3)查看执行情况（主从）
[root@cc-svr-a ~]# ntpq -p
remote refid st t when poll reach delay offset jitter
==============================================================================
*CC-Node-02 LOCAL(0) 12 u 58 64 17 0.226 -22.698 29.261

发表在 Linux, Oracle RAC | 一条评论

select max(id),min(id) from table优化

发表于 2011 年 7 月 22 日 由 惜分飞

1、查看数据库版本
SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 2、创建表和索引
create table t_a as select * from dba_objects;
create index t_a_ind on t_a(object_id);

3、查询最大值
SQL> select max(object_id) from t_a;
执行计划
———————————————————-
Plan hash value: 3226265922

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

4、查询最小值
SQL> select min(object_id) from t_a;
执行计划
———————————————————-
Plan hash value: 3226265922

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |
|

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
429 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

5、查询最大值和最小值
SQL> select max(object_id),min(object_id) from t_a;
执行计划
———————————————————-
Plan hash value: 2127980459

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T_A | 78093 | 991K| 293 (1)| 00:00:04 |
—————————————————————————

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
4 recursive calls
0 db block gets
1119 consistent gets
1044 physical reads
0 redo size
502 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

6、查询最大值和最小值（排除null）
SQL> select max(object_id),min(object_id) from t_a where object_id is not null;
执行计划
———————————————————-
Plan hash value: 1214261695

———————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

———————————————————————————

| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01
|

| 1 | SORT AGGREGATE | | 1 | 13 | |
|

|* 2 | INDEX FAST FULL SCAN| T_A_IND | 78093 | 991K| 50 (2)| 00:00:01
|

———————————————————————————

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

2 – filter(“OBJECT_ID” IS NOT NULL)

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
4 recursive calls
0 db block gets
242 consistent gets
0 physical reads
0 redo size
502 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

7、分别查询最大值和最小值
SQL> select (select max(object_id) from t_a) max,(select min(object_id) from t_a) min from dual;
执行计划
———————————————————-
Plan hash value: 312201770

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 13 | |

| 4 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
480 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

说明：
1、单查询最大值或者最小值，sql会自动走index
2、如果同时查询最大值和最小值，sql会使用全表扫描，而不是我们想象的索引快速扫描
3、加上where 排除掉null的情况，sql使用索引快速扫描，原因是：在不能确定索引列不为null（或者没有排除掉null）的情况下，不会使用索引快速扫描，而sql为了保证正确而采用了全表扫描
4、INDEX FULL SCAN (MIN/MAX)扫描效率很搞，所以把最大值，最小值分开查询，提高执行效率
5、其他写法
SQL> select (select /*+ index_asc(t_a t_a_ind) */ object_id from t_a where rownu
m=1) min ,(select /*+ index_desc(t_a t_a_ind) */ object_id from t_a where rownum=1)
max from dual;

执行计划
———————————————————-
Plan hash value: 674626822
—————————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————————

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:
00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | INDEX FULL SCAN | T_A_IND | 78093 | 991K| 2 (0)| 00:

00:01 |

|* 3 | COUNT STOPKEY | | | | |

| 4 | INDEX FULL SCAN DESCENDING| T_A_IND | 78093 | 991K| 2 (0)| 00:

00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

—————————————————————————————

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

1 – filter(ROWNUM=1)
3 – filter(ROWNUM=1)

Note—— dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
480 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意：
1）这个要正确执行，需要一个前提条件object_id这列要为not null限制条件，不然会hint提示无效
2）从执行计划的统计信息上看，这个和INDEX FULL SCAN (MIN/MAX)方式的执行效率一样

发表在 Oracle性能优化 | 一条评论