Wrong results when parallel hint specified in select stt.

From: <Prasada.Gunda1_at_hartfordlife.com>
Date: Tue, 24 Oct 2000 09:51:47 -0400
Message-Id: <10659.120036@fatcity.com>

Last week, I sent an email to the list on the same issue and I also posted it on metalink. I got a response from oracle tech support and I would like share the info with the list. Oracle tech support said that the work around is setting 'parallel_min_servers' parameter to 0. It is already set to 0 which is default.

Regards,
Prasad

 From: PRASADA GUNDA 19-Oct-00 13:29
 Subject: Wrong results when parallel hint specified in select stt.

 RDBMS Version: 8.1.6
 Operating System and Version: Hp-Ux v11  Error Number (if applicable):
 Product (i.e. SQL*Loader, Import, etc.): Oracle Database  Product Version: 8.1.6

 Wrong results when parallel hint specified in select stt.

 Hi,

 We are using oracle 8.1.6 on Hp-ux v11.

 The following query gives wrong results with parallel hint specified in the  select statement and it gives correct results if I don't specify the parallel hint.

 Table has 4 partitions and 21 million rows.

 If any body experiences the same kind of problem, please share with the List.

 Your suggestions are appreciated.

 Regards,
 Prasad

 Without Parallel hint :

---

 select
 POB.CLIENT_KEY||'*'||POB.PROVISION_OPTION_KEY ||
'*'||POB.PROVISION_OPTION_EFF_DT ||'*'||POB.source_system_selection_number, count(*)
 from PROVISION_OPTION_BRIDGE POB
 where POB.CURRENT_RECORD_FLAG = 'Y'
 group by POB.CLIENT_KEY||'*'||POB.PROVISION_OPTION_KEY ||
'*'||POB.PROVISION_OPTION_EFF_DT ||'*'||POB.source_system_selection_number
 having count(*) > 1
 /

 no rows selected

 real: 811140

 With Parallel hint :

---

 select /*+ parallel(POB,4) */
 POB.CLIENT_KEY||'*'||POB.PROVISION_OPTION_KEY ||
'*'||POB.PROVISION_OPTION_EFF_DT ||'*'||POB.source_system_selection_number, count(*)
 from PROVISION_OPTION_BRIDGE POB
 where POB.CURRENT_RECORD_FLAG = 'Y'
 group by POB.CLIENT_KEY||'*'||POB.PROVISION_OPTION_KEY ||
'*'||POB.PROVISION_OPTION_EFF_DT ||'*'||POB.source_system_selection_number
 having count(*) > 1
 /

 KEYSET COUNT(*)

---

 658491*9914*01-apr-1997* 378830
 9093*9993*01-mar-1997* 349584
 91034*913*01-jul-1993*1 368828
 91087*9495*15-feb-1994* 275089
 9152*913*01-may-1997*6 375721
 9160*6025*01-may-1997* 368188
 92030*9761*31-dec-1997* 382153

 9225*1637*01-dec-1992*7 374738
 9234*943*01-jan-1963*1 377496
 9235*9645*01-dec-1970* 376567
 95703*9547*01-nov-1997* 368424
 97943*5683*01-nov-1997* 366255
 97968*9852*01-nov-1997* 389236
 99999*9914*01-nov-1997* 380808
 83210*6028*01-feb-1992* 366687
 90187*9647*01-sep-1998* 380535
 90668*7985*01-jul-1993* 373398

 91017*928*01-apr-1998*5 372303
 92026*7985*31-dec-1997* 370508
 9233*5683*01-jan-1986* 364760
 9233*9547*01-jan-1986* 362553
 99994*9728*01-nov-1997* 380813

 139811*9547*01-jan-1999* 383791
 398542*9547*01-jan-1997* 367055
 401160*5807*01-jan-1997* 375540
 638638*8004*01-aug-1991* 347182
 768473*6753*01-jan-1975* 372414

 78931*6026*01-jan-1989* 330962
 83233*2268*01-feb-1998*2 338706

 8858*7985*01-may-1990* 338199
 9100*854*01-mar-1997* 361710
 9161*5926*01-jan-1969* 354516
 9161*9570*01-jan-1969* 379557
 91942*939*01-mar-1998*7 376919
 91943*1637*01-mar-1998*7 370476
 92030*7985*31-dec-1997* 374148

 92031*6026*31-dec-1997* 356468
 92048*9728*01-jul-1993* 325125
 9233*9915*01-jan-1986* 384471
 262325*9547*01-feb-1995* 354370
 445690*9547*24-mar-1990* 365907
 82751*853*01-dec-1997* 346976
 83197*7985*01-jan-1998* 369856

 8716*7982*15-apr-1997* 290494
 90436*913*01-jul-1993*2 369492
 9089*1637*01-may-1988*4 369547

 9109*939*01-may-1997*7 368707
 9161*9698*01-jan-1969* 367305

 92030*6026*31-dec-1997* 373943
 98121*1637*01-nov-1997*7 376680
 98186*7974*01-nov-1997* 314724

 51 rows se

 From: Oracle, Ken Robinson 20-Oct-00 17:40  Subject: Re : Wrong results when parallel hint specified in select stt.

 This may be related to bug 1226434 (still under investigation). The work-around listed in the bug is to set

 parallel_min_servers to 0

 Does that seem to help with your scenario? I recommend you file an iTAR to work this more  interactively. The more diagnostics information the better on narrowing down this problem and finding a  resolution.

 NOTE: Development throws a lot of attention at issues like this where query results are wrong. Received on Tue Oct 24 2000 - 08:51:47 CDT