Re: Performance (CPU) issue: translating binary data (stored as raw) into friendly types

Hi,
 you might want to look into using the parallel query option with  parallel degree 2 or 4.

 If you don't have the license, it might be possible to split the single large query into multiple queries by some attribute.

 Also, it might be worth playing with the purity declarations of your function,
 declaring it as deterministic etc. (if it's not deterministic, how could the result ever be cached ? )
 There used to be pragmas RNDS, RNPS in oracle 8.0 for a similiar purpose,
 I am not sure whether they still apply in 9.2

 Then, you could try native compilation of your pl/sql code so you don't have the pl/sql interpreter in between.

Karsten

jetlag11235_at_yahoo.com (Jetlag) wrote in message news:<2eeed00e.0211220601.2fac30f5_at_posting.google.com>...
> Environment: Two identically configured systems. Oracle 9.2, Debian
> (woody), 1G RAM, 3x72G SCSI drives, two processors (SMP supported by
> OS).
>
> Problem: I collect binary data in RAW(1024) records at the rate of
> approximately 100M per month. Every two weeks, I decode this data
> from the storage server to the analysis server. This process
> currently takes approximately 1 hour per million rows.
>
> My Solution: One enormous query is run from SQL using a dblink. It
> uses eight CASE statements and calls two PL/SQL functions about 30
> times per record. The PL/SQL functions are optimized versions of
> RAW_TO_DEC and RAW_TO_FLOAT. One processor is consistently maxed out
> at 99% usage. The other is idle. I/O is not an issue.
>
> Thoughts:
> A) Should I be using an external procedure in C, probably better
> equipped to deal with binary data? (I avoided this due to my
> inexperience with C, and my hope to keep everything native to Oracle.)
> B) Is there a way to parallelize the evaluation of PL/SQL functions
> within a single SQL query?
> C) If I call function_foo(my_var), does the answer remain cached in
> case the same function call is made again? The function is static in
> the sense the same answer is guaranteed for the same input.
>
> Thanks for any help.
>
> -- jetlag --
Received on Fri Nov 29 2002 - 08:38:05 CST