leaf node splits
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Jan 2012 21:20:19 +0000
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
