Did you analyze the table and indexes after the index build? Also what is
the difference in the explain plan?
Jim
"E Elliott" <emelliott_99_at_yahoo.com> wrote in message
news:9c4485cf.0106200724.3a57e40e_at_posting.google.com...
> We are seeing drastic slowdowns in our system after adding 2 indexes
> to the largest table in our DB.
>
> Let me give you some background:
> We develop Accounting Software that can use either SQL Server (ugh..I
> know!) or Oracle as its DB backend. As we put out new releases, we
> sometimes need to "Migrate" the database. In our case to migrate
> means to add new columns to a table, add a new table, add new indexes,
> etc.
>
> In our most recent release one of our changes is to add 2 indexes to 2
> separate columns on our biggest table. In a brand new build of the
> new version, everything flies -- even with millions of rows. But,
> when we apply the changes via our migration tools, a 2 minute
> process(generating a report) turns into 45 minutes(the table has 18
> million rows). When we remove the two new indexes, all returns to
> normal. We have done everything from running new stats to exporting
> and importing to remove fragmentation. We still are not seeing any
> imporvement. In total this table has 11 indexes. While, we know this
> slows down inserts and updates, we can live with that since most
> iserting and updating is done in overnight batch jobs. The indexes
> can be justfied as they really speed up our reporting and searching
> functions.
>
> We just can't figure out why adding the 2 new indexes to an existing
> DB would cause SO much slow down - even after we did exp/imp to get
> rid of fragmentation. I know there will be some slow down with new
> indexes, but this seems extreme.
>
> What else can we look at? What does Oracle do with those indexes that
> causes such a drastic slow down? We would like to try to track down a
> cause so we have explanations for our customers or to see if we need
> to change our migration process. Of course, all I hear is that the
> slowdown doesn't happen in SQL Server, so what is Oracle doing? I'm
> having a hard time explaining!
>
> We're testing on: Oracle 8.1.6 on NT.
>
> I would appreciate any insight or other things to try. Let me know if
> I need to post any additional info.
>
> Thanks,
>
> Elizabeth Elliott
Received on Wed Jun 20 2001 - 10:39:42 CDT
