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

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:

1. 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.)
2. Is there a way to parallelize the evaluation of PL/SQL functions within a single SQL query?
3. 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 --