Re: How to improve performance using Oracle Spatial

*If* a trace file helps you identify that your dominant time consumer is an EXEC call upon a PL/SQL block that contains no SQL (or it does contain SQL, but the response times for that SQL is negligible), then you need to move into the mode of using DBMS_PROFILER upon that PL/SQL block.

To summarize what I'd recommend:

• Do what Jared said to identify what part of your task is taking the most time.
• Use Oracle extended SQL trace to measure the database part of that time (and in fact give you important information about the part of the application that communicates directly with the database).
• If your dominant time consumer is execution of PL/SQL (not SQL) codepath, then use DBMS_PROFILER to dig into the PL/SQL code line-by-line.

Cary Millsap
http://method-r.com
http://carymillsap.blogspot.com

On Wed, Dec 31, 2008 at 7:23 PM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>wrote:

> 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