Re: sort + external procedure tuning

DB2 allows you to run extprocs privileged inside the wall if you tell it to. This may be the reason;excessive context switching. What does NT perfmon say about context switching ? .

What is the elapsed time for 1 row;100 1E4;1E5 rows ?

what is the tkprof elapsed time . Switch SQL_trace on and send the log file to the group.
"rosaline" <rfan_at_yahoo.com> wrote in message news:3DF80179.6EE430D1_at_hotmail.com...
> 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!
>
>
>
Received on Thu Dec 12 2002 - 11:06:11 CST