I believe Kevin gave that presentation at OpenWorld -- either last year
or the year before. His paper is available for download on the TUSC
site, as he works for TUSC.
- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> Naveen -
> He provided figures, and they are on the handout that is somewhere
> in my
> office. Maybe I'll run across it someday, or even better, maybe he'll
> publish his results. Here are a few more details that I posted to
> this list
> earlier.
> Kevin Loney (author of Oracle DBA Handbook) has performed index
> performance tests and presented a paper at our Twin Cities Oracle
> User's
> Group (http://www.tcoug.org). I don't know if his paper is on that
> site or
> if Kevin has posted it somewhere or if he will included his findings
> in a
> future book.
> His results (from memory) was that there weren't any big
> surprises. Say
> it takes 1 hour to load a table with no indexes on it. If you put 1
> index on
> that table, load time will increase about 20% to maybe 1hr 12 minutes
> (depending on how many columns are indexed, etc.). If we add a second
> index,
> load time will again increase, but by a smaller amount than for the
> first,
> maybe to 1 hr. 23 minutes. And so it goes. By the time we reach 20
> indexes,
> adding a 21st index may add only 3 or 4 minutes to our load time.
> There
> didn't seem to be any point where adding one more index would throw
> load
> times into a black hole and double load times or something like that.
> Kevin also tested whether the size of the index mattered. There
> were
> points where say, the 100,000th row caused index performance to
> suddenly
> drop, probably due to factors like adding a newer blevel. However it
> was
> almost impossible to predict this point ahead of time.
> My conclusions:
> - Dropping indexes speeds inserts.
> - If you have a single index on a table, adding a second index
> is
> costly.
> - If the table already has 20 indexes, one more isn't going to
> have a
> noticeable effect.
>
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Saturday, October 26, 2002 3:43 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Did he publish any figures? I mean, it seems common-sense that adding
> the
> first index will hurt but adding 11th index to a table won't hurt
> that much.
>
> As stephane pointed out, cost of an index is 2.5 times more than the
> cost of
> insert in a non-indexed table.
>
> So assuming cost is 1, than cost with 1 index will be 3.5 as 250%
> increase.
>
> Cost with 5 indexes should be 13.5 and cost with 6 indexes will be 16
> less
> than twenty percent increase.
>
> Since the addition cost is constant for every index added, the
> percentage
> increase in cost (and also maybe time) will be lower and lower.
>
> Am I right or missing something?
>
> Regards
> Naveen
>
> -----Original Message-----
> Sent: Friday, October 25, 2002 11:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Stephane
> You mentioned "each additional index costs about 2.5 times the
> cost of
> inserting into a non-indexed table". I just wanted to point out that
> Kevin
> Loney has done some performance tests involving the number of
> indexes. I
> don't know if he has published these anywhere. In a nutshell, the
> results
> were that a single index really hurts insert performance, and each
> additional index increases the hurt, but by a decreasing amount. The
> conclusions were:
> - If you can drop all indexes, that will really help inserts.
> - If you have one index, adding a second index will really hurt,
> but not
> as bad.
> - If the table already has 15 indexes, adding one more index
> probably
> won't be noticed.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Friday, October 25, 2002 8:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Rahul wrote:
> >
> > List,
> > i have two heavily inserted tables, the structures are same.
> > currently these tables reside on separate disks, can i increase the
> > performance
> > of inserts if i create these tables in a cluster ? as a cluster
> would
> force
> > the rows of both the tables
> > to be physically close on the disk !
> >
> > regards
> > -rahul
> >
> > Ora 7.3 on AIX
> >
>
> You would also increase contention ... I'd rather try to augment the
> number of free lists, and, if you are lucky enough not to access your
> indexes in RANGE SCAN mode, to create them as REVERSE.
> Beware of indexes, by the way, each additional index costs about 2.5
> times the cost of inserting into a non-indexed table (in terms of
> logical blocks).
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Naveen Nahata
> INET: naveen_nahata_at_mindtree.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>
=== message truncated ===
Do you Yahoo!?
Y! Web Hosting - Let the expert host your web site
http://webhosting.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun Oct 27 2002 - 15:03:20 CST
