maintain

分区表中的index 失效及处理

January 4, 2012 maintain, oracle No comments

分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行,需要注意的是global 索引,当global索引所在表执行alter table 涉及下列操作时,会导至该索引失效,需要重新建立:

———————————————————————-

一、测试环境

[oracle@testdb ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 4 14:52:40 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Dayangase 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

二、测试步骤
1.全局索引测试步骤


create table yang (id number,name char(100))
partition by range(id)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));

insert into yang values(1,’a’);
insert into yang values(11,’a’);
insert into yang values(21,’a’);
commit;

create index idx_yang_id on yang (id);

———————– add测试—————————-

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

———————– drop测试—————————

insert into yang values(31,’a’);
commit;

alter table yang drop partition e;
alter table yang drop partition d;
alter index idx_yang_id rebuild;

———————– truncate测试—————————-

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

insert into yang values(31,’a’);
commit;

alter table yang truncate partition e;
alter table yang truncate partition d;
alter index idx_yang_id rebuild;

———————– split测试—————————
alter table yang add partition d values less than (maxvalue);
alter table yang split partition d at (40) into (partition d,partition e);

insert into yang values(45,’a’);
commit;
alter table yang split partition e at (50) into (partition e,partition f);

insert into yang values(65,’a’);
commit;
alter table yang split partition f at (60) into (partition f,partition g);

insert into yang values(75,’a’);
commit;
alter table yang split partition g at (70) into (partition g,partition h);
alter index idx_yang_id rebuild;

2.本地索引测试步骤


create table yang (id number,name char(100))
partition by range(id)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));

insert into yang values(1,’a’);
insert into yang values(11,’a’);
insert into yang values(21,’a’);
commit;

create index l_idx on yang (id) local;

———————– add测试—————————-

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

———————– drop测试—————————

insert into yang values(31,’a’);
commit;

alter table yang drop partition e;
alter table yang drop partition d;

———————– truncate测试—————————-

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

insert into yang values(31,’a’);
commit;

alter table yang truncate partition e;
alter table yang truncate partition d;

———————– split测试—————————
alter table yang add partition d values less than (maxvalue);
alter table yang split partition d at (40) into (partition d,partition e);

insert into yang values(45,’a’);
commit;
alter table yang split partition e at (50) into (partition e,partition f);

insert into yang values(65,’a’);
commit;
alter table yang split partition f at (60) into (partition f,partition g);

insert into yang values(75,’a’);
commit;
alter table yang split partition g at (70) into (partition g,partition h);

alter index l_idx rebuild partition h;
alter index l_idx rebuild partition g;

三、测试结论 (range, list)


global index:

add partition valid
drop partition 分区中无数据:valid 有数据:unusable
truncate partition 分区中无数据:valid 有数据:unusable

local index:
add partition,drop partition,truncate partition 都不会导致失效

—————————————————-

split partition a->a,b

global index :
a,b无数据 valid
a有数据 b无数据 valid
a无数据 b有数据 valid
a,b都有数据 unusable

local index:
a,b无数据 usable
a有数据 b无数据 usable
a无数据 b有数据 usable
a,b均有数据 unusable

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

exchang partition (range list)

reference:http://www.itpub.net/thread-1323472-1-1.html

我下面就主要对这两种办法讨论update global indexes。

建表SQL(其中,HAOPART2和HAOPART是一样的结构):


create table haopart (
id number not null,
c1 char(100),
c2 char(200),
c3 char(300)
)
PARTITION BY RANGE(id)
(
PARTITION PART01 VALUES LESS THAN (100),
PARTITION PART02 VALUES LESS THAN (200),
PARTITION PART03 VALUES LESS THAN (500),
PARTITION PART04 VALUES LESS THAN (1000),
PARTITION PARTMAX VALUES LESS THAN (MAXVALUE)
)
tablespace USERS
;

create index haolocal_1 on haopart(c1) local tablespace USERS;
create index haolocal_2 on haopart(c2) local tablespace USERS;
create index haolocal_3 on haopart(c3) local tablespace USERS;
create index haoglobal on haopart(id,c1,c2,c3) global tablespace USERS ;

insert into haopart
select rownum,object_name,object_name,object_name
from dba_objects;

这样HAOPART就有3个local indexes和1个global index。

临时表建表SQL(其中,HAOTMP和HAOTMP2是一样的结构):


create table haotmp
(
id number not null,
c1 char(100),
c2 char(200),
c3 char(300)
) tablespace users;

create index tmphao_1 on haotmp(c1) tablespace USERS;
create index tmphao_2 on haotmp(c2) tablespace USERS;
create index tmphao_3 on haotmp(c3) tablespace USERS;

一.以exchange partition为例,不加update global indexes时:
1. 如果partiton里有数据,global index则会失效
SQL> select count(*) from haopart2 partition(part04);
COUNT(*)
———-
500
SQL> select count(*) from haotmp2;
COUNT(*)
———-
0
SQL> alter table haopart2 exchange partition part04 with table haotmp2 including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 UNUSABLE NO

2. 如果partition里没有任何数据,新的临时表有数据,global index也会失效。

SQL> select count(*) from haotmp2;
COUNT(*)
———-
500

SQL> select count(*) from haopart2 partition(part04);
COUNT(*)
———-
0
SQL> alter index haoglobal2 rebuild;
Index altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2 including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
2 table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 UNUSABLE NO

3.即使partition和临时表都没有数据,也会使global index失效。
SQL> alter table haopart2 truncate partition part04;
table truncated.

SQL> truncate table haotmp2;
table truncated.

SQL> alter index haoglobal2 rebuild;
Index altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2 including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 UNUSABLE NO

二.以exchange partition为例,加上update global indexes时:
1. 无论任何时候,global index都不会失效。

SQL> select count(*) from haopart2 partition(part04);
COUNT(*)
———-
500
SQL> select count(*) from haotmp2;
COUNT(*)
———-
56

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2 including indexes without validation update global indexes;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
2 table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME STATUS PAR
—————————— ——– —
HAOGLOBAL2 VALID NO

2. 会对原表加Mode=3 TM lock,会对原表做ddl的partition加Mode=6 TM lock。

select o.OBJECT_ID,o.OBJECT_NAME,o.SUBOBJECT_NAME,o.OBJECT_TYPE,l.LMODE
from dba_objects o,v$lock l
where o.OBJECT_ID=l.ID1
and l.TYPE=’TM’
and l.sid=1094
;
OBJECT_ID OBJECT_NAM SUBOBJECT_ OBJECT_TYPE LMODE
———- ———- ———- ——————- ———-
10597 HAOPART PART04 table PARTITION 6
10593 HAOPART table 3
10604 HAOTMP table 6

3. exchange partition update global indexes不会block使用global index的select语句,但是由于大量的update index操作,所以会使得查询大量走undo,所以查询会变慢。

在如下exchange partition update global indexes命令进行时:

alter table haopart exchange partition part04 with table haotmp
including indexes without validation
update global indexes;

在另一个session执行如下走global index的select:
select count(*) from haopart where id <=1000; ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2902 (1)| 00:00:35 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | INDEX RANGE SCAN| HAOGLOBAL | 31744 | 124K| 2902 (1)| 00:00:35 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<=1000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2914 consistent gets 0 physical reads 0 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4095 consistent gets 0 physical reads 27052 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5130 consistent gets 0 physical reads 49140 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

可见,执行计划是不变的,但是逻辑读不断上升,也产生大量的redo。
明显查询了undo。

4. exchange partition update global index会阻碍该partition上的dml,但不会阻碍其他partition上的dml。

根据第二点,由于这条语句会对该partition加Mode=6 TM lock,所以很显然,该partition是无法做dml的。

我们会看到等待事件:enq: TM – contention:TM-3:2:

—————————–

exchange partition –local index 测试

SQL> create table t_partition (id number,name varchar2(50))
2 partition by range(id)(
partition p1 values less than (10) ,
partition p2 values less than (20) ,
partition p3 values less than (30) ,
partition pmax values less than (maxvalue)
)
; 3 4 5 6 7 8

Table created.

SQL> create index partition_idx_name on t_partition (name) local;

Index created.

SQL> create table t_no_partition (id number,name varchar2(50));

Table created.

SQL> create index idx_name on t_no_partition(name);

Index created.

SQL> insert into t_no_partition values(11,’11’);

1 row created.

SQL> insert into t_no_partition values(12,’ddd’);

1 row created.

SQL> insert into t_no_partition values(13,’13dsa’);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter table t_partition exchange partition p2 with table t_no_partition INCLUDING INDEXES with validation;

Table altered.

SQL> select partition_name, status from user_ind_partitions where index_name=upper(‘partition_idx_name’);

PARTITION_NAME STATUS
—————————— ——–
P1 USABLE
P2 USABLE
P3 USABLE
PMAX USABLE

SQL> alter table t_partition exchange partition p1 with table t_no_partition;

Table altered.

SQL> alter table t_partition exchange partition p3 with table t_no_partition;

Table altered.

SQL> select partition_name, status from user_ind_partitions where index_name=upper(‘partition_idx_name’);

PARTITION_NAME STATUS
—————————— ——–
P1 USABLE
P2 USABLE
P3 UNUSABLE
PMAX USABLE

可以看到不带INCLUDING INDEXES with validation 被exchange的local index partition 会失效,对于存在主键的分区表,可以在主键上以DISABLE VALIDATE方式创建unique constraint约束,以代替全局的主键索引。若交换表上存在主键索引的话,那么建议在交换前暂时将该索引drop掉,待交换完成后再重建。

———————————————
hash partition的测试:

SQL> CREATE table liu(id number,name varchar2(20))
PARTITION BY HASH (id)
PARTITIONS 8
STORE IN (users) 2 3 4
5 ;

table created.

