Show us an explain plan and the tkprof. We need to know where the bottle
neck is.
Jim
"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 Wed Dec 11 2002 - 22:19:43 CST
