Re: sort + external procedure tuning

rosaline wrote:

> 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
> SORT_AREA_RETAINED_SIZE
> SORT_MULTIBLOCK_READ_COUNT
>
> 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!

Your posting generates more questions than answers.

Which version of 8i? If, for example 8.1.6, I wouldn't be surprised unless the optimizer was patched.
How recent/accurate were the statistics generated by running DBMS_STATS for the optimizer?
Are the indexes identical?
Did you run explain plan to see if the indexes were being used? What is the function doing?

Before you start messing with init.ora parameters I'd suggest a return to basics.

Dan Morgan Received on Thu Dec 12 2002 - 11:37:31 CST