SQL>
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into liu values (i,null);
4 commit;
5 end loop;
6 end;
7 /
alter table liu add constraint pk_liu_id primary key(id);
CREATE INDEX idx_liu_id ON liu (name) LOCAL ;

Partition Name Max Value Length tablespace Compression Rows Blocks Empty Blocks Last Analyzed Avg Space # Subparts

SYS_P153 0 USERS DISABLED 1,188 46 978 2012/1/4 14:25:41 7257 0
SYS_P152 0 USERS DISABLED 1,201 46 978 2012/1/4 14:25:41 7248 0
SYS_P150 0 USERS DISABLED 1,232 46 978 2012/1/4 14:25:41 7226 0
SYS_P155 0 USERS DISABLED 1,259 46 978 2012/1/4 14:25:41 7208 0
SYS_P151 0 USERS DISABLED 1,262 46 978 2012/1/4 14:25:41 7206 0
SYS_P156 0 USERS DISABLED 1,280 46 978 2012/1/4 14:25:41 7193 0
SYS_P149 0 USERS DISABLED 1,283 46 978 2012/1/4 14:25:41 7191 0
SYS_P154 0 USERS DISABLED 1,295 46 978 2012/1/4 14:25:41 7183 0

alter table liu add partition ;

idx_liu_id 分区变为unusable:

Partition Name tablespace Last Analyzed Max Value Length Rows BLevel Distinct Keys # Subparts

UNUSABLE SYS_P165 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P166 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P167 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P168 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P169 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P170 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P171 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P172 USERS 2012/1/4 14:30:15 0 0 0 0 0
UNUSABLE SYS_P173 PURCHASE 0 0

select status from dba_indexes where index_name=’PK_LIU_ID’;

SQL> select status from dba_indexes where index_name=’PK_LIU_ID’;

STATUS
——–
UNUSABLE

SQL>

同样变为了unusable,hash分区在add partition时候 由于数据会重组,所以local,global index 都会变成unusable

在线重定义一例

December 29, 2011 maintain, oracle 1 comment

创建需要redefinition的表,结构同源表


— Create table
create table yhd_so_temp
(id NUMBER(18) not null,
end_user_id NUMBER(18),
order_amount NUMBER(11,2),
order_code CHAR(12),
order_status NUMBER(4),
order_type NUMBER(4),
order_need_cs NUMBER(1),
order_delivery_fee NUMBER(11,2),
order_source NUMBER(4),
back_operator_id NUMBER(18),
order_paid_by_rebate NUMBER(11,2),
order_pay_cfm_bk_op_id NUMBER(18),
order_payment_confirm_date DATE,
order_payment_signal NUMBER(4),
order_payment_method_id NUMBER(18),
order_payment_code VARCHAR2(500),
order_create_time DATE,
order_to_logistics_time DATE,
order_out_of_inventory_status NUMBER(4),
good_receiver_id NUMBER(18),
order_need_invoice NUMBER(1),
parent_so_id NUMBER(18),
delivery_date DATE,
expect_receive_date DATE,
expect_receive_time NUMBER(4),
receive_date DATE,
order_delivery_method_id NUMBER(18),
order_paid_by_account NUMBER(11,2),
order_paid_by_others NUMBER(11,2),
tracker_session_id VARCHAR2(400),
account_payable NUMBER(11,2),
product_amount NUMBER(11,2),
session_id VARCHAR2(400),
data_exchange_flag NUMBER(1),
good_receiver_name VARCHAR2(500),
good_receiver_address VARCHAR2(400),
good_receiver_province VARCHAR2(400),
good_receiver_city VARCHAR2(400),
good_receiver_county VARCHAR2(400),
good_receiver_post_code VARCHAR2(400),
good_receiver_phone VARCHAR2(400),
supplier_process_status NUMBER(4),
po_id NUMBER(18),
order_paid_by_coupon NUMBER(11,2),
cancel_date DATE,
is_leaf NUMBER(1),
order_cs_remark VARCHAR2(2000),
has_imported NUMBER(1),
good_receiver_mobile VARCHAR2(400),
do_cancel_flag NUMBER(10) not null,
so_item_count_change_flag NUMBER(1) not null,
order_paid_by_card NUMBER(11,2) not null,
reference_point NUMBER,
real_point NUMBER,
recompense_points NUMBER not null,
membership_base_point NUMBER not null,
group_ratio NUMBER,
wlt_point NUMBER not null,
track_info VARCHAR2(2000),
is_fragile NUMBER(1),
is_liquid NUMBER(1),
bought_times NUMBER(18),
spec_proc_flag NUMBER(4),
id_card VARCHAR2(400),
order_import_source NUMBER(18),
is_new_good_receiver_hpone NUMBER(1),
partner_amount NUMBER(11,2),
partner_amount_type NUMBER(1),
is_vip NUMBER(1),
good_receiver_city_id NUMBER(18),
good_receiver_country_id NUMBER(18),
good_receiver_county_id NUMBER(18),
good_receiver_province_id NUMBER(18),
allyes_uid VARCHAR2(300),
payment_gateway_id NUMBER(18),
need_allocation NUMBER(1) not null,
warehouse_id NUMBER(18),
delivery_supplierid NUMBER(18),
order_import_do_flag NUMBER(1),
cancel_operator_id NUMBER(18),
mc_site_id NUMBER(8),
order_need_integral NUMBER(9),
virtual_stock_status NUMBER(2),
business_type NUMBER(2),
flag NUMBER(1),
order_finish_time DATE,
update_time DATE,
good_remark VARCHAR2(300),
order_cs_reason VARCHAR2(400),
treatment_time DATE,
treatment_user_id VARCHAR2(400),
is_half_day_delivery NUMBER(1) default 0,
central_version NUMBER(5,2),
order_promotion_discount NUMBER(11,2),
is_need_double_form NUMBER(1) default 0,
creditcard_owner_name VARCHAR2(120),
is_moved_by_hand NUMBER(1) default 0 not null,
order_delivery_person_mobile VARCHAR2(400),
estimate_receive_date DATE,
delivery_remark VARCHAR2(500),
receive_remark VARCHAR2(500),
supplier_delivery_time VARCHAR2(400),
order_delivery_add_fee NUMBER(11,2),
user_ip VARCHAR2(500),
user_guid VARCHAR2(400),
print_num NUMBER(2),
order_create_pay_time DATE,
delivery_method_type NUMBER(8),
delivery_service_type NUMBER(8),
pay_service_type NUMBER(8),
good_receiver_area VARCHAR2(500),
good_receiver_area_id NUMBER(18),
is_new_deliveryorder NUMBER(3),
delivery_level NUMBER(1),
order_weight NUMBER(11,2)
)
partition by range (ORDER_CREATE_TIME)
(
partition P0811 values less than (TO_DATE(‘ 2008-11-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
tablespace data01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition P0812

……….


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session force parallel dml parallel 4;

Session altered.

SQL> alter session force parallel query parallel 4;

Session altered.

SQL> set timing on;

SQL>
SQL>
SQL> begin
2 DBMS_REDEFINITION.START_REDEF_TABLE(uname => ‘EDW1_USER’, orig_table => ‘YHD_SO’,int_table=> ‘YHD_SO_TEMP’,options_flag=>dbms_redefinition.cons_use_rowid);
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:06:00.41

花了6分钟 速度很不错

下面copy index 约束等,对于分区表建议不要copy index,约束,手工建立local index

SQL> select count (*) from yhd_so_temp

COUNT (*)
————
13474251

SQL> declare
2 num_errors PLS_INTEGER;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’,
copy_indexes => 0,
copy_triggers=>true,
copy_constraints=>false,
copy_privileges=>true,
ignore_errors=>false,
num_errors=>num_errors,
copy_statistics=>true);
end;
/ 3 4 5 6 7 8 9 10 11 12 13 14 15 16

PL/SQL procedure successfully completed.

Elapsed: 00:09:52.79

花费了9分钟

创建local index

alter session set workarea_size_policy=manual;

alter session set sort_area_size=1057600;

alter session set db_file_multiblock_read_count=128;

alter table YHD_SO_TEMP
add constraint YHD_PK_SO_N_1 primary key (ID,order_create_time) using index tablespace data01 local;

create index IDX_YHD_SO_PARENT_SO_ID_N_1 on YHD_SO_TEMP (PARENT_SO_ID)
tablespace data01 local parallel 4

create index YHD_IDX_CREATE_TIME_N_1 on YHD_SO_TEMP (TRUNC(ORDER_CREATE_TIME))
tablespace data01 local parallel 4

create index YHD_IDX_ORDER_STATUS_N_1 on YHD_SO_TEMP (ORDER_STATUS)
tablespace data01 local parallel 4

create index YHD_IDX_SO_CREATE_DATE_N_1 on YHD_SO_TEMP (ORDER_CREATE_TIME)
tablespace data01 local parallel 4

create index YHD_IDX_SO_END_USER_ID_N_1 on YHD_SO_TEMP (END_USER_ID)
tablespace data01 local parallel 4

create index YHD_IDX_SO_ORDER_TYPE_N_1 on YHD_SO_TEMP (ORDER_TYPE)
tablespace data01 local parallel 4

create index YHD_IDX_SO_PAYMENT_ID_N_1 on YHD_SO_TEMP (ORDER_PAYMENT_METHOD_ID)
tablespace data01 local parallel 4

create index YHD_UQ_IDX_SO_CODE_N_1 on YHD_SO_TEMP (ORDER_CODE)
tablespace data01 local parallel 4

SYNC同步表,这一步主要是为了减少finish lock table的时间 ,收集新表的statistics,最后不要忘了alter index noparallel

SQL> begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE (
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname =>’edw1_user’,tabname =>’yhd_so’,estimate_percent=> 10,degree =>4, cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter index IDX_YHD_SO_PARENT_SO_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_CREATE_TIME_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_ORDER_STATUS_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_CREATE_DATE_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_END_USER_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_ORDER_TYPE_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_PAYMENT_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_UQ_IDX_SO_CODE_N_1 noparallel;

Index altered.

SQL>

整个过程没有超过1个小时,1300万的数据也不算小 online redefinition 是heap转分区表的一个不错的方法。

How to create very big index

December 29, 2011 11g, maintain, oracle No comments

how to create very big index

我们总会碰到各种case 需要建立索引 有些索引大至1T,这些索引的创建是个非常棘手的问题,oracle 11g 在建立partition index时,给出了不错的解决方案


Let’s now re-create the index as a globally partitioned index, with one partition defined to contain all values of interest and another partition defined to contain the vast number of processed values. Initially, the index is created in an unusable state so no segments and no storage is allocated to any of the partitions

在创建partition index 时候使用unusable 可以不立刻分配storage和segment空间,从而解决了空间分配这个问题:

通过Richard Foote提供的例子,我们可以很好的看出效果:

SQL> create table bowie_stuff (id number, processed varchar2(10));

Table created.

SQL> insert into bowie_stuff select rownum, ‘YES’ from dual connect by level <= 1000000; 1000000 rows created. SQL> commit;

Commit complete.

SQL> update bowie_stuff set processed = ‘NO’ where id in (999990, 999992, 999994, 999996, 999998);

5 rows updated.

SQL> commit;

Commit complete.

SQL> create index bowie_stuff_i on bowie_stuff(processed) pctfree 0;

Index created.

SQL> select index_name, leaf_blocks from dba_indexes where index_name = ‘BOWIE_STUFF_I’;

INDEX_NAME LEAF_BLOCKS

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

BOWIE_STUFF_I 1877

SQL> select segment_name, blocks from dba_segments where segment_name = ‘BOWIE_STUFF_I’;

SEGMENT_NAME BLOCKS

——————– ———-

BOWIE_STUFF_I 1920

建立了一个normal index 分配了1920个块

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


SQL> drop index bowie_stuff_i;

Index dropped.

SQL> create index bowie_stuff_i on bowie_stuff(processed)

2 global partition by range (processed)

3 (partition not_processed_part values less than (‘YES’),

4 partition processed_part values less than (MAXVALUE))

5 unusable;

Index created.

SQL> alter index bowie_stuff_i rebuild partition not_processed_part;

Index altered.

SQL> select index_name, partition_name, leaf_blocks from dba_ind_partitions where index_name = ‘BOWIE_STUFF_I’;

INDEX_NAME PARTITION_NAME LEAF_BLOCKS

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

BOWIE_STUFF_I PROCESSED_PART 0

BOWIE_STUFF_I NOT_PROCESSED_PART 1

SQL> select segment_name, partition_name, blocks from dba_segments where segment_name = ‘BOWIE_STUFF_I’;

SEGMENT_NAME PARTITION_NAME BLOCKS

——————– ——————– ———-

BOWIE_STUFF_I NOT_PROCESSED_PART 8

这里仅仅分配了 8个block

根据Richard Foote 的分析,在index create 完成之后alter index xx unusable 在10g,11g 也是截然不同的:

Starting with a simple Oracle 10g example, we create a table and associated index:

SQL> create table bowie as select rownum id, ‘BOWIE’ name from dual connect by level <= 1000000; Table created. SQL> create index bowie_id_i on bowie(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=> true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);

PL/SQL procedure successfully completed.

If we now make the index unusable:

view sourceprint?

SQL> alter index bowie_id_i unusable;

Index altered.

SQL> select index_name, blevel, leaf_blocks, num_rows, status, dropped from dba_indexes where index_name = ‘BOWIE_ID_I’;

INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DRO

———- ———- ———– ———- ——– —

BOWIE_ID_I 2 2226 1000000 UNUSABLE NO

SQL> select segment_name, bytes, blocks, extents from dba_segments where segment_name = ‘BOWIE_ID_I’;

SEGMENT_NAME BYTES BLOCKS EXTENTS

———— ———- ———- ———-

BOWIE_ID_I 18874368 2304 18

当index 置于unusable时候 blocks 依然存在

Using the same demo as before but running Oracle11g R2:

SQL> create table bowie as select rownum id, ‘BOWIE’ name from dual connect by level <= 1000000; Table created. SQL> create index bowie_id_i on bowie(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=> true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);

PL/SQL procedure successfully completed.

SQL> alter index bowie_id_i unusable;

Index altered.

SQL> select index_name, blevel, leaf_blocks, num_rows, status, dropped from dba_indexes where index_name = ‘BOWIE_ID_I’;

INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DRO

———- ———- ———– ———- ——– —

BOWIE_ID_I 2 2226 1000000 UNUSABLE NO

SQL> select segment_name, bytes, blocks, extents from dba_segments where segment_name = ‘BOWIE_ID_I’;

no rows selected

在11gR2中 alter index unusable 发现index 所占用的blocks 完全消失了

以下是分区表的测试:

SQL> CREATE TABLE big_album_sales(id number, album_id number, country_id number,

release_date date, total_sales number) PARTITION BY RANGE (release_date)

(PARTITION ALBUMS_2007 VALUES LESS THAN (TO_DATE(’01-JAN-2008′, ‘DD-MON-YYYY’)),

PARTITION ALBUMS_2008 VALUES LESS THAN (TO_DATE(’01-JAN-2009′, ‘DD-MON-YYYY’)),

PARTITION ALBUMS_2009 VALUES LESS THAN (TO_DATE(’01-JAN-2010′, ‘DD-MON-YYYY’)),

PARTITION ALBUMS_2010 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_album_sales SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2000), ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 1000000; 1000000 rows created. SQL> commit;

Commit complete.

We first create a Non-Partitioned Index:

SQL> CREATE INDEX big_album_tot_sales_i ON big_album_sales(total_sales);

Index created.

Next a Global Partitioned Index:

SQL> CREATE INDEX big_album_country_id_i ON big_album_sales(country_id)

GLOBAL PARTITION BY RANGE (country_id)

(PARTITION TS1 VALUES LESS THAN (26),

PARTITION TS2 VALUES LESS THAN (51),

PARTITION TS3 VALUES LESS THAN (76),

PARTITION TS4 VALUES LESS THAN (MAXVALUE));

Index created.

Finally, a Local Partitioned index:

SQL> CREATE INDEX big_album_album_id_i ON big_album_sales(album_id) local;

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=> ‘BOWIE’, tabname=> ‘BIG_ALBUM_SALES’, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

If we now split the last table partition, this will effectively make the:

1) Non-Partitioned Unusable
2) All partitions of the Global Partitioned index unusable
3) Just the last 2 partitions of the Local Partitioned Index unusable

