Re: need help about Oracle architecture decision ...

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.

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).

> 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.

> 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'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).

> 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.

Once more I thank you very much for your support.

Best Regards,
Giovanni Received on Tue Sep 21 2004 - 08:05:10 CDT