Recently, the IBM RISC 6000 machine where our database is lying was upgraded to eight processors from four and RAM was upgraded to 6 GB from 4 GB. We are using Oracle 22.214.171.124 database. Now, one of the managers here is asking me for the response time in the database level. How can I get the response time? Please note that I collect statistics using Statspack.
Statspack can show you certain statistics for a given time period and trends over time for many, many performance elements of your database. But... response time isn't really one of them. Since you've already been collecting Statspack statistics, you're probably already familiar with how it can allow you to monitor instance load, I/O levels, wait events and even high-cost SQL. But, if you want to know about reponse time you really needed to know what response time was for some key application elements before the hardware upgrades and then be able to check those same elements now after the upgrades. Without the before, all you can do is say what you see now and you can't then determine a percentage of improvement.
What you may want to do is to look in the SQL Statements summary section of your Statspack report and see which queries it thinks are the highest cost. You could then take those queries and run them "stand-alone" with timing turned on and turn on tracing so that you can get detailed information about what the query is doing. If you see queries with high LIOs (logical I/O...buffer gets in the Statspack report), there might be opportunities to tune the query to run more efficiently.
I'll suggest a great book if you want to really learn how to exploit Statspack: Don Burleson's "High Performance Tuning with Statspack" from Oracle Press.
But, unless you have a pre-upgrade response time baseline that you can now compare with, you're only going to be able to say what response time is now. You won't have a way to compare and thus can't really answer your manager's question. I'd advise that you set up a set of queries that are the "heavy hitters" in your database and maybe use Statspack to watch for them showing up in the report. Or periodically execute those queries with tracing on and see how they're performing. That way, you'll start to build a baseline which you can use for future comparisons.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.