Hi Guru,
Here is the small test case.I don't know it's strange behavior or it's obvious one.
VERSION
----------
11.2.0.1.0
First Test
===========
drop table test2 purge;
create table test2 nologging as select * from dba_objects;
insert into test2 select * from test2;
insert into test2 select * from test2;
insert into test2 select * from test2;
commit;
exec dbms_stats.gather_table_stats(user,'TEST2');
TDESAI_DBA@hadwts01-db > select count(*) from test2;
COUNT(*)
----------
7136168
drop table test1 purge;
create table test1 nologging as select * from dba_objects;
insert /*+ append */ into test1 select * from test1;
commit;
insert /*+ append */ into test1 select * from test1;
commit;
insert /*+ append */ into test1 select * from test1;
commit;
insert /*+ append */ into test1 select * from test1;
commit;
exec dbms_stats.gather_table_stats(user,'TEST1');
create index test1_idx on test1(object_id);
alter index test1_idx unusable;
select count(*) from test1;
alter system flush buffer_cache;
insert /*+ append */ into test1 select * from test2;
commit;
--Now start Tracing
alter index test1_idx rebuild;
--Stop Tracing
Second test
-----------------
Just Change
alter index test1_idx rebuild online;
Test 1
--------
alter index test1_idx rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 1 0
Execute 1 55.97 62.98 205007 205122 26278 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 55.97 62.98 205007 205125 26279 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 308
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE TEST1_IDX (cr=208067 pr=205011 pw=24993 time=0 us)(object id 0)
21408536 SORT CREATE INDEX (cr=205023 pr=205003 pw=0 time=10623558 us)
21408536 TABLE ACCESS FULL TEST1 (cr=205023 pr=205003 pw=0 time=16969608 us cost=45401 size=85634208 card=14272368)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
direct path read 2536 0.06 8.77
db file sequential read 4 0.00 0.00
Disk file operations I/O 2 0.00 0.00
control file sequential read 95 0.00 0.02
control file parallel write 69 0.00 0.03
direct path write 2 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.04 0.04
Test 2
---------
alter index test1_idx rebuild online
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 1 0
Execute 1 66.01 124.30 205809 205288 26679 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 66.01 124.31 205809 205291 26680 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 308
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE TEST1_IDX (cr=208223 pr=205805 pw=24993 time=0 us)(object id 0)
21408536 SORT CREATE INDEX (cr=205087 pr=205804 pw=0 time=10605921 us)
21408536 TABLE ACCESS FULL TEST1 (cr=205087 pr=205804 pw=0 time=77541120 us cost=45401 size=85634208 card=14272368)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 804 0.00 0.22
db file scattered read 12813 0.05 66.81
latch: object queue header operation 1 0.00 0.00
control file sequential read 95 0.00 0.02
control file parallel write 69 0.00 0.03
direct path write 2 0.00 0.00
reliable message 3 0.00 0.00
enq: RO - fast object reuse 1 0.00 0.00
enq: CR - block range reuse ckpt 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.04 0.04
That threshold is culprit ? If so then why on second case it didn't affect.