rosaline

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!

stephan bressler

1. function_name is the external procedure?
--> need to be called for every row, thus 100M times

2. Table with 1M rows, no where-clause but with "order by"
--> Oracle need to get _all_ rows, sort them "by 2" and return after that
the first rows to client

Seems that 10 Minutes is quite fair for such a query. I'm pretty sure,
there's no parameter tuning to solve this.

TUNE YOUR SQL!!!


Stephan

telemachus

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.

damorgan

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