SQL> ALTER TABLE big_album_sales SPLIT PARTITION ALBUMS_2010

AT (TO_DATE(’01-JAN-2011’, ‘DD-MON-YYYY’))

INTO (PARTITION ALBUMS_2010, PARTITION ALBUMS_2011);

Table altered.

SQL> select index_name, status from dba_indexes where table_name = ‘BIG_ALBUM_SALES’;

INDEX_NAME STATUS

———————— ——–

BIG_ALBUM_TOT_SALES_I UNUSABLE

BIG_ALBUM_COUNTRY_ID_I N/A

BIG_ALBUM_ALBUM_ID_I N/A

SQL> select index_name, partition_name, status, leaf_blocks from dba_ind_partitions where index_name like ‘BIG_ALBUM_%’;

INDEX_NAME PARTITION_NAME STATUS LEAF_BLOCKS

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

BIG_ALBUM_ALBUM_ID_I ALBUMS_2007 USABLE 807

BIG_ALBUM_ALBUM_ID_I ALBUMS_2008 USABLE 381

BIG_ALBUM_ALBUM_ID_I ALBUMS_2009 USABLE 383

BIG_ALBUM_ALBUM_ID_I ALBUMS_2010 UNUSABLE

BIG_ALBUM_ALBUM_ID_I ALBUMS_2011 UNUSABLE

BIG_ALBUM_COUNTRY_ID_I TS1 UNUSABLE 629

BIG_ALBUM_COUNTRY_ID_I TS2 UNUSABLE 629

BIG_ALBUM_COUNTRY_ID_I TS3 UNUSABLE 629

BIG_ALBUM_COUNTRY_ID_I TS4 UNUSABLE 629

SQL> select segment_name, partition_name, bytes, blocks from dba_segments where segment_name like ‘BIG_ALBUM_%’ and segment_type like ‘INDEX%’;

SEGMENT_NAME PARTITION_NAME BYTES BLOCKS

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

BIG_ALBUM_ALBUM_ID_I ALBUMS_2007 7340032 896

BIG_ALBUM_ALBUM_ID_I ALBUMS_2008 3145728 384

BIG_ALBUM_ALBUM_ID_I ALBUMS_2009 4194304 512

BIG_ALBUM_TOT_SALES_I 23068672 2816

可以发现 BIG_ALBUM_ALBUM_ID_I 状态为UNUSABLE的 blocks已经消失,BIG_ALBUM_COUNTRY_ID_I 由于所有partition都变成了 UNUSABLE 所以整个segment占用的blocks都被取消
BIG_ALBUM_TOT_SALES_I由于是normal index blocks 依然存在,这跟上面的heap表测试出现了矛盾 heap table 在index unusable 时候 blocks 将被drop掉,而在分区表中blocks依然存在!

[转]Local Index Issue With Partitioned PK and Unique Key Constraints

December 27, 2011 maintain, oracle

看到一篇不错的文章 转载下


Nuno Souto (Noons) also asked a really interesting question on my Differences between Unique and Non-Unique Indexes blog entry (comment 4) that I thought it worthy of a separate blog entry to do the answer justice. The question was:

