Speeding Intermedia Query

[Angel Faus]

Hi all,

We've got an Oracle database in a Web application. Intermedia Text
queries are an important part of the usage and work fine.

The problem is that in order to generate a certain web page, we need
to perform a great number of intermedia queries (like 14 o 15). This
is part of the bussiness requirements and we cannot change it. So,
even if each individual query is not that slow (from 1 to 3 seconds)
the accumulation of them is quite fatal.

My question is: what steps should be done to speed up the application?

We've believe the application (sqls, etc...) is well designed. We
cache everything that is cachable (query results, etc..). Our hardware
is state-of-art Intel (dual processor, 1Gb ram, good hard disks).

The size of the indexed data is about 20Gb.

Apparently the bottlneck is IO (since CPU is not used 100%, and the
network is not the problem). We have thought in adding more RAM, but
since its a 32 bit server, the maximum would be 2Gb, and we are not
sure whether this would make any difference.

On the other hand we could buy more disks and try to spread the IO
access evenly, but this is also a limited option.

So, what can be done when the application is not fast enough??

Thanks,

-angel

[Jim Kennedy]

"Angel Faus" <af***@corp.vlex.com> wrote in message
news:7a*************************@posting.google.co m...

Hi all,

We've got an Oracle database in a Web application. Intermedia Text
queries are an important part of the usage and work fine.

The problem is that in order to generate a certain web page, we need
to perform a great number of intermedia queries (like 14 o 15). This
is part of the bussiness requirements and we cannot change it. So,
even if each individual query is not that slow (from 1 to 3 seconds)
the accumulation of them is quite fatal.

My question is: what steps should be done to speed up the application?

We've believe the application (sqls, etc...) is well designed. We
cache everything that is cachable (query results, etc..). Our hardware
is state-of-art Intel (dual processor, 1Gb ram, good hard disks).

The size of the indexed data is about 20Gb.

Apparently the bottlneck is IO (since CPU is not used 100%, and the
network is not the problem). We have thought in adding more RAM, but
since its a 32 bit server, the maximum would be 2Gb, and we are not
sure whether this would make any difference.

On the other hand we could buy more disks and try to spread the IO
access evenly, but this is also a limited option.

So, what can be done when the application is not fast enough??

Thanks,

-angel

So you can't change the application which infers that the application is
some sort of vender application. If so then call the vendor and bitch. Get
Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines and
see if there is something there. Additionally, I would call up the vendor
and complain. Poorly written application code is over 90% of the time the
reason the application performance and scalability are in the toilet. If
you can prove it then so much the better. (do they use bind variables, do
they parse once and execute many times, etc.)

You need to find your bottle neck (statspack is a good tool to help with
this) and then attach the problem that way. There is no way we can
reasonably tell you how to speed things up without a lot of detailed
information.
Jim

[Frank]

Jim Kennedy wrote:

"Angel Faus" <af***@corp.vlex.com> wrote in message
news:7a*************************@posting.google.co m...
<snip!>
So you can't change the application which infers that the application is
some sort of vender application. If so then call the vendor and bitch. Get
Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines and
see if there is something there. Additionally, I would call up the vendor
and complain. Poorly written application code is over 90% of the time the
reason the application performance and scalability are in the toilet. If
you can prove it then so much the better. (do they use bind variables, do
they parse once and execute many times, etc.)

Hear, hear! Wholeheartedly agree
--
Regards, Frank van Bortel

[Angel Faus]

Hi,

First of all, thanks for answering.

So you can't change the application which infers that the application is
some sort of vender application.
I can change the application, but my application _needs_ to perform a
dozen intermedia queries to create a single web page. This is an
intrinsic bussines requirement, not a side-effect of poor
implementation.
Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines and
see if there is something there. Additionally, I would call up the vendor
and complain. Poorly written application code is over 90% of the time the
reason the application performance and scalability are in the toilet. If
you can prove it then so much the better. (do they use bind variables, do
they parse once and execute many times, etc.)
Yep. But I did say that, as far as I know, the application code is
well implemented. We do use bind variables, whenever we can we parse
once and execute many times, etc.
You need to find your bottle neck (statspack is a good tool to help with
this) and then attach the problem that way. There is no way we can
reasonably tell you how to speed things up without a lot of detailed
information.

I have used statspack, and tkprof, and everything, and the conclusion
is that the bottleneck is, simply put, that oracle _has_ to do a lot
of work to do what I am asking it to do.

From you answers I gather that I did not correctly formulate the
question.

Let me try again: supposing there are no big mistakes in the
application, and supposing that we believe that there is a IO
bottleneck, what is the most efficient hardware answer to this?

I can imagine some of them:

- Adding more disks and stripping the data between them.
- Moving to a 64 bits server with insane amounts of RAM.
- Spending huge amounts of dolars to get a RAC.

But I do not honestly know their effectivity. What is the usual way of
improving IO?

Any advice from the wiser ones would be appreciated. I promise to keep
working in the application side of the question.

A final note: we are talking about reducing response time, not getting
more throughput.

-angel

[Jim Kennedy]

"Angel Faus" <af***@corp.vlex.com> wrote in message
news:7a***********************@posting.google.com. ..

Hi,

First of all, thanks for answering.

So you can't change the application which infers that the application is
some sort of vender application.

I can change the application, but my application _needs_ to perform a
dozen intermedia queries to create a single web page. This is an
intrinsic bussines requirement, not a side-effect of poor
implementation.

Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines and see if there is something there. Additionally, I would call up the vendor and complain. Poorly written application code is over 90% of the time the reason the application performance and scalability are in the toilet. If you can prove it then so much the better. (do they use bind variables, do they parse once and execute many times, etc.)

Yep. But I did say that, as far as I know, the application code is
well implemented. We do use bind variables, whenever we can we parse
once and execute many times, etc.

You need to find your bottle neck (statspack is a good tool to help with
this) and then attach the problem that way. There is no way we can
reasonably tell you how to speed things up without a lot of detailed
information.

I have used statspack, and tkprof, and everything, and the conclusion
is that the bottleneck is, simply put, that oracle _has_ to do a lot
of work to do what I am asking it to do.

From you answers I gather that I did not correctly formulate the
question.

Let me try again: supposing there are no big mistakes in the
application, and supposing that we believe that there is a IO
bottleneck, what is the most efficient hardware answer to this?

I can imagine some of them:

- Adding more disks and stripping the data between them.
- Moving to a 64 bits server with insane amounts of RAM.
- Spending huge amounts of dolars to get a RAC.

But I do not honestly know their effectivity. What is the usual way of
improving IO?

Any advice from the wiser ones would be appreciated. I promise to keep
working in the application side of the question.

A final note: we are talking about reducing response time, not getting
more throughput.

-angel

If you already know what the bottleneck is (and there is always a
bottleneck) then reduce it. If the problem is that you need more IO then
you have to find a way to increase more IO. That may mean multiple
controllers and multiple disk drives . It depends upon where the bottleneck
is.

Jim

[Angel Faus]

Hi,

First of all, thanks for answering.

So you can't change the application which infers that the application is
some sort of vender application.

I can change the application, but my application _needs_ to perform a
dozen intermedia queries to create a single web page. This is an
intrinsic bussines requirement, not a side-effect of poor
implementation.

Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines and
see if there is something there. Additionally, I would call up the vendor
and complain. Poorly written application code is over 90% of the time the
reason the application performance and scalability are in the toilet. If
you can prove it then so much the better. (do they use bind variables, do
they parse once and execute many times, etc.)

Yep. But I did say that, as far as I know, the application code is
well implemented. We do use bind variables, whenever we can we parse
once and execute many times, etc.

You need to find your bottle neck (statspack is a good tool to help with
this) and then attach the problem that way. There is no way we can
reasonably tell you how to speed things up without a lot of detailed
information.

I have used statspack, and tkprof, and everything, and the conclusion
is that the bottleneck is, simply put, that oracle _has_ to do a lot
of work to do what I am asking it to do.

From you answers I gather that I did not correctly formulate the
question.

Let me try again: supposing there are no big mistakes in the
application, and supposing that we believe that there is a IO
bottleneck, what is the most efficient hardware answer to this?

I can imagine some of them:

- Adding more disks and stripping the data between them.
- Moving to a 64 bits server with insane amounts of RAM.
- Spending huge amounts of dolars to get a RAC.

But I do not honestly know their effectivity. What is the usual way of
improving IO?

Any advice from the wiser ones would be appreciated. I promise to keep
working in the application side of the question.

A final note: we are talking about reducing response time, not getting
more throughput.

-angel

[Jim Kennedy]

"Angel Faus" <af***@corp.vlex.comwrote in message
news:7a***********************@posting.google.com. ..

Hi,

First of all, thanks for answering.

So you can't change the application which infers that the application is
some sort of vender application.

I can change the application, but my application _needs_ to perform a
dozen intermedia queries to create a single web page. This is an
intrinsic bussines requirement, not a side-effect of poor
implementation.

Tom Kyte's book Expert 1 on 1 Oracle. I would look up stored outlines

and

see if there is something there. Additionally, I would call up the

vendor

and complain. Poorly written application code is over 90% of the time

the

reason the application performance and scalability are in the toilet.

If

you can prove it then so much the better. (do they use bind variables,

do

they parse once and execute many times, etc.)

Yep. But I did say that, as far as I know, the application code is
well implemented. We do use bind variables, whenever we can we parse
once and execute many times, etc.

You need to find your bottle neck (statspack is a good tool to help with
this) and then attach the problem that way. There is no way we can
reasonably tell you how to speed things up without a lot of detailed
information.

I have used statspack, and tkprof, and everything, and the conclusion
is that the bottleneck is, simply put, that oracle _has_ to do a lot
of work to do what I am asking it to do.

From you answers I gather that I did not correctly formulate the
question.

Let me try again: supposing there are no big mistakes in the
application, and supposing that we believe that there is a IO
bottleneck, what is the most efficient hardware answer to this?

I can imagine some of them:

- Adding more disks and stripping the data between them.
- Moving to a 64 bits server with insane amounts of RAM.
- Spending huge amounts of dolars to get a RAC.

But I do not honestly know their effectivity. What is the usual way of
improving IO?

Any advice from the wiser ones would be appreciated. I promise to keep
working in the application side of the question.

A final note: we are talking about reducing response time, not getting
more throughput.

-angel

If you already know what the bottleneck is (and there is always a
bottleneck) then reduce it. If the problem is that you need more IO then
you have to find a way to increase more IO. That may mean multiple
controllers and multiple disk drives . It depends upon where the bottleneck
is.

Jim