Re: analyzing db for performance bottlenecks

Start using utlbstat and utlestat first before getting to buy any tools. It's free and it comes with Oracle.
Just run utlbstat at one point in time, and utlestat 4 to 8 hours later. Don't forget to enable timed_statistics, before you do this.

However, I already have a feeling you incorrectly blame the database. Here's why:
Question:
Are you using bind variables?
My experience is many java/jdbc programmers don't use them. Doing so they will force a *hard parse* (which is very costly) and a very low statement hit-ratio at the database side.
The Oracle Enterprise Edition is capable of translating all those hardcoded literals into bind variables, the Oracle Server Edition isn't. You don't specify which edition you are using, but it wouldn't surprise me if this is the problem.
In that case, You are vexing your cpu too much by sending all those unnecessary parse requests.

Hth,

Sybrand Bakker, Oracle DBA

<jmanju_at_my-deja.com> wrote in message news:8q8du6$8ta$1_at_nnrp1.deja.com...
> Hi,
>
> We are doing performance tests on our server and it looks like the
> bottleneck is database. We are using oracle 8.1.6 on solaris or NT with
> java/corba server and the connection to database is using JDBC. On
> solaris, one java server can handle 1800 requests/minute and increasing
> the number of servers to 4 (all of them connecting to the same db)
> increases the total number of requests handled by the system. But after
> that point, increasing the number of java servers does not help. This is
> the case even if we move each server to its own machine and the db alone
> on one machine.
>
> Do you know of good tools to analyze the database to see where the
> bottlenecks are or tools to help tune a database? We have checked for
> disk contention and shared memory so far and they seem fine. Thank you,
>
> Manju.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Sep 19 2000 - 15:31:54 CDT