RI drop performance gains issue

[Aaron]

In a test environment we have dropped over 100 RI restrictions which showed
a huge instant performance gain where some jobs were running with 2 or 3
times the performance than previously.

The problem is that we have now dropped the same restrictions in the
production environment but have seen absolutely no performance difference
since the drop of RI.

Any idea's why we have not seen the expected performance improvement? We
have subsequently run Runstats and rebinds out of desperation but still no
improvement.

Thanks,

Aaron
----------

[klh]

Aaron,

Is it possible that the Production environment is in bad need of a
REORG? If you have a heavily hit application, it may be that the test
environment doesn't have the same amount of fragmentation that
production does. The runstats may then be forcing the application to
do tablespace scans.

Other things to look at may be workload on the server. Is the
production server a lot busier than test? It may be that production
is just overloaded. Also do the servers have the same processing
power?

Are the same indexes defined on production as in test? Never hurts to
check.

It's probably easiest to debug if you can isolate one sql statement
and run an explain on it in both environments.

HTH,
klh

"Aaron" <aa*****************@hotmail.com> wrote in message news:<OJ***************@newsfe5-gui.ntli.net>...

In a test environment we have dropped over 100 RI restrictions which showed
a huge instant performance gain where some jobs were running with 2 or 3
times the performance than previously.

The problem is that we have now dropped the same restrictions in the
production environment but have seen absolutely no performance difference
since the drop of RI.

Any idea's why we have not seen the expected performance improvement? We
have subsequently run Runstats and rebinds out of desperation but still no
improvement.

Thanks,

Aaron
----------

[robert]

"Aaron" <aa*****************@hotmail.com> wrote in message news:<OJ***************@newsfe5-gui.ntli.net>...

In a test environment we have dropped over 100 RI restrictions which showed
a huge instant performance gain where some jobs were running with 2 or 3
times the performance than previously.

The problem is that we have now dropped the same restrictions in the
production environment but have seen absolutely no performance difference
since the drop of RI.

Any idea's why we have not seen the expected performance improvement? We
have subsequently run Runstats and rebinds out of desperation but still no
improvement.

Thanks,

Aaron
----------

the obvious answer: the Test environment has so little data that it is
fully buffered. in such a situation, RI processing could be a signficant
percentage of total; the Production environment is not so lucky, and
time spent in physical I/O is overwhelming the RI processing reduction.
IOW, you've saved 10% of 10% of the total cost of processing in Production,
10% of 90% in Test (numbers for illustration only).

[Aaron]

To a degree you will be right in that the Prod environment is definitely
more fragmented than test, but I would have expected to see some
improvement, say 20% or something small but measurable.

Production is busier but it has more engines available also I have run tests
at all different times of the day with the very similar results.

All the indexes are the same.

That's a good idea about trying an explain on a single SQL statement, I'll
see if I can find a good candidate to try that on.

Aaron

"klh" <kh******@yahoo.com> wrote in message
news:a1*************************@posting.google.co m...

Aaron,

Is it possible that the Production environment is in bad need of a
REORG? If you have a heavily hit application, it may be that the test
environment doesn't have the same amount of fragmentation that
production does. The runstats may then be forcing the application to
do tablespace scans.

Other things to look at may be workload on the server. Is the
production server a lot busier than test? It may be that production
is just overloaded. Also do the servers have the same processing
power?

Are the same indexes defined on production as in test? Never hurts to
check.

It's probably easiest to debug if you can isolate one sql statement
and run an explain on it in both environments.

HTH,
klh

"Aaron" <aa*****************@hotmail.com> wrote in message

news:<OJ***************@newsfe5-gui.ntli.net>...

In a test environment we have dropped over 100 RI restrictions which showed a huge instant performance gain where some jobs were running with 2 or 3
times the performance than previously.

The problem is that we have now dropped the same restrictions in the
production environment but have seen absolutely no performance difference since the drop of RI.

Any idea's why we have not seen the expected performance improvement? We have subsequently run Runstats and rebinds out of desperation but still no improvement.

Thanks,

Aaron
----------

[Aaron]

Unfortunately this test environment is a full sized clone of Production used
for testing Prod fixes so is pretty much an exact copy of Production.

Aaron

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

"Aaron" <aa*****************@hotmail.com> wrote in message news:<OJ***************@newsfe5-gui.ntli.net>...

In a test environment we have dropped over 100 RI restrictions which showed a huge instant performance gain where some jobs were running with 2 or 3
times the performance than previously.

The problem is that we have now dropped the same restrictions in the
production environment but have seen absolutely no performance difference since the drop of RI.

Any idea's why we have not seen the expected performance improvement? We have subsequently run Runstats and rebinds out of desperation but still no improvement.

Thanks,

Aaron
----------

the obvious answer: the Test environment has so little data that it is
fully buffered. in such a situation, RI processing could be a signficant
percentage of total; the Production environment is not so lucky, and
time spent in physical I/O is overwhelming the RI processing reduction.
IOW, you've saved 10% of 10% of the total cost of processing in

Production, 10% of 90% in Test (numbers for illustration only).