“Isn’t it still the case that unique indexes cannot be locally partitioned unless the partition key is part of the index key? Not sure if 11g removes this. If still so, that would weigh heavily in favour of non-unique indexing for PK on a table potentially requiring local index partitions.”

Simplistically, the answer to the first part is Yes it is still the case, even in 11g and the answer to the second part is No, it wouldn’t weigh heavily in favour of non-unique indexing for PK on a table requiring local index partitions. It wouldn’t actually be a consideration at all.

Let me explain why.

Firstly, there is a really really good reason why Oracle doesn’t allow us to create a Unique Index in which the Partition key is not part of a Local Index. It’s called protecting us from ourselves !!

Let’s start by mentioning constraints again.

Remember, the main reason we have indexes policing PK and Unique constraints is so that Oracle can very quickly and efficiently determine whether or not a new value already exists. Do a quick index look-up, is the value there, yes or no, allow the insert (or update), yes or no.

Just imagine for one moment what would happen if Oracle actually allowed us to create a Unique Local index in which the index didn’t include the partitioned column(s).

Lets say a table is Range Partitioned on column ’A’ and we try and create a Unique Local index on just column ‘B’. Let’s assume we have (say) 500 table partitions meaning we must therefore have 500 local index partitions as well. When we insert a new value for our unique index for value B, it will attempt to do so in the corresponding local index partition as governed by the value A for the new row. However Oracle can’t just check this one index partition for uniqueness to ensure value of column B doesn’t already exist, Oracle would need to check all 500 index partitions because it would be possible for our new value of column B to potentially have previously been inserted into any of the other 499 partitions !!

Each and every insert into our partitioned table (partitioned by column A) therefore would require Oracle to check all (say)500 index partitions each and every time to check for duplicates of column B. Again, it’s important to understand that any given value of column B could potentially be in any of the 500 partitions, IF Oracle allowed us to create a Local Partitioned Index just on column B.

Checking all 500 index partitions looking for a specific value of column B would obviously be impractical, inefficient and totally un-scalable. Therefore Oracle doesn’t allow us to do this. It doesn’t allow us to create a Local index in which the indexed columns does’t include the partitioning columns as well.

This is actually a good thing.

If you want to create a Unique index in a partitioned table, you MUST either add all the partitioned columns and make it part of the LOCAL unique index (so that way each and every insert would only have to check the one local partition as this value is known now it’s part of the index) or you must create it as a GLOBAL index (in which again, Oracle only has to check the one index structure).

It actually makes a lot of sense to do this.

Moving onto the second part of the question. Let’s just use a Local Non-Unique index to police our PK constraints then.

Fortunately this isn’t allowed either for exactly the same reasons. You can’t create a Local Non-unique index to police a PK (or Unique) constraint if the Constraint does not also include the partitioned columns. Otherwise again, Oracle would need to check each and every index partition to determine whether the constraint has been violated or not.

If you attempt to use an existing Local Non-Unique index to police a PK or Unique constraint that does not contain the partitioned columns, you will get an error saying it can’t create the (by default Global index) because the useless Local Non-Unique index (from a policing the constraint point of view) already exists.

Again if you want to create a Non-Unique index to police a PK or Unique constraint you must either ensure the constraint includes all the partitioned columns in which case it can be Local or you must use a Global Non-Unique index.

In other words, the rules apply equally to both Unique and Non-Unique indexes.

So it’s not really a case of Oracle not allowing one to create a Local Unique index without including the partitioned columns (although that’s of course true) but really a case of Oracle not allowing a PK or Unique *constraint* to be policed via *any* Local index (whether Unique or Non-Unique), unless the partitioned columns are also included.

reference:http://richardfoote.wordpress.com/2007/12/20/local-index-issue-with-partitioned-pk-and-unique-key-constraints/

BTREE索引浅解

December 22, 2011 maintain, oracle No comments

结构如下图所示:

对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值;第二个字段为四个字节,表示所链接的索引块的地址,该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到,对于根节点块来说,包含三条记录,分别为(0 B1)、(500 B2)、(1000 B3),它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

从物理位置上来讲,index block 内部是没有顺序的,不重整的话,是按照进入先后顺序排的, 也就是我们通常讲 块间有序,块内无序。
对于叶子节点块来说,其所包含的索引条目与分支节点一样,都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)也具有两个字段。第一个字段表示索引的键值,对于单列索引来说是一个值;而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID,该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话,则该ROWID占用6个字节;如果索引是创建在分区表上的全局索引的话,则该ROWID占用10个字节。

一次LOB字段的查询导致的系统问题以及sql_id变化的探究

December 20, 2011 maintain, oracle No comments

16号早上10点左右发现系统变慢,查看等待事件 发现大量i/o salve wait, load 比值增加,等待语句sql_id为 38wm92jbxu0vf


09:59:30 up 80 days, 8:00, 2 users, load average: 16.02, 4.67, 2.95

SID USERNAME MACHINE EVENT SQL ST LT LOGON_TIME
—— ———- ———————- —————————— —————————- — —— ———-
1753 USER_DATA2 JDBC Thin Client log file sync /cqy9u8yvwhftq A 0 22175
2250 USER_DATA2 JDBC Thin Client SQL*Net more data from client /3s4uqa7n9z9hf I 0 22087
2115 USER_DATA2 JDBC Thin Client SQL*Net more data from client /3s4uqa7n9z9hf I 0 22746
2096 PROD_DATA2 JDBC Thin Client SQL*Net message from client 30hsdmcf9zyhq/34vfybbn2m7bc A 0 27989
835 USER_DATA2 JDBC Thin Client direct path read 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22194
928 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22749
947 USER_DATA2 JDBC Thin Client SQL*Net more data to client 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22682
957 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22754
961 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22751
972 USER_DATA2 JDBC Thin Client SQL*Net message from client 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22172
981 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22177
985 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22174
991 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22172
992 USER_DATA2 JDBC Thin Client i/o slave wait 38wm92jbxu0vf/3s4uqa7n9z9hf A 0 22176
994 USER_DATA2 JDBC Thin Client i/o slave wait

OSW报告显示系统在9:58-10:00之间经历了大量的io wait,从下面的数据可以清楚反应出来

zzz ***Fri Dec 16 09:58:00 CST 2011
avg-cpu: %user %nice %sys %iowait %idle
3.05 0.00 2.62 0.77 93.57
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
dm-8 0.00 0.00 87.96 11.37 1102.34 168.56 551.17 84.28 12.79 0.11 1.07 0.67 6.66
dm-9 0.00 0.00 31.77 0.33 508.36 5.35 254.18 2.68 16.00 0.02 0.53 0.52 1.67
dm-10 0.00 0.00 190.97 13.71 2654.18 101.67 1327.09 50.84 13.46 0.13 0.62 0.48 9.80
dm-11 0.00 0.00 53.18 4.01 775.92 33.44 387.96 16.72 14.15 0.07 1.24 1.07 6.12
dm-12 0.00 0.00 102.34 1.34 1886.29 10.03 943.14 5.02 18.29 0.09 0.83 0.65 6.69
dm-13 0.00 0.00 66.89 0.33 1124.08 0.33 562.04 0.17 16.73 0.07 1.01 0.78 5.22
dm-14 0.00 0.00 1.00 1.00 1.00 1.00 0.50 0.50 1.00 0.00 1.17 1.17 0.23
dm-15 0.00 0.00 102.01 1.34 1880.94 10.03 940.47 5.02 18.30 0.09 0.83 0.65 6.72

zzz ***Fri Dec 16 09:59:30 CST 2011
avg-cpu: %user %nice %sys %iowait %idle
18.32 0.00 60.82 16.95 3.92

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util

dm-8 0.00 0.00 1610.71 0.40 25771.43 3.17 12885.71 1.59 16.00 16.45 10.25 0.59 94.88
dm-9 0.00 0.00 991.27 0.79 15836.51 12.70 7918.25 6.35 15.98 9.37 9.49 0.94 93.41
dm-10 0.00 0.00 4983.73 0.79 79752.38 19.05 39876.19 9.52 16.00 112.20 23.08 0.22 108.45
dm-11 0.00 0.00 1287.70 0.40 20603.17 6.35 10301.59 3.17 16.00 11.40 8.87 0.76 98.41
dm-12 0.00 0.00 2853.17 0.00 45930.16 0.00 22965.08 0.00 16.10 37.76 13.28 0.36 103.73
dm-13 0.00 0.00 1939.68 6.75 30757.14 29.76 15378.57 14.88 15.82 18.77 9.68 0.50 96.67
dm-14 0.00 0.00 1.19 1.19 1.19 1.19 0.60 0.60 1.00 0.02 8.17 8.17 1.94
dm-15 0.00 0.00 2853.17 0.00 45930.16 0.00 22965.08 0.00 16.10 37.80 13.28 0.36 103.89

整体负载在9:59左右飙升,sys%比例增加,idle%接近于0 wait严重 cpu严重不足

