FAQ
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.

Search Discussions

  • Eagle Fan at Jan 18, 2012 at 1:28 pm
    At first, I suspect it's related to overflow segment, but seems it's not.
    I created same small IOT tables on both databases and run the secondary
    index creation sql again.

    On 10203 database, it used parallel index fast scan read but on 11202
    database, it still used single thread db file sequential read.

    Thanks.
    On Wed, Jan 18, 2012 at 9:22 PM, Eagle Fan wrote:

    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


    --
    Eagle Fan (www.dbafan.com)


    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jan 21, 2012 at 3:33 pm
    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@gmail.com>
    To: <oracle-l@freelists.org>
    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


    --
    http://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


    --
    http://www.freelists.org/webpage/oracle-l
  • Eagle Fan at Jan 23, 2012 at 5:10 am
    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 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@gmail.com>
    To: <oracle-l@freelists.org>
    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


    --
    http://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


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Eagle Fan (www.dbafan.com)


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 18, '12 at 1:23p
activeJan 23, '12 at 5:10a
posts4
users2
websiteoracle.com

2 users in discussion

Eagle Fan: 3 posts Jonathan Lewis: 1 post

People

Translate

site design / logo © 2022 Grokbase