If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > mutliple index reads on an insert

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-18-09, 09:35
span3d span3d is offline
Registered User
 
Join Date: Oct 2005
Posts: 4
mutliple index reads on an insert

Can someone explain why an insert cause multiple logical index reads?
Our application does bulk inserts and insert of each rows caused about 20-30 index reads, if the reads are physical we have a slow insert. The table has 4 indexes , 3 unique and 1 non-unique ( no clustered indexes). We are having random insert spike because of index reads.
Reply With Quote
  #2 (permalink)  
Old 03-18-09, 09:50
span3d span3d is offline
Registered User
 
Join Date: Oct 2005
Posts: 4
so on an avg bulk insert of 200 rows causes about 4000 index reads, each index is isolated to its own tablespace (table partitioning) each index gets about 1000 reads.
Reply With Quote
  #3 (permalink)  
Old 03-18-09, 10:05
n_i n_i is offline
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
Do you think that referential constraints might be somehow involved?
Reply With Quote
  #4 (permalink)  
Old 03-18-09, 10:07
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
Obviously it has to insert the data into the index, and it has to traverse the b-tree to find the correct page to update, so this could account for several reads each time. If there is no room on the correct index page for the new data, then a page-split of the index page is necessary, which can be costly and require additional index reads.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old 03-18-09, 10:57
span3d span3d is offline
Registered User
 
Join Date: Oct 2005
Posts: 4
There is no referential constraints on this table.

Marcus,
How can i monitor for page-splits?
Here is from db2pd:
IIDEmpPgDel BndrySplts PsEmPg Scans KeyUpdates NonBndSpts PgAllocs PseuDels DelClean IntNodSpl
4 79 0 82 588287 550865 32767 33367 550865 123563 723
3 21 0 24 300991 550209 28283 29485 550209 474021 1245
2 147 3915 151 12 550209 25537 29569 550209 56946 342
1 20 0 23 100 550209 28095 29275 550209 436091 1222

RootSplits=0
InclUpdats=0
Merges=0
What can we do to iptimize this?
Reply With Quote
  #6 (permalink)  
Old 03-19-09, 03:37
Peter.Vanroose Peter.Vanroose is offline
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
Quote:
Originally Posted by span3d
Our application does bulk inserts
In that case it could make sense to postpone the index updates to after the bulk of inserts. This is what the LOAD utility does (as opposed to the SQL INSERT).
Maybe that's an option?
(If on LUW, also have a look at the INDEXING MODE option of LOAD.)
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #7 (permalink)  
Old 03-19-09, 08:25
span3d span3d is offline
Registered User
 
Join Date: Oct 2005
Posts: 4
load is not an option for us, the table is updated/read constantly...
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On