Re: Oracle 8.1.7 huge performance problem : please help !

Thanks. The netapp filesystem is recommanded by Oracle and is made of several disks appearing as a single single volume. Both Instances are on the same machine and have their files (datafiles,..) located on the single netapp volume.
I would think that it is IO bound as sort_are_size is not used when doing a full scan for example. We never analyzed the objects so I suspect the optimizer is not used at all and would probably not be used for a full scan too. regarding the shared pool, we gave it a bigger size in 8.1.7 and the data cache is bigger too in 8.1.7 compared to 7.3.4. Can I force the Instance to asynch io in 8.1.7 ? I read that in 8.1.5 and 8.1.6 , asynchronous IO were disabled whatever the value for disk_asynch_io was. Are asynchronous IO used for reads too ?
I tried to play with _filesystemio_options but it did not change anything...

Philippe

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0401140723.3bf36499_at_posting.google.com>...
> 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 Thu Jan 15 2004 - 03:01:20 CST