Hi Gurus,
I've been presented an Oracle installation (9.2.0.1.0) on a Windows 2003 box
(2 x Intel Xeon 3.0 Ghz, 2 Gb RAM) and I believe that the performance is
rather bad, specifically for long queries.
For example I have a 3 table join based on tables of about 500,000 records
each, taking more than 10 minutes to complete. OK, the join fields are indexed
CHAR fields, which I don't like very much. Even that I believe this is too
much time to resolve. So first of all I would like to know what should I
expect for a response time on this. If the times are appropriate the rest of
the mail might have no sense. Anyway, here we go:

I've analyzed the execution plans that seem to be right (the execution path
shows no full table scans, only index accesses). I need to mention that the
server was originally configured as a shared server, which I changed to
dedicated with no differences in time response (at least with one user). I
also changed the buffers' sizes, which didn't make any difference either. So
I'm suspicious about the I/O performance.
Now I've created a separated tablespace with a bigger block size (16k against
the original 8k) to store there some of the biggest tables, and another
similar tablespace for the indexes related to those tables.
I would like to know if you recommend to use uniform extent sizes or auto
allocation for this (I'm not familiar with the last one, which seems to be
used in this installation), and also if you consider that a few bigger extents
(with size multiple of the multiblock read size) would lead to better query
performance than a lot of small extents.
Finally, if you have any other recommendation for this subject, I would be
very thankful. Also if you would like to have further info, please let me know.

Thanks in advance,

--Claudio


Oracle LazyDBA home page