CGet Phys Log LogC CPU Redo Execs HParse Parse Enq Load User% Sys% Idle% %Wait Gc_Cr_Req Gc_Cr_Sen Gc_Ccr_Req Gc_Ccr_Sen
12-16 09:57:56 376 7 0 2 921 7 1856 0 1850 11 0 2 2 96 0 82 222 59 471
12-16 09:58:06 110 4 0 0 634 466 3003 23 3002 4 1 1 1 97 0 131 180 340 646
12-16 09:58:17 99 6 0 3 608 25 1465 0 1465 9 1 2 1 97 0 79 102 56 415
12-16 09:58:27 96 5 0 1 541 131 1419 0 1414 3 1 2 1 97 0 109 101 122 705
12-16 09:58:37 148 6 0 2 799 121 5081 5 5067 6 1 2 1 96 0 159 135 138 407
12-16 09:58:47 137 3 0 1 490 83 2138 0 2137 4 1 3 1 95 0 99 191 129 309
12-16 09:58:57 367 5 0 1 825 77 1483 0 1477 12 1 1 1 97 0 131 175 99 348
12-16 09:59:07 147 18 0 1 1248 93 2617 5 2617 5 1 6 9 84 1 114 145 122 554
12-16 09:59:18 358 86 0 6 7844 193 3197 1 3153 7 3 17 43 9 31 96 75 123 681
12-16 09:59:28 412 101 0 0 12639 197 3497 2 3492 4 6 17 54 4 24 113 208 167 397
12-16 09:59:38 489 117 0 2 13986 75 3922 0 3923 4 27 17 55 6 22 115 208 96 451
12-16 09:59:48 695 116 0 1 14102 30 4477 3 4476 5 30 18 63 5 15 80 171 93 154
12-16 09:59:58 443 120 0 2 13974 31 3545 0 3541 13 36 18 60 6 16 63 63 68 455
12-16 10:00:09 2083 117 2 3 14324 1337 6947 36 6949 3 35 28 56 5 11 3628 722 1424 868
12-16 10:00:19 1135 117 28 34 13526 698 9824 9 9855 6 34 23 56 2 19 301 1008 499 675
12-16 10:00:29 1132 107 8 10 14043 1228 12309 2 12314 19 36 26 52 3 19 1238 1137 910 997
12-16 10:00:39 724 73 22 25 9066 2620 11982 5 11920 21 34 6 1 91 1 505 514 1406 1002
12-16 10:00:49 686 11 -60 1 2597 2214 26911 4 26908 9 27 10 3 86 2 412 780 669 1110
12-16 10:00:59 1118 11 0 3 3106 513 23849 6 23847 16 24 14 4 81 1 638 586 650 956
12-16 10:01:10 664 7 0 0 2039 1498 23106 3 23106 38 21 5 2 92 1 541 349 612 843

查看当时的awr报告:

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
direct path read 1,335,925 4,857 4 191.7 User I/O
CPU time   2,260   89.2  
SQL*Net more data to client 4,567,337 380 0 15.0 Network
SQL*Net more data from client 72,356 118 2 4.7 Network
log file sequential read 28,784 61 2 2.4 System I/O

发现大量io等待事件

