Re: Oracle 8.1.7 huge performance problem : please help !

kouchba_at_yahoo.com (kouchba) wrote in message news:<f55a8ec8.0401140345.17e23c71_at_posting.google.com>...
> Hi there,
>
> We are using Oracle 8.1.7 on Solaris 7 and Oracle 7.3.4 as well.
> We compared the execution plan of a query under Oracle 7 and Oracle
> 8.1.7 and it is exactly the same (using the same indexes,..). The
> number of extents of the index is the same too. The data files of the
> two instances are located on the same disks. But each time we execute
> the query under Oracle 8 , it is about ten times slower. That
> slowliness is not related to that particular query but to the whole
> application when executed under Oracle 8.1.7. So I am thinking more of
> an instance problem rather than an application problem.
>
> It seems to be IO bound to me just as something which have changed in
> the way Oracle 8 interacts with the filesystem. I tried to play with
> the hidden parameter _filesystemio_options but it did not work.
>
> Any idea ?
>
> Thanks,
> Philippe (kouchba_at_yahoo.com, pbackouche_at_sepro-robotique.com)

We have a fairly large system which we migrated from 7.3 to 8.0 to 8.1 and we did not encounter any overall performance problem. I suspect the problem you describe is related to your specific setup.

First, try to map your logical disk layout to your physical disk layout. Depending on your disk setup (RAID 0, RAID 5, or just plain disks) the two may not be one and the same. You may have a hot disk or overloaded IO channell to contend with.

Second, If the disk farm itself look OK take a close look at all the init.ora parameters that affect the optimizer such as multi_block_read_count and sort_area_size and look for any changes to the parameter values made between the versions.

If items 1 and 2 seem Ok then try reorganizing each of the objects involved and recalculating the statistics. Then recheck the plans. No change in plan and a performance problem would indicate some type of resource problem. My best guess is IO. Since you should have already verified the physical IO resources above then check that the version 8.1 buffer pool is at least as large as the version 7.3 buffer pool. Since you now have two db running make sure that you have not exhaused system memory or cpu.

Also compare the quantity of the data. Unless the counts are equal then the same plan may not be the right plan.

HTH -- Mark D Powell -- Received on Wed Jan 14 2004 - 09:23:54 CST