RE: Anyone seen any independent performance .....

[Cary Millsap]

Title: RE: Anyone seen any independent performance .....

There’s actually more to it than that. For every block in Oracle that changes, there’s:   1. Generation of redo on the upcoming undo block change 2. An undo (i.e., rollback segment) block change 3. Generation of redo on the upcoming data block change 4. A data block change   These four steps happen for every index and data block change. So, inserting into a table with n indexes will motivate on the order of (1+n)*2 units of redo to be written to your redo stream, and (1+n)*(1+1) blocks of undo and data to be written through your DBWR (actually more, because index leaf node insertions sometimes generate recursive SQL that propagates changes upward in the B*-tree, each block of which also generates redo and undo).   Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9–12 Dallas - Next event: Miracle Database Forum, Sep 20–22 Middlefart Denmark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Steve Perry Sent: Thursday, September 05, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject: Re: Anyone seen any independent performance .....   (1) one for the table insert. + (3) 1 for the header block, 1 for the branch block and 1 for the leaf block * number of indexes   I think Dave Ensor mentioned something like that in a presentation.   ----- Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, September 05, 2002 1:21 PM Subject: RE: Anyone seen any independent performance .....   I don't remember where but the results of the study were like following ... if the cost of inserting one row to a table is 1 unit and if you have 5 indexes on the table than total cost of inserting 1 row to the table is      1   (cost of inserting a row)  + 3*5 (5 indexes) ---------    16   So total cost is about 16 units. Raj ______________________________________________________ Rajendra Jamadagni              MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!   -----Original Message----- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 2:08 PM To: Multiple recipients of list ORACLE-L Subject: Anyone seen any independent performance .....   studies on what happens to performance as indexes keep getting added to tables while inserting data ??