Time Model Statistics


  • Wait Class


  • Wait Events


  • Background Wait Events


  • Operating System Statistics


  • Service Statistics


  • Service Wait Class Stats

    Back to Top

    Time Model Statistics

    • Total time in database user-calls (DB Time): 2533.6s
    • Statistics including the word “background” measure background process
      time, and so do not contribute to the DB time statistic

    • Ordered by % or DB time desc, Statistic name

    Statistic Name Time (s) % of DB Time
    DB CPU 2,260.24 89.21
    sql execute elapsed time 891.84 35.20
    parse time elapsed 35.13 1.39
    hard parse elapsed time 4.79 0.19
    hard parse (sharing criteria) elapsed time 2.34 0.09
    connection management call elapsed time 1.30 0.05
    sequence load elapsed time 0.73 0.03
    PL/SQL execution elapsed time 0.36 0.01
    hard parse (bind mismatch) elapsed time 0.04 0.00
    failed parse elapsed time 0.01 0.00
    repeated bind elapsed time 0.00 0.00
    DB time 2,533.59  
    background elapsed time 478.83  
    background cpu time 120.28  

    sql execute elapsed time 所占比重极少,db_time被花费在大量的等待上。

    导致system在等待什么,为何sys%突然飙升,大量的i/o slave wait 又从何而来,一切仅仅1分钟左右的事件,我们只能从event_wait着手

    sys@pri> select sql_fulltext from v$sql where sql_id = ’38wm92jbxu0vf’;

    no rows selected

    查看38wm92jbxu0vf并未查到任何sql_text语句

    SQL> @sample sql_text v$sqlarea “sql_id = ’38wm92jbxu0vf'” 1000

    no rows selected

    SQL> — doesn’t look like we’ll ever catch it in the shared pool

    同样没有任何收获,难道在shared_pool中不存在cursor?

    但是我们却从v$session中发现了线索

    sys@pri> select username, sid, serial#, status, sql_id, event, seconds_in_wait
    from v$session
    where username like nvl(‘&username’,username)
    and sid like nvl(‘&sid’,sid)
    and sql_id=’38wm92jbxu0vf’
    order by username, sid, serial#; 2 3 4 5 6
    Enter value for username:
    old 3: where username like nvl(‘&username’,username)
    new 3: where username like nvl(”,username)
    Enter value for sid:
    old 4: and sid like nvl(‘&sid’,sid)
    new 4: and sid like nvl(”,sid)

    USERNAME SID SERIAL# STATUS SQL_ID EVENT SECONDS_IN_WAIT
    ——————– ———- ———- ——– ————- —————————— —————
    USER_DATA2 1664 6 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51004
    USER_DATA2 1665 6 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51002
    USER_DATA2 1666 7 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51004
    USER_DATA2 1668 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51002
    USER_DATA2 1669 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51002
    USER_DATA2 1671 8 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51001
    USER_DATA2 1672 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 10549
    USER_DATA2 1673 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51001
    USER_DATA2 1674 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51002
    USER_DATA2 1676 5 INACTIVE 38wm92jbxu0vf SQL*Net message from client 51001

    根据我们的猜想,session并没有使用 shared_pool中的cursor,而是使用了cached_cursor


    sys@pri> select distinct * from v$open_cursor
    where rownum < 10 and sql_id ='38wm92jbxu0vf'; 2 3 SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ 0000001054723E30 2241 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 0000001058705460 2135 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 00000010546A8710 1689 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 00000010586FE908 2105 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 00000010586E10B8 1973 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 000000105F7094A0 2140 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 00000010586F6838 2069 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 000000105F6DE5A0 1948 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0 00000010546D6100 1893 USER_DATA2 000000105B6DECF0 1474102126 38wm92jbxu0vf table_4_9_4ba92_0_0_0

    正如我们所见sql_text中包含了 table_4_9_4ba92_0_0_0, 根据tom所提供的信息:asktom.oracle.com

    sys@pri> select owner, object_name, object_type
    from dba_objects
    where object_id = (select to_number(‘&hex_value’,’XXXXXX’) from dual); 2 3
    Enter value for hex_value: 4ba92
    old 3: where object_id = (select to_number(‘&hex_value’,’XXXXXX’) from dual)
    new 3: where object_id = (select to_number(‘4ba92′,’XXXXXX’) from dual)

    OWNER OBJECT_NAME OBJECT_TYPE
    —————————— ——————————————————————————————————————————– ——————-
    USER_DATA2 GROUPON_ACTIVITY TABLE

    查到了GROUPON_ACTIVITY这张表 我们来看看GROUPON_ACTIVITY这张表的结构:

    sys@pri> desc user_data2.GROUPON_ACTIVITY;


    Name Type
    —————————– ————–
    ID NUMBER(18)
    NAME VARCHAR2(900)
    START_TIME DATE
    END_TIME DATE
    PRODUCT_CODE VARCHAR2(10)
    PRICE NUMBER(11,2)
    PEOPLE_LOWER NUMBER(9)
    PEOPLE_UPPER NUMBER(9)
    SELLING_POINT NCLOB
    PROMPT NCLOB
    NOTE VARCHAR2(1000)
    STATUS NUMBER(9)

    包含了两个NCLOB列,验证了tom的判断

    那么我们如何找出引起io的sql语句呢,通过上述分析,我们得知SQL通过access一个lob列产生了一个新的sql_id,那么必然会产生一个PREV_SQL_ID 我们是否可以从这里着手呢?

    sys@pri> select username, sid, serial#, status, sql_id, prev_sql_id, event, seconds_in_wait
    from v$session
    where username like nvl(‘&username’,username)
    and sid like nvl(‘&sid’,sid)
    and sql_id=’38wm92jbxu0vf’
    order by username, sid, serial#; 2 3 4 5 6
    Enter value for username:
    old 3: where username like nvl(‘&username’,username)
    new 3: where username like nvl(”,username)
    Enter value for sid:
    old 4: and sid like nvl(‘&sid’,sid)
    new 4: and sid like nvl(”,sid)

    USERNAME SID SERIAL# STATUS SQL_ID PREV_SQL_ID EVENT SECONDS_IN_WAIT
    —————————— ———- ———- ——– ————- ————- —————————————————————- —————
    USER_DATA2 1664 6 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49242
    USER_DATA2 1665 6 INACTIVE 38wm92jbxu0vf b0d46gq8gg0t0 SQL*Net message from client 49240
    USER_DATA2 1666 7 INACTIVE 38wm92jbxu0vf 8kumczaskxmmk SQL*Net message from client 49242
    USER_DATA2 1668 5 INACTIVE 38wm92jbxu0vf b0d46gq8gg0t0 SQL*Net message from client 49240
    USER_DATA2 1669 5 INACTIVE 38wm92jbxu0vf b0d46gq8gg0t0 SQL*Net message from client 49240
    USER_DATA2 1670 5 INACTIVE 38wm92jbxu0vf 6at2bn8hzgwf3 SQL*Net message from client 339
    USER_DATA2 1671 8 INACTIVE 38wm92jbxu0vf b0d46gq8gg0t0 SQL*Net message from client 49239
    USER_DATA2 1672 5 INACTIVE 38wm92jbxu0vf 6at2bn8hzgwf3 SQL*Net message from client 8787
    USER_DATA2 1673 5 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1674 5 INACTIVE 38wm92jbxu0vf 0hbv174zz7ksa SQL*Net message from client 49240
    USER_DATA2 1676 5 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1677 132 INACTIVE 38wm92jbxu0vf 8kumczaskxmmk SQL*Net message from client 49242
    USER_DATA2 1684 1772 INACTIVE 38wm92jbxu0vf dbgvdpt1rwvm2 SQL*Net message from client 49239
    USER_DATA2 1686 109 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49240
    USER_DATA2 1687 427 INACTIVE 38wm92jbxu0vf 804m3bb9zh20j SQL*Net message from client 3
    USER_DATA2 1688 5791 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1689 2114 INACTIVE 38wm92jbxu0vf 8kumczaskxmmk SQL*Net message from client 49240
    USER_DATA2 1691 9051 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49240
    USER_DATA2 1692 12031 INACTIVE 38wm92jbxu0vf 6ryuz4q7wqnd3 SQL*Net message from client 49240
    USER_DATA2 1693 2288 INACTIVE 38wm92jbxu0vf 8kumczaskxmmk SQL*Net message from client 49242
    USER_DATA2 1694 2087 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1697 5111 INACTIVE 38wm92jbxu0vf b0d46gq8gg0t0 SQL*Net message from client 49240
    USER_DATA2 1698 102 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1700 560 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49242
    USER_DATA2 1701 934 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1702 10471 INACTIVE 38wm92jbxu0vf ar18k5pwfsjdg SQL*Net message from client 2896
    USER_DATA2 1703 4347 INACTIVE 38wm92jbxu0vf 6at2bn8hzgwf3 SQL*Net message from client 49242
    USER_DATA2 1705 98 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49240
    USER_DATA2 1707 2957 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49239
    USER_DATA2 1709 2055 INACTIVE 38wm92jbxu0vf db6qwu9sytmf7 SQL*Net message from client 49240
    USER_DATA2 1710 470 INACTIVE 38wm92jbxu0vf 0hbv174zz7ksa SQL*Net message from client 49242

    我们终于有了发现,通过对PREV_SQL_ID的逐一查找 我们终于抓出了这个SQL:

    SELECT g.*,ga.area_id,
    CASE
    WHEN g.start_time > SYSDATE THEN
    ‘1’
    WHEN g.end_time < SYSDATE THEN '-1 ' ELSE '0' END AS TYPE FROM xx g INNER JOIN yy ga ON g.id = ga.groupon_id WHERE ga.area_id = :1 AND g.i d in (:2, :3, :4, :5, :6, ...)

    跟进17号同一时段的system负载:

    CGet Phys Log LogC CPU Redo Execs HParse Parse Enq Load User% Sys% Idle% %Wait Gc_Cr_Req Gc_Cr_Sen Gc_Ccr_Req Gc_Ccr_Sen

    12-17 09:59:04 2 0 0 2 35 4 157 0 156 1 0 0 1 99 0 14 27 11 51
    12-17 09:59:14 5 0 0 1 51 26 463 0 458 2 0 1 2 97 0 16 76 42 57
    12-17 09:59:25 6 0 0 11 104 21 967 0 977 1 0 1 2 96 0 26 30 25 52
    12-17 09:59:35 26 0 1 9 146 46 1467 1 1471 9 0 3 3 94 0 255 56 67 52
    12-17 09:59:45 8 0 -1 11 126 10 1908 1 1914 1 0 2 3 95 0 14 35 16 86
    12-17 09:59:55 11 0 0 2 115 91 2464 2 2465 0 0 1 3 96 0 33 111 78 53
    12-17 10:00:05 1103 0 0 12 1249 289 3117 4 3125 2 0 10 3 86 0 739 336 794 332
    12-17 10:00:15 1162 0 14 28 2632 1055 3414 5 3357 22 1 7 5 88 1 352 550 634 498
    12-17 10:00:26 565 0 5 7 1302 319 4929 2 4936 3 1 3 4 93 0 175 264 245 239
    12-17 10:00:36 376 0 9 20 622 200 4151 2 4165 10 1 3 3 94 0 175 317 187 315
    12-17 10:00:46 331 0 -28 0 578 313 4337 1 4331 2 1 3 3 94 0 153 270 302 322
    12-17 10:00:56 598 0 0 0 756 256 5204 8 5203 2 1 5 2 93 0 160 280 233 284
    12-17 10:01:06 730 0 0 8 843 628 2955 56 2964 3 1 3 2 95 0 380 291 621 741

    总结

    一张含有nclob字段表的查询语句,由于SQL在读取lob字段的时候采用direct path的方式,并且access LOB字段所产生的sql_id特殊性,迷惑了我们很久,
    同时由于这个APP系统的特殊性,大量的瞬间查询导致了system iowait等待,在awr里反映出大量的direct path read等待,伴随着大量的Network等待,这些都是逐一显现出来的
    同时由于大量的io请求,导致cpu的繁忙,从而导致了idle的急速下降,这些都是接二连三所出现的反映。最后通过cache这些缓存到应用端 从而解决了问题。

  • Limitations of DRIVING_SITE Hint

    December 19, 2011 maintain, oracle No comments

    Limitations of DRIVING_SITE Hint

    Applies to:

    Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2
    This problem can occur on any platform.

    Symptoms

    For optimization of distributed queries Oracle provides the DRIVING_SITE hint.
    The Oracle? Database Administrator’s Guide 11g Release 1 (11.1)
    (http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm#sthref3180)
    describes it as follows:


    Using the DRIVING_SITE Hint

    The DRIVING_SITE hint lets you specify the site where the query execution is performed. It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.

    Following is an example of a SELECT statement with a DRIVING_SITE hint:

    SELECT /*+ DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com dept
    WHERE emp.deptno = dept.deptno;

    So when you’re using this hint you will see in the query execution plan REMOTE in the corresponding operation section, here’s an example for a SELECT. Note that we have the table example_data on both databases, i.e. we have a local table example_data and a remote table example_data. The SQL is launched from the local side:


    select /*+ DRIVING_SITE(remote_example_data) */ *
    from example_data local_example_data, example_data@v10203 remote_example_data
    where local_example_data.x = remote_example_data.x;

    —————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
    —————————————————————————————————
    | 0 | SELECT STATEMENT | | 164 | 4264 | 5 (20)| 00:00:01 | | |
    |* 1 | HASH JOIN | | 164 | 4264 | 5 (20)| 00:00:01 | | |
    | 2 | TABLE ACCESS FULL| EXAMPLE_DATA | 2 | 26 | 2 (0)| 00:00:01 | | |
    | 3 | REMOTE | EXAMPLE_DATA | 82 | 1066 | 2 (0)| 00:00:01 | V10203 | R->S |
    —————————————————————————————————

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

    1 – access(“LOCAL_EXAMPLE_DATA”.”X”=”REMOTE_EXAMPLE_DATA”.”X”)

    Remote SQL Information (identified by operation id):
    —————————————————-

    3 – SELECT “X” FROM “EXAMPLE_DATA” “REMOTE_EXAMPLE_DATA” (accessing ‘V10203’ )

    So this works as expected as is also clearly visible in the “Remote SQL Information” section.

    You might wonder what happens when you do this in a CREATE TABLE AS SELECT, i.e.:

    create table remote_new_data (x,y) — need to give unique names to columns
    as
    select /*+ DRIVING_SITE(remote_example_data) */ *
    from example_data local_example_data, example_data@v10203 remote_example_data
    where local_example_data.x = remote_example_data.x;

    When you check the execution plan, you’ll notice hint seems to work for the query itself, but the newly
    created table is created locally. That also doesn’t change if you include the DRIVING_SITE hint directly after the CREATE TABLE portion, you’ll get an execution plan like:


    ———————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
    ———————————————————————————————————-
    | 0 | CREATE TABLE STATEMENT | | 8 | 48 | 6 (17)| 00:00:01 | | |
    | 1 | LOAD AS SELECT | REMOTE_NEW_DATA | | | | | | |
    |* 2| HASH JOIN | | 8 | 48 | 5 (20)| 00:00:01 | | |
    | 3 | REMOTE | EXAMPLE_DATA | 2 | 6 | 2 (0)| 00:00:01 | V10203 |R->S |
    | 4 | TABLE ACCESS FULL | EXAMPLE_DATA | 8 | 24 | 2 (0)| 00:00:01 | | |
    ———————————————————————————————————-

    PLAN_TABLE_OUTPUT
    ——————————————————————————————————————————————————————————————————–

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

    2 – access(“LOCAL_EXAMPLE_DATA”.”X”=”REMOTE_EXAMPLE_DATA”.”X”)

    Remote SQL Information (identified by operation id):
    —————————————————-

    3 – SELECT /*+ */ “X” FROM “EXAMPLE_DATA” “REMOTE_EXAMPLE_DATA” (accessing ‘V10203’ )

    Cause

    What happened? That’s actually expected behaviour, a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this. DRIVING_SITE hint means that entire cursor (not a subquey) is supposed to be mapped remotely. That also means CREATE TABLE cannot be executed remotely (which is also the reason why you get ORA-2021 when you try to accomplish this with an Create Table table_name@remote_database).

    Solution

    So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL.

    ORA-01555 on Active Physical standby and patchset issue without Central Inventory

    December 7, 2011 maintain, oracle, replication 2 comments

    今天在生产库解决一个ORA-01555 on Active Physical standby问题:
    Applies to:

    Oracle Server – Enterprise Edition – Version: 11.2.0.2.0 and later [Release: 11.2 and later ]
    Information in this document applies to any platform.
    Primary and Standby databases at 11.2.0.2
    Symptoms

    After upgrading to 11.2.0.2 we see this ORA-1555 on the Standby Database. It is not possible to logon with system, dbsnmp or other db-users. Only sys as sysdba is working.
    It’s like we are running on system’s undo Tablespace, – and not the one which has been restored..

    查看系统alert日志发现:
    ORA-01555 caused by SQL statement below (SQL ID: 1f9apznp767fc, Query Duration=0 sec, SCN: 0x0006.62ab3687):
    select object_name synonym_name, object_name, owner object_owner, status, object_type
    from sys.all_objects o
    where o.owner = object_owner
    and o.object_type in (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘SEQUENCE’, ‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE’, ‘TYPE’)
    and o.object_name not like ‘BIN$%’

    order by o.object_type, o.object_name

    一个简单的查询 都导致了ora-01555 undo回滚段出现严重问题,同时出现如下问题:

    ORA-00313: open failed for members of log group 15 of thread 1
    ORA-00312: online log 15 thread 1: ‘/data/oracle/oradata/edw1/redo15.log’
    ORA-27037: unable to obtain file status

    客户端报如下错误:

    同时发现mutex 等待 以及大量library cache lock 等待

    通过查找metalink 发现与以下描述极其相似

    This problem is introduced in 11.2.0.2 by the fix for bug 9214531

    A self-hang / spin can occur when getting a library cache lock
    leading to a wider hang scenario with other sessions waiting
    for the blocked lock.

    Rediscovery Notes:
    A process spins in kglLock(),
    other processes waiting for the spinning session
    ORA-1555 on Active data Guard

    解决方案:通过打p10018789_112020_Linux-x86-64解决此问题

    打patch 过程过遇到很多问题,下面一一阐述:

    opatch lsinventory raise error code 104

    OPatch cannot find a valid oraInst.loc file to locate Central Inventory.
    OPatch failed with error code 104

    通过了解发现此RDBMS是通过tar包过来直接relink的,缺少全局的 Inventory 所以我们需要手动创建Inventory
    首先要编辑一个oraInst.loc文件,使之指向我们要创建全局inventory的目录

    inventory_loc=/data/oracle/oraInventory
    inst_group=oinstall

    cd $ORACLE_HOME/oui/bin
    ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”” ORACLE_HOME_NAME=”

    我们这里采用的是 ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

    The inventory pointer is located at /etc/oraInst.loc

    The inventory is located at /data/oracle/oraInventory

    ‘AttachHome’ was successful.

    针对rac的Inventory重建 我们需要指定crs_home 和oracle_home

    ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Ora_Crs_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2 CRS=true

    ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Oracle_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2

    下面的过程就显得很简单了:

    解压缩补丁文件

    unzip p10018789_112020_Linux-x86-64.zip

    重新生成oraInventory

    ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

    设置环境变量

    export PATH=$ORACLE_HOME/OPatch:$PATH

    停止redo apply

    alter database recover managed standby database cancel;

    关闭数据库

    shutdownn immediate

    打补丁

    cd 10018789

    opatch apply

    打开数据库并开始redo应用

    startup mount

    alter database open read only

    alter database recover managed standby database disconnect from session using current logfile;

    观察日志 发现问题解决

    SQL*Loader to load data with virtual column

    December 6, 2011 maintain, oracle No comments

    在使用sqlldr工具导入数据的时候 往往需要将导入的某列按顺序排列,我们可以使用sqlldr 的虚拟列功能实现

    [oracle@liu ~]$ sqlplus ‘/as sysdba’

    SQL*Plus: Release 10.2.0.1.0 – Production on Tue Dec 6 11:00:55 2011

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    With the Partitioning, OLAP and Data Mining options

    SQL> create table coke_coupon_record
    (id int,
    VERIFICATION_CODE varchar(20),
    HAS_SEND number(8));

    Table created.

    cat coke_coupon_record.ctl
    load data
    characterset ZHS16GBK
    infile ‘/home/oracle/tmp_coke_coupon_record.txt’
    append into table coke_coupon_record
    fields terminated by WHITESPACE
    TRAILING NULLCOLS
    (
    “ID” sequence(MAX,1), ——MAX means Oracle will auto find the maxvalue of this column and will increase by 1
    “VERIFICATION_CODE” ,
    “HAS_SEND” “0”
    )


    cat tmp_coke_coupon_record.txt

    FS39F6Z
    8GP4G3T
    N9VN8SP
    3NY7FHS
    C9RX39Q
    6F79QBT
    CZLTU4P
    PG4KVG2
    N55USUD
    LV6VT5W
    K6UBQY5
    AV95KEK
    LGA4AGU
    GZWP648
    HLRK7ZA
    H58TG4E
    DCR37L3
    TCN5R59
    GTUXEN5
    FLGSL35
    5BSQV2W
    4XTGVFQ
    WQCEQFN
    B2MHSGD
    FPKWCVY
    2QUACHR
    M9UZ4ZA
    SBKHTSQ
    RMEXDZC
    BXT82CT
    DQQ33S9
    LN2EDPY
    EASAYR4
    F6FKM92
    LLZQWKW
    KG58R8X
    7S7F9X5
    E4CW8DS
    BM558W2
    B36MD2K
    GSGWQ3C
    2XF8EG2
    ZEW8ZP9
    MG6ND7L
    FQ7V73G
    RLYGNXU
    NCWDQUD
    YUW6X22
    GKA6PXY
    7HFZ9Y5
    QF6EUDV
    FKLLHQM
    36WUYDU
    PZ5TBE2
    3T6DBFU
    RALXBRA
    GSQG8BN
    GE7863V
    KAZGRUZ
    DLF8RFD
    2XR26DW
    BZA4WHX
    ZUZM8XA
    EEHY88Z
    6ZCD3AG
    X7GWUKP
    GHG9YXR
    TU32QCB
    W33G23G
    K2HA339
    94K27UY
    F9AASRP
    TPMVPK7
    YEE9M2Y
    2QPL6C7
    ZDVRTQT
    DK3EWAD
    2TR3S8Z
    N9ZHC3E
    W4XNXGC
    HCUDDLA
    YS2V25W
    GMG9ZRT

    [oracle@liu ~]$ sqlldr \’/ as sysdba\’ control=coke_coupon_record.ctl log=ceshi.log

    SQL*Loader: Release 10.2.0.1.0 – Production on Tue Dec 6 11:49:05 2011

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Commit point reached – logical record count 64
    Commit point reached – logical record count 83
    [oracle@liu ~]$ sqlplus ‘/as sysdba’

    SQL*Plus: Release 10.2.0.1.0 – Production on Tue Dec 6 11:49:15 2011

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    With the Partitioning, OLAP and Data Mining options


    SQL> select * from coke_coupon_record;

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    1 FS39F6Z 0
    2 8GP4G3T 0
    3 N9VN8SP 0
    4 3NY7FHS 0
    5 C9RX39Q 0
    6 6F79QBT 0
    7 CZLTU4P 0
    8 PG4KVG2 0
    9 N55USUD 0
    10 LV6VT5W 0
    11 K6UBQY5 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    12 AV95KEK 0
    13 LGA4AGU 0
    14 GZWP648 0
    15 HLRK7ZA 0
    16 H58TG4E 0
    17 DCR37L3 0
    18 TCN5R59 0
    19 GTUXEN5 0
    20 FLGSL35 0
    21 5BSQV2W 0
    22 4XTGVFQ 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    23 WQCEQFN 0
    24 B2MHSGD 0
    25 FPKWCVY 0
    26 2QUACHR 0
    27 M9UZ4ZA 0
    28 SBKHTSQ 0
    29 RMEXDZC 0
    30 BXT82CT 0
    31 DQQ33S9 0
    32 LN2EDPY 0
    33 EASAYR4 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    34 F6FKM92 0
    35 LLZQWKW 0
    36 KG58R8X 0
    37 7S7F9X5 0
    38 E4CW8DS 0
    39 BM558W2 0
    40 B36MD2K 0
    41 GSGWQ3C 0
    42 2XF8EG2 0
    43 ZEW8ZP9 0
    44 MG6ND7L 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    45 FQ7V73G 0
    46 RLYGNXU 0
    47 NCWDQUD 0
    48 YUW6X22 0
    49 GKA6PXY 0
    50 7HFZ9Y5 0
    51 QF6EUDV 0
    52 FKLLHQM 0
    53 36WUYDU 0
    54 PZ5TBE2 0
    55 3T6DBFU 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    56 RALXBRA 0
    57 GSQG8BN 0
    58 GE7863V 0
    59 KAZGRUZ 0
    60 DLF8RFD 0
    61 2XR26DW 0
    62 BZA4WHX 0
    63 ZUZM8XA 0
    64 EEHY88Z 0
    65 6ZCD3AG 0
    66 X7GWUKP 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    67 GHG9YXR 0
    68 TU32QCB 0
    69 W33G23G 0
    70 K2HA339 0
    71 94K27UY 0
    72 F9AASRP 0
    73 TPMVPK7 0
    74 YEE9M2Y 0
    75 2QPL6C7 0
    76 ZDVRTQT 0
    77 DK3EWAD 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    78 2TR3S8Z 0
    79 N9ZHC3E 0
    80 W4XNXGC 0
    81 HCUDDLA 0
    82 YS2V25W 0
    83 GMG9ZRT 0

    83 rows selected.

    同样我们可以调用sequence实现

    [oracle@liu ~]$ cat coke_coupon_record.ctl

    load data
    characterset ZHS16GBK
    infile ‘/home/oracle/tmp_coke_coupon_record.txt’
    append into table coke_coupon_record
    fields terminated by WHITESPACE
    TRAILING NULLCOLS
    (VERIFICATION_CODE,
    HAS_SEND “0”,
    ID “seq_coke_coupon_record.nextval” ——注意sequence 列需要放在最后
    )

    SQL>create sequence seq_coke_coupon_record start with 1 increment by 1 maxvalue 99999999 minvalue 1 cache 20;

    Sequence created

    SQL> select * from coke_coupon_record;

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    1 FS39F6Z 0
    2 8GP4G3T 0
    3 N9VN8SP 0
    4 3NY7FHS 0
    5 C9RX39Q 0
    6 6F79QBT 0
    7 CZLTU4P 0
    8 PG4KVG2 0
    9 N55USUD 0
    10 LV6VT5W 0
    11 K6UBQY5 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    12 AV95KEK 0
    13 LGA4AGU 0
    14 GZWP648 0
    15 HLRK7ZA 0
    16 H58TG4E 0
    17 DCR37L3 0
    18 TCN5R59 0
    19 GTUXEN5 0
    20 FLGSL35 0
    21 5BSQV2W 0
    22 4XTGVFQ 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    23 WQCEQFN 0
    24 B2MHSGD 0
    25 FPKWCVY 0
    26 2QUACHR 0
    27 M9UZ4ZA 0
    28 SBKHTSQ 0
    29 RMEXDZC 0
    30 BXT82CT 0
    31 DQQ33S9 0
    32 LN2EDPY 0
    33 EASAYR4 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    34 F6FKM92 0
    35 LLZQWKW 0
    36 KG58R8X 0
    37 7S7F9X5 0
    38 E4CW8DS 0
    39 BM558W2 0
    40 B36MD2K 0
    41 GSGWQ3C 0
    42 2XF8EG2 0
    43 ZEW8ZP9 0
    44 MG6ND7L 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    45 FQ7V73G 0
    46 RLYGNXU 0
    47 NCWDQUD 0
    48 YUW6X22 0
    49 GKA6PXY 0
    50 7HFZ9Y5 0
    51 QF6EUDV 0
    52 FKLLHQM 0
    53 36WUYDU 0
    54 PZ5TBE2 0
    55 3T6DBFU 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    56 RALXBRA 0
    57 GSQG8BN 0
    58 GE7863V 0
    59 KAZGRUZ 0
    60 DLF8RFD 0
    61 2XR26DW 0
    62 BZA4WHX 0
    63 ZUZM8XA 0
    64 EEHY88Z 0
    65 6ZCD3AG 0
    66 X7GWUKP 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    67 GHG9YXR 0
    68 TU32QCB 0
    69 W33G23G 0
    70 K2HA339 0
    71 94K27UY 0
    72 F9AASRP 0
    73 TPMVPK7 0
    74 YEE9M2Y 0
    75 2QPL6C7 0
    76 ZDVRTQT 0
    77 DK3EWAD 0

    ID VERIFICATION_CODE HAS_SEND
    ———- ——————– ———-
    78 2TR3S8Z 0
    79 N9ZHC3E 0
    80 W4XNXGC 0
    81 HCUDDLA 0
    82 YS2V25W 0
    83 GMG9ZRT 0

    83 rows selected.

    Reasons that a TX lock may be requested in S mode

    December 4, 2011 maintain, oracle 2 comments

    Oracle中的锁,一共有6种模式:

    0:none
    1:null 空
    2:Row-S 行共享(RS):共享表锁,sub share
    3:Row-X 行独占(RX):用于行的修改,sub exclusive
    4:Share 共享锁(S):阻止其他DML操作,share
    5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive
    6:exclusive 独占(X):独立访问使用,exclusive

    Example Tables

    The lock waits which can occur are demonstrated using the following tables.
    Connect as SCOTT/TIGER or some dummy user to set up the test environment using the following SQL:

    DROP TABLE tx_eg;
    CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) ) INITRANS 1 MAXTRANS 1;
    INSERT into tx_eg VALUES ( 1, ‘First’,’FEMALE’ );
    INSERT into tx_eg VALUES ( 2, ‘Second’,’MALE’ );
    INSERT into tx_eg VALUES ( 3, ‘Third’,’MALE’ );
    INSERT into tx_eg VALUES ( 4, ‘Fourth’,’MALE’ );
    INSERT into tx_eg VALUES ( 5, ‘Fifth’,’MALE’ );
    COMMIT;

    In the examples below three sessions are required:
    Ses#1 indicates the TX_EG table owners first session
    Ses#2 indicates the TX_EG table owners second session
    DBA indicates a SYSDBA user with access to View:V$LOCK
    Waits due to Row being locked by an active Transaction

    When a session updates a row in a table the row is locked by the sessions transaction. Other users may SELECT that row and will see the row as it was BEFORE the UPDATE occurred. If another session wishes to UPDATE the same row it has to wait for the first session to commit or rollback.
    The second session waits for the first sessions TX lock in EXCLUSIVE mode.

    –Ses#1:
    update tx_eg set txt=’Garbage’ where num=1;

    –Ses#2:
    update tx_eg set txt=’Garbage’ where num=1;

    –DBA:
    select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’;

    SID TY ID1 ID2 LMODE REQUEST
    ———- — ———- ———- ———- ———-
    8 TX 131075 597 6 0
    10 TX 131075 597 0 6
    This shows SID 10 is waiting for the TX lock held by SID 8 and it wants the lock in exclusive mode (as REQUEST=6).

    –DBA:
    select sid,p1raw, p2, p3 from v$session_wait where wait_time=0 and event=’enqueue’;

    SID P1RAW P2 P3
    ———- ——– ———- ———-
    10 54580006 131075 597
    > ~~~~ ~~ ~~~~~~ ~~~
    > type|mode id1 id2
    > TX 6 13107 597

    The next select shows the object_id and the exact row that the session is waiting for. This information is only valid in V$SESSION when a session is waiting due to a row level lock.
    As SID 10 is the waiter above then this is the session to look at in V$SESSION:

    –DBA:
    select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session
    where sid=10;

    ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
    ———- ———- ———- ———-
    3058 4 2683 0

    > The waiter is waiting for the TX lock in order to lock row 0
    > in file 4, block 2683 of object 3058.

    — Ses#1:
    rollback;

    –Ses#2:
    rollback;

    Waits due to Unique or Primary Key Constraint enforcement

    If a table has a primary key constraint, a unique constraint or a unique index then the uniqueness of the column/s referenced by the constraint is enforced by a unique index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not.

    –Ses#1:
    ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );

    –Ses#1:
    insert into tx_eg values (10,’New’,’MALE’);

    –Ses#2:
    insert into tx_eg values (10,’OtherNew’,null);

    –DBA:
    select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’;

    SID TY ID1 ID2 LMODE REQUEST
    ———- — ———- ———- ———- ———-
    8 TX 196625 39 6 0
    10 TX 262155 65 6 0
    10 TX 196625 39 0 4

    This shows SID 10 is waiting for the TX lock held by SID 8
    and it wants the lock in share mode (as REQUEST=4).
    SID 10 holds a TX lock for its own transaction.

    –Ses#1:
    commit;

    –Ses#2:
    ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated

    –Ses#2:
    rollback;

    Waits due to Insufficient ‘ITL’ slots in a Block

    Oracle keeps note of which rows are locked by which transaction in an area at the top of each data block known as the ‘interested transaction list’.
    The number of ITL slots in any block in an object is controlled by the INITRANS and MAXTRANS attributes. INITRANS is the number of slots initially created in a block when it is first used, while MAXTRANS places an upper bound on the number of entries allowed. Each transaction which wants to modify a block requires a slot in this ‘ITL’ list in the block.

    MAXTRANS places an upper bound on the number of concurrent transactions which can be active at any single point in time within a block.

    INITRANS provides a minimum guaranteed ‘per-block’ concurrency.

    If more than INITRANS but less than MAXTRANS transactions want to be active concurrently within the same block then the ITL list will be extended
    BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK.

    If there is no free ‘ITL’ then the requesting session will wait on one of the active transaction locks in mode 4.

    –Ses#1:
    update tx_eg set txt=’Garbage’ where num=1;

    –Ses#2:
    update tx_eg set txt=’Different’ where num=2;

    –DBA:
    select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock
    where type=’TX’;

    SID TY ID1 ID2 LMODE REQUEST
    ———- — ———- ———- ———- ———-
    8 TX 327688 48 6 0
    10 TX 327688 48 0 4

    This shows SID 10 is waiting for the TX lock held by SID 8
    and it wants the lock in share mode (as REQUEST=4).

    –Ses#1:
    commit;

    –Ses#2:
    commit;

    –Ses#1:
    ALTER TABLE tx_eg MAXTRANS 2;

    Ses#1:
    update tx_eg set txt=’First’ where num=1;

    –Ses#2:
    update tx_eg set txt=’Second’ where num=2;

    –Both rows update as there is space to grow the ITL list to accommodate
    both transactions.

    –Ses#1:
    commit;

    –Ses#2:
    commit;

    From 9.2 you can check the ITL Waits in v$segment_statistics with a query like:

    SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
    FROM v$segment_statistics t
    WHERE t.STATISTIC_NAME = ‘ITL waits’
    AND t.VALUE > 0;

    If need be, increase INITTRANS and MAXTRANS.

    In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated in 10g and higher. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

    Waits due to rows being covered by the same BITMAP index fragment

    Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If 2 sessions wish to update rows covered by the same bitmap index fragment then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.

    –Ses#1:
    CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );

    –Ses#1:
    update tx_eg set sex=’FEMALE’ where num=3;

    –Ses#2:
    update tx_eg set sex=’FEMALE’ where num=4;

    –DBA:
    select SID,TYPE,ID1,ID2,LMODE,REQUEST
    from v$lock where type=’TX’;

    SID TY ID1 ID2 LMODE REQUEST
    ———- — ———- ———- ———- ———-
    8 TX 262151 62 6 0
    10 TX 327680 60 6 0
    10 TX 262151 62 0 4

    –This shows SID 10 is waiting for the TX lock held by SID 8
    –and it wants the lock in share mode (as REQUEST=4).

    –Ses#1:
    commit;

    –Ses#2:
    commit;

    Other Scenarios

    There are other wait scenarios which can result in a SHARE mode wait for a TX lock but these are rare compared to the examples given above.

    Example:
    If a session wants to read a row locked by a transaction in a PREPARED state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4).
    As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt state very soon after the prepare this is not generally noticeable..