Seq Scan: 5.0-7.3

Hello All, I have two identical db's running. 1) Online 5.07/SCO os5.04 running on a ALR 6xPPro 200 512MB ram 2) IDS 7.3/Solaris 2.6 running on Sun E3500 4x336 Ultrasparc 512MB ram On two identical tables with identical index's I am experiencing a performance problem with the IDS instance. The simple sql below run through dbaccess on both machines returns in less than 2 seconds on the Online/SCO box, and takes more than ten seconds on the IDS/Solaris box. select * from contest order by contest_date, contest_time Output from 'set explain on' reveals that they are both doing a sequential scan, and using temp space because of the order by. Temp space on both machines resides in the root dbspace. Both instances have plenty of space in the root dbspace. Why isn't IDS as fast or faster on what I would consider superior hardware for this sequential scan and order? I imagine in the future I will tune IDS by using the Read Ahead values, but I cannot imagine such a large discrepancy exists because I have not set those Read Ahead values. Any help would be greatly appreciated. Olaf

rico@wsx.wsex.com wrote: > Hello All, > > I have two identical db's running. > > 1) Online 5.07/SCO os5.04 running on a ALR 6xPPro 200 512MB ram > 2) IDS 7.3/Solaris 2.6 running on Sun E3500 4x336 Ultrasparc 512MB ram > > On two identical tables with identical index's I am experiencing a performance problem with the IDS instance. > > The simple sql below run through dbaccess on both machines returns in less than 2 seconds on the Online/SCO box, and takes more than ten seconds on the IDS/Solaris box. > > select * from contest order by contest_date, contest_time> > Output from 'set explain on' reveals that they are both doing a sequential scan, and using temp space because of the order by. > > Temp space on both machines resides in the root dbspace. Both instances have plenty of space in the root dbspace. > > Why isn't IDS as fast or faster on what I would consider superior hardware for this sequential scan and order? > > I imagine in the future I will tune IDS by using the Read Ahead values, but I cannot imagine such a large discrepancy exists because I have not set those Read Ahead values. > > Any help would be greatly appreciated. > > Olaf Hi Olaf, There is a paramter in the configuration influencing the optimizer that was not present in version 5. It's OPTCOMPIND and may take values 0, 1 or 2. # OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 0 # To hint the optimizer For details refer to the Performance Guide. Regards -- Helmut Leininger Bull AG / Vienna Unix Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.