467,161 Members | 889 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Speeding Intermedia Query

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
Jul 19 '05 #1
  • viewed: 1999
Share:
6 Replies
"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
Jul 19 '05 #2
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

Jul 19 '05 #3
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
Jul 19 '05 #4
"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
Jul 19 '05 #5
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
Jun 27 '08 #6
"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
Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bennett Haselton | last post: by
3 posts views Thread by David | last post: by
3 posts views Thread by John D | last post: by
9 posts views Thread by mfyahya@gmail.com | last post: by
2 posts views Thread by Wayne | last post: by
2 posts views Thread by simon_w3@ntlworld.com | last post: by
11 posts views Thread by Dan Sugalski | last post: by
reply views Thread by Frank | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.