Re: Oracle 8.1.7 huge performance problem : please help !

kouchba_at_yahoo.com (kouchba) wrote in message news:<f55a8ec8.0401150008.760ed0c2_at_posting.google.com>...
> 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 --

Depending on your hardware platform asnyc IO is normally only available with raw partitions. Some other file systems support async IO, but like I said this is OS dependent. On some platforms Oracle will sense the presence of asnyc IO and will automatically use it. You will need to look at your platform dependent installation manual.

Make sure both databases are configured on the same type of file system: raw, cfx, ufx, etc....

Are the database block size and multi-block read count parameters the same? The multiblock read count would affect the performance of full table scans.

Are the tables and indexes created as the same type: heap vs partition, normal b-trieve vs bitmap on both instances?

HTH -- Mark D Powell -- Received on Thu Jan 15 2004 - 09:10:22 CST