Re: IOT secondary index creation can't use parallel (30hours)
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Mon, 23 Jan 2012 13:09:32 +0800
Hi Jonathan:
Thanks for your testing.
It did run successfully with online & parallel on my 10203 version
database. The table has a lot of DML sessions and during that 20 minutes,
I didn't see blocked sessions.
So as you said it could be some bug in 10203 version for IOT secondary
parallel online index creation (but I didn't hit the bug on my database)
and oracle implicitly disable it in 11g version. But it takes very long
time for secondary index build.
What's the ORA-600 error did you get?
I have opened a tar for this but didn't get conclusion yet.
Thanks.
On Sat, Jan 21, 2012 at 11:32 PM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
>
> 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
>
>
>
--
Eagle Fan (www.dbafan.com)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
