Well, finally the performance of the query improved from 12 minutes to 34
seconds. Good, isn't it? :)
It seems that when I've run the GATHER_SCHEMA_STATS procedure, somehow the
indexes were not properly analyzed. So I've made a manual analyze of the
indexes and after that the execution plan shows that the optimizer decided not
to use the indexes but make full table scans, amazingly resulting in much
better performance (34 seconds against the original 12 minutes).
Anyway, the overall system performance in other aspects is still not good in
my opinion, and I still have a few questions that I'd appreciate your help with:
1) 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).
2) Do 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?
3) Would it be good to use a tablespace with bigger block size for the bigger
tables? (let's say 16k, 32k or 64k against the original 8k).
4) What would be the recommended way to move a table and it's indexes and lobs
from a group of tablespaces to another group of tablespaces (let's say from
DATA8K, INDEX8K and LOBS8K to DATA32K, INDEX32K and LOBS32K)
Thanks in advance,
--Claudio
PS: Remember I'm using 9i.
On Wed, 25 Oct 2006 13:07:28 -0200, Claudio Alonso -Oracle DBA wrote
> 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
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page