Re: Optimizing a query
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- Date: Mon, 03 Apr 2006 23:44:34 -0400
On 04/03/2006 07:47:46 PM, Hameed, Amir wrote:
> Thanks Mladen! The production server does have 40 CPUs. I am looking at
> the optimization from the RAC standpoint. Would PQO-based FTS be better
> than regular (buffer-based) FTS in RAC?
>
Amir, it depends on the workload and the structure of the table. If table is
partitioned and if the workload is light, PQO-based FTS will definitely be
much faster. I'm not going to speculate about the disks as with such CPU
resources you probably have SAN with RAID 1+0 and lots of cache which can
prevent the disk subsystem from becoming a bottleneck. Be aware that all
those parallel query processes consume not only significant CPU resources but
also consume major I/O bandwidth. You mentioned RAC. That also introduces
communication over the interconnect which can become a bottleneck. Something
like 10 processes "chatting" actively over the interconnect cant easily
consume its capacity, thus preventing anything else from running normally.
All in all, before scheduling a production job, you should test carefully.
Database servers with CPU as a major bottleneck are very rare occurrences
indeed. Usually, the problem lies in I/O or, in case of RAC, the interconnect.
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
