Re: Re: How to improve performance using Oracle Spatial

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 1 Jan 2009 09:03:10 +0100
Message-ID: <1163.1230796990_at_roughsea.com>

Eriovaldo,

    If your process is written in PL/SQL, don't forget the DBMS_PROFILER package. I suspect that your problem, like so often, comes from the algorithm.

SF



On Thu 1/01/09 02:23 , "Eriovaldo Andrietta" ecandrietta_at_gmail.com sent:

Thanks,

 I agree with the way,  I will consider your comments.
 My goal asking for helping is find out any special way to analyze performance when using Oracle Spatial.
 the process that I am running is a PL/SQL with geometry operations.
 It is taking a long time, I am talking about hours.
 I guess that I need to work with a short database in order to identify the critical point, via trace.

 If you have any other suggestion, it will be welcome.

Best Regards
Eriovaldo

On Wed, Dec 31, 2008 at 5:12 PM, Jared Still <jkstill_at_gmail.com> wrote:

On Wed, Dec 31, 2008 at 8:45 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

I need increase performance in my aplication.
It is using Oracle Spatial.
Could someone help me ?
All basic resources were applied.

I am talking about a big database with 100.000 lines with geometry datatype.
Using sdo_relate, sdo_join and others.
I think that database parameters must be reviews, Which ones ? PGA, SGA
Is there any in special ?

Wow, that's a pretty tall order.

If performance is a problem, you will first need to determine exactly which part
of the application is performing too poorly.

The application users can tell you what parts are slow.

They can also prioritize for you so that you work on the most important parts first.

Then you need to determine where the bottlenecks are.

From your email it seems that the assumption is that that database is at fault.

That may or may not be the case.

If transaction is taking 10 seconds, and it should take less than 1 second, you
have to find out where the time is being spent.

Then you need to pick the section(s) of the transaction that consume the most
time, and determine what can be done to make them perform better.

It's probably not a good idea to start tweaking database parameters in hopes
that one may fix the problem.

There are no silver bullets.

That should give you a place to start.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 01 2009 - 02:03:10 CST