Re: IOT secondary index creation can't use parallel (30hours)
Date: Sat, 21 Jan 2012 15:32:13 -0000
I just ran a quick test on 10.2.0.3 and 18.104.22.168 and found that 22.214.171.124 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:
- The rebuild in 10.2.0.3 didn't run online
- Parallel online crashes in **some** cases - so Oracle disabled the combination in the upgrade
- Original Message ----- From: "Eagle Fan" <eagle.f_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, January 18, 2012 1:22 PM Subject: IOT secondary index creation can't use parallel (30hours)
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.
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
http://www.freelists.org/webpage/oracle-l Received on Sat Jan 21 2012 - 09:32:13 CST