FAQ
9.2.0.8.

I have a table partitioned daily and subpartitioned by list on which there are two local indexes and two global unique indexes.

The maintenance job for this table runs at the weekend getting rid of the older partitions and adding some new ones for the future.
This maintenance is done with "UPDATE GLOBAL INDEXES"

Every day, an insert .. select statement populates the next day's partitions.

I'm investigating several aspects of a performance problem with this process but I have been distracted by what may be a tangential issue.

There is a reporting environment taken at a fixed point at end of play the day in which I can play with this process and investigate.

I can run this insert .. select, roll back the transaction, run it again, roll it back, etc.

There's no other DML being run on this reporting environment and certainly none which affects the objects affected by by insert ... select.

Whilst I've been making 10046 traces, I've also been capturing session stats from v$mystat and I'm intrigued by an observation.

Every day, the first time I run this statement with this new data I will get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 splits" then I roll it back.

Every subsequent time I repeat this statement and roll back, I insert the exact same data in the insert .. select but I get 1020 @"leaf node splits" and "1020 leaf node 90-10 splits'".

Any thoughts on the difference?

Search Discussions

  • Andy Klock at Jan 27, 2012 at 1:51 am

    On Thu, Jan 26, 2012 at 4:20 PM, Dominic Brooks wrote:
    ...

    Every day, the first time I run this statement with this new data I will
    get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 splits"
    then I roll it back.

    Every subsequent time I repeat this statement and roll back, I insert the
    exact same data in the insert .. select but I get 1020 @"leaf node splits"
    and "1020 leaf node 90-10 splits'".

    Any thoughts on the difference?
    I believe the "leaf node splits" counter is goes up even if they are "leaf
    node 90-10 splits". So, when your new data is inserted the first time 1980
    of those are 50-50 splits. When you rollback that transaction those blocks
    aren't un-split so on subsequent runs those values fall right into their
    place in the index.

    As for the 90-10, that looks like you have a right handed index and is
    splitting with every insert.

    Andy
  • Jonathan at Jan 27, 2012 at 7:20 am
    How many list subpartitions in each partition ?
    Are you using ASSM or freelist management on the tables or indexes ?
    Do any of the indexes have very repetitious key values ?
    Does the insert include a "sequence.nextval" and if so is that column indexed ?
    Is the volume of data (including indexes) large relative to the size of the cache ?



    Regards
    Jonathan Lewis

    dombrooks@hotmail.com wrote:
    9.2.0.8.

    I have a table partitioned daily and subpartitioned by list on which ther=
    e are two local indexes and two global unique indexes.


    Every day, the first time I run this statement with this new data I will =
    get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 split=
    s" then I roll it back.

    Every subsequent time I repeat this statement and roll back, I insert the=
    exact same data in the insert .. select but I get 1020 @"leaf node split=
    s" and "1020 leaf node 90-10 splits'".

    Any thoughts on the difference?

    --
    http://www.freelists.org/webpage/oracle-l
  • Dom Brooks at Jan 27, 2012 at 4:28 pm
    Perhaps what I should have asked is as index splits are recursive transactions
    unaffected by my rollback, why do I consistently see the same number of 90-10 splits in these subsequent transactions?

    How many list subpartitions in each partition ?
    3 subpartitions in each of 64 partitions
    Are you using ASSM or freelist management on the tables or indexes ? ASSM.
    Do any of the indexes have very repetitious key values ?
    Yes.

    One of these global indexes is a unique composite index with 6 columns.
    17 million rows in table.
    Column 1 - 3786 distinct values
    Column 2 - 1000862 distinct values
    Column 3 - 2 distinct values
    Column 4 - the partition date column - 64 distinct values
    Column 5 - 2 distinct values
    Column 6 - 2 distinct values

    (From an index maintenance perspective, the date column would probably be better at position 1 but
    some queries might suffer as a result - something to investigate)

    Two other non-unique local indexes - one on column 2 above; one composite on the subpartition key (140 distinct values),
    column 1, column 2.

    Does the insert include a "sequence.nextval" and if so is that column indexed ?
    Yes. Another global unique index on the primary key populated by sequence number.
    Is the volume of data (including indexes) large relative to the size of the cache ?
    Table - 3G
    Indexes - 3G
    Buffer Cache - 16G

    Cheers,
    Dominic--
    http://www.freelists.org/webpage/oracle-l
  • Andy Klock at Jan 27, 2012 at 4:34 pm
    I would think that:
    Does the insert include a "sequence.nextval" and if so is that column
    indexed ?
    Yes. Another global unique index on the primary key populated by sequence
    number.

    This would explain this phenomenon. You have have a right handed index
    that has no where to go but to the right with every subsequent run. I
    thought I covered that, but Jonathan is extremely thorough.

    Andy
    On Fri, Jan 27, 2012 at 11:27 AM, Dom Brooks wrote:

    Perhaps what I should have asked is as index splits are recursive
    transactions
    unaffected by my rollback, why do I consistently see the same number of
    90-10 splits in these subsequent transactions?

    How many list subpartitions in each partition ?
    3 subpartitions in each of 64 partitions
    Are you using ASSM or freelist management on the tables or indexes ? ASSM.
    Do any of the indexes have very repetitious key values ?
    Yes.

    One of these global indexes is a unique composite index with 6 columns.
    17 million rows in table.
    Column 1 - 3786 distinct values
    Column 2 - 1000862 distinct values
    Column 3 - 2 distinct values
    Column 4 - the partition date column - 64 distinct values
    Column 5 - 2 distinct values
    Column 6 - 2 distinct values

    (From an index maintenance perspective, the date column would probably be
    better at position 1 but
    some queries might suffer as a result - something to investigate)

    Two other non-unique local indexes - one on column 2 above; one composite
    on the subpartition key (140 distinct values),
    column 1, column 2.

    Does the insert include a "sequence.nextval" and if so is that column
    indexed ?
    Yes. Another global unique index on the primary key populated by sequence
    number.
    Is the volume of data (including indexes) large relative to the size of
    the cache ?
    Table - 3G
    Indexes - 3G
    Buffer Cache - 16G

    Cheers,
    Dominic--
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Dom Brooks at Jan 27, 2012 at 6:05 pm
    Sorry - I'm just being really dim, which possibly doesn't translate effectively in this medium.

    I've been waiting for the "Doh!"...

    I just wasn't getting the different effect between the rollback on the 50:50 splits - ie the split remains but the new data is obviously rolled back.

    Whereas with the 90:10s all the data was new data which when removed meant the blocks were empty and so ... back on the free list waiting for me to do it all again.


    Cheers,
    Dominic

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 26, '12 at 9:21p
activeJan 27, '12 at 6:05p
posts6
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase