Re: need help about Oracle architecture decision ...

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 21 Sep 2004 17:55:16 -0700
Message-ID: <1095814589.445251@yasure>

Comments in-line.

Giovanni Azua wrote:

> Hello Daniel,
>
> First of all, thank you very much for your concern and
> will to help!
>
> Please find my comments bellow:
>
>

>>I'm inclined to agree with your conclusion but not how you got there.
>>

>
> This is precisely what I wanted to know. I had my strong ideas
> right against including some alien C/C++ sub-programs just
> because of performance.
>
>

>>The biggest problem from where I'm sitting is that no one has invested
>>the time necessary to determine why you have the performance issue in
>>the first place.
>>

>
> I explained somehow in the scenario, I have an schema with a
> very complex ETL process inplace that would break if the schema
> is changed. If I were able convince my company ... as I already
> did :-) I will immediately modify the schema to include in a
> single appropriate table a "computed field" or redundant mirrored
> copy of all fields used for searching, so I would not need to
> join 8/9 tables in order to get what I need.

Think Materialized View.

> Additionally I have very expensive multiple contains clauses
> in my query, this I will change as soon as I understand how I
> can use multi-column context indexes to fulfill my needs which
> is being able to weight each token differently according of
> whether it occurs in one tag or the other (first or last).

All the C/C++ in the world will not speed this up unless you want to pay them to reinvent the existing wheel: Your choice.

>>Oracle Text is very CPU intensive what is your CPU utilization?
>>

>
> I think that 300 to 10000 milliseconds is quite an efficient usage
> of the CPU for having to achieve 2*(#tokens) contains searches combined
> with more than 8 table joins. The SQL generated has good performance
> the problem is having to look for the search and output fields in too
> many different places.

Milliseconds is not a measure of CPU utilization. You need to get someone involved in this that can dianose hardware utilization.

>>C/C++ is not going to speed up I/O ... where is the time being spent?
>>

> This is also my idea, even more, I would like to find out what
> the optmizer reaction is when there are SQL function calls to
> C/C++ external functions.

You are wandering all over the map. You have nothing to investigate until you know what and where the problem resides: And you don't.

>>You've provided no version information so it may be that you have an
>>older version but assuming 9i or 10g have you tried native compilation?
>>

>
> I am using 9.2.0.5+ including some emergency OPatch updates to some
> Oracle Text bugs I found.
>
>

>>What operating system? I took an Oracle Text app on Windows that was
>>performing poorly and got it up to decent performance just by swapping
>>to Linux.
>>

> Windows W2K and a powerful Solaris SPARC 64 with very efficient I/O
> (disk array row devices configuration).

Which SPARC 64 chip and how many? I have this ugly feeling that what you are calling "powerful" I would call a boat anchor.

>>But ... both you and your management ... are in no position to determine
>>the best path if all you know is what you posted here. C/C++ might help
>>but my guess is only in wasting money. If you don't have the expertise
>>necessary to analyze the root cause hire an outside consultant that can:
>>Perhaps Jonathan Lewis.
>>

>
> While you are right I just wanted to find out if my arguments against
> using C/C++ with Oracle under this scenario would be completely OK.

You haven't got an argument ... you have a prejudice. You need to stop what you are doing, which appears to be a waste of time, and focus on identifying the underlying issue. Only then can a recommendation be made as to which way to turn.

> Once more I thank you very much for your support.
>
> Best Regards,
> Giovanni

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue Sep 21 2004 - 19:55:16 CDT