Re: IOT secondary index creation can't use parallel (30hours)
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 21 Jan 2012 15:32:13 -0000
I just ran a quick test on 10.2.0.3 and 11.2.0.3 and found that 11.2.0.3
ran serially if I tried to do parallel and online.
The 10.2.0.3 test, with the same script, crashed with an ORA-00600 error.
(online alone was okay, parallel alone was okay)
Two ideas that may explain your observations:
a) The rebuild in 10.2.0.3 didn't run online
b) Parallel online crashes in **some** cases - so Oracle disabled the
combination in the upgrade
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Eagle Fan" <eagle.f@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, January 18, 2012 1:22 PM
Subject: IOT secondary index creation can't use parallel (30hours)
Hi:
I created IOT secondary index using parallel 4 online option on two
databases. One database is 10203 version and another one is 11202 version.
On 10203 version, the SQL finished in 20 minutes, but on 11202 version, it
used 30 hours. The two databases have same table/index structure and both
of them are about 100GB.
On 10203 database, it used PK index fast full scan with parallel 4 as
expected. But on 11202 database, it didn't use parallel index fast full
scan, it used single thread db file sequential read on PK index. That's why
it took 30 hours.
I don't know why on 11202 version, the secondary IOT index creation used
less effecient db file sequential read instead of index fast full scan.
Is there any way to make it faster?
The SQL is like this:
CREATE INDEX T_IDX2 ON T (
a,b,c)tablespace index01 parallel 4 ONLINE;
Thanks in advance.
--
Eagle Fan
--
//www.freelists.org/webpage/oracle-l
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2109/4749 - Release Date: 01/17/12
--
//www.freelists.org/webpage/oracle-l
Other related posts:
