sort + external procedure tuning

Hello,
I test a query on db2 udb v7.2 EE and Oracle8i EE. On db2 it took me less than 30s. On Orale server it took me 10m. My query is like:
SELECT id1, function_name(col2, 'Str1', col3, 'Str2') FROM MyTable
Order by 2 desc;

We use C/C++ external procedure. My table size is about 500M, 1M rows.

I don't want to argue why one is better than the other one. I just hope you can HELP me to improve the performance of the query on oracle server

Win2K, 4 CPUs, 2G mem, Raid5(5 disk).

The init.ora parameters I try to change is:

DB_BLOCK_BUFFERS
DB_BLOCK_LRU_LATCHES
DB_FILE_MULTIBLOCK_READ_COUNT

SORT_AREA_SIZE

PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS
PARALLEL_MIN_PERCENT

OPTIMIZER_MODE = first_rows
PARALLEL_AUTOMATIC_TUNING I didn't change them all at the same time. The final result is from 18m to 10m. I also use Oracle Expert which did no more help than what I did. The 4 processors is rather idle. Two of them about 40%, two is nearly 0. From Win2K, performance monitor, the i/o is always almost 0.

Thanks for any help, recommand, idea! Received on Wed Dec 11 2002 - 21:24:37 CST