0

I have a recently released 10 000 user analytical application that is suffering from performance issues because of excessive amount of transactions. Apart from re-writing many of the SQLs in the application for better performance we are also on a top down approach where we are adding more volumes and tweeks to memory etc. An Oracle specialist redesigned our server from single tablespace with 2 volumes to 2 tablespaces with 4 volumes where the indexes will have their own volume and tablespace. Each volume is a separate disk array so they do not compete for IO.

We have executed this change in development environment and we are about to move into acceptance but before paying for the change the business would like to see measurable gains.

Where in the AWR report can I make comparisons to measure such data on the before and after the change is executed?

APC
  • 144,005
  • 19
  • 170
  • 281
  • Hindsight is a marvellous thing, but shouldn't you have thought about measuring performance improvement *before* you started work on this exercise? Seriously. You needed to agree some targets with the business a long time ago. – APC Nov 04 '12 at 21:40
  • Apart from anything else, if you don't have some benchmarks which quantify the current problem how do you know these changes have addressed the real issues? You don't. Obviously, I hope for your sake they do, but really you've just been grimacing and guessing, which is not a satisfactory tuning strategy. Have you been running *any* benchmarks? AWR? Statspack? – APC Nov 04 '12 at 21:42
  • @APC Yes I have AWR for weekly daily(8am-6pm) and hourly with well document load scenarios for before and after the the change was made in development. The reason I made this post is that I do not know what section of the report is the correct section off the changes that was recommended. –  Nov 05 '12 at 09:53

1 Answers1

0

The AWR has this section called IO Stats. Not unreasonably, this is where it reports the statistics for IO activity. Within that section there are figures for each tablespace. You need to run these reports against the target database. Ideally you want several different runs, each run being no more than twenty minutes long; the longer the run, the more likely it is that the law of averages will drain meaning from the figures you get.

So, the principle is: you run this report and get a benchmark. Then you deploy your changes and re-run the report. The difference in the various columns is the performance benefit of the change. Really you would want to requests and data per sec go up, average time and waits go down.

To make these figure more convincing you should run the same workload in the before and after tests. This is where something like Real Application Testing really comes into its own.

APC
  • 144,005
  • 19
  • 170
  • 281