Re: Optimizing a query

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

--
http://www.freelists.org/webpage/oracle-l