Home » SQL & PL/SQL » SQL & PL/SQL » Parallel query returns wrong results (oracle 10.2.0.2 on AIX 5.3.0.0)
Parallel query returns wrong results [message #343205] Tue, 26 August 2008 11:22 Go to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Hi All,

I ran the following 3 queries on my database, expecting the same results, but the last 2 are returning wrong results. I did some research & applied patch #5140532 (Metalink note 5140532.Cool, but this didn't resolve the problem. If i disable parallel query, the queries all produce the correct result, but with very bad performance.

Below are the queries in question:

1. Select count(*) from advprod.f_epis a, advprod.a_epis_clm b where a.epi_id = b.epi_id;

2. Select count(*) from advprod.a_epis_clm where epi_id in ( select epi_id from advprod.f_epis);

3. Select count(*) from advprod.a_epis_clm where exists (select * from advprod.f_epis where advprod.f_epis.epi_id = advprod.a_epis_clm.epi_id);

Can anyone help with this?
Re: Parallel query returns wrong results [message #343229 is a reply to message #343205] Tue, 26 August 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a test case that we can reproduce.

Regards
Michel
Re: Parallel query returns wrong results [message #343231 is a reply to message #343205] Tue, 26 August 2008 12:54 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
I don't understand your request, Michel...
Re: Parallel query returns wrong results [message #343233 is a reply to message #343231] Tue, 26 August 2008 12:57 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Do query 2 & 3 produce the same count?
Re: Parallel query returns wrong results [message #343234 is a reply to message #343233] Tue, 26 August 2008 13:00 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
And also does this query return anything?

SELECT   epi_id,
         COUNT (*)
    FROM advprod.f_epis
GROUP BY epi_id
  HAVING COUNT (*) > 1
Re: Parallel query returns wrong results [message #343235 is a reply to message #343231] Tue, 26 August 2008 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
divroro12 wrote on Tue, 26 August 2008 10:54
I don't understand your request, Michel...



http://en.wikipedia.org/wiki/Test_case
Re: Parallel query returns wrong results [message #343236 is a reply to message #343231] Tue, 26 August 2008 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
divroro12 wrote on Tue, 26 August 2008 19:54
I don't understand your request, Michel...

In order to help you it would be easier if we can reproduce what you said on our database. For this we need tables and data.
So post create table and insert statements for us to execute the same thing.

Regards
Michel

Re: Parallel query returns wrong results [message #343238 is a reply to message #343205] Tue, 26 August 2008 13:24 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
1. Yes, 2 & 3 produce the same results.

2. 1 SELECT epi_id,
2 COUNT (*)
3 FROM advprod.f_epis
4 GROUP BY epi_id
5* HAVING COUNT (*) > 1
6 /

no rows selected

Regards,

divroro12
Re: Parallel query returns wrong results [message #343239 is a reply to message #343238] Tue, 26 August 2008 13:30 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
In that case then a test case is going to be required like Michel said.
Re: Parallel query returns wrong results [message #343241 is a reply to message #343205] Tue, 26 August 2008 13:37 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
I have previously experienced the same problem and it was down to using DEFAULT parallelism. Trying setting a physicaly number on the table's and/or indexes

ie.

instead of

alter table table_name parallel;


use

alter table table_name parallel degree 4;


Our easiest solution was to upgrade to 10.2.0.4 and the problem disappeared.

[Updated on: Tue, 26 August 2008 13:37]

Report message to a moderator

Re: Parallel query returns wrong results [message #343243 is a reply to message #343241] Tue, 26 August 2008 13:55 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Coleing,
As i mentioned earlier, when i disable parallelism, the queries produce the same results, but system performance is horrible! Right now, we can't upgrade to 10.2.0.4.

Michel,
Below is your request:

1. CREATE TABLE "ADVPROD"."" ("EPI_ID" CHAR(16 byte) NOT NULL,
"MDST_RECORD_ID" CHAR(12 byte) NOT NULL, "MDST_UPDT_CNT"
NUMBER(7) NOT NULL, "PERSON_KEY" NUMBER(9) NOT NULL)
TABLESPACE "T_LARGE_ADVPROD" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
PARALLEL ( DEGREE DEFAULT )
NOLOGGING
MONITORING

2. CREATE TABLE "ADVPROD"."" ("DEMOG_KEY" NUMBER(9) NOT NULL,
"EPIS_PRIM_PRAC_KEY" NUMBER(9) NOT NULL, "EPI_FIRST_PRD_KEY"
NUMBER(5) NOT NULL, "EPI_GEO_PROV_KEY" NUMBER(9) NOT NULL,
"EPI_LAST_PRD_KEY" NUMBER(5) NOT NULL, "EPI_MNG_PHYS_KEY"
NUMBER(9) NOT NULL, "EPI_OTHER_KEY" NUMBER(9) NOT NULL,
"EPI_PRM_PHYS_KEY" NUMBER(9) NOT NULL, "EP_SRC_PRM_PHY_KEY"
NUMBER(9) NOT NULL, "EP_SRC_PRM_PRC_KEY" NUMBER(9) NOT NULL,
"FAMILY_KEY" NUMBER(9) NOT NULL, "GEO_KEY" NUMBER(7) NOT NULL,
"IMPUTED_PCP_KEY" NUMBER(9) NOT NULL, "MNG_PRACTICE_KEY"
NUMBER(9) NOT NULL, "PCP_PRAC_KEY" NUMBER(9) NOT NULL,
"PCP_PROV_KEY" NUMBER(9) NOT NULL, "PERSON_KEY" NUMBER(9) NOT
NULL, "PLAN_KEY" NUMBER(9) NOT NULL, "SRC_MNG_PHYS_KEY"
NUMBER(9) NOT NULL, "SRC_MNG_PRAC_KEY" NUMBER(9) NOT NULL,
"SRC_PCP_KEY" NUMBER(9) NOT NULL, "SRC_PCP_PRAC_KEY"
NUMBER(9) NOT NULL, "EPI_ADM_CNT" NUMBER(3) NOT NULL,
"EPI_COST_SCALE" NUMBER(8, 3) NOT NULL, "EPI_DAYS_CNT"
NUMBER(5) NOT NULL, "EPI_ID" CHAR(16 byte) NOT NULL,
"EPI_IP_ALLOW_AMT" NUMBER(14, 2) NOT NULL,
"EPI_IP_NETPAY_AMT" NUMBER(14, 2) NOT NULL,
"EPI_IP_NRM_PRF_AMT" NUMBER(11, 2) NOT NULL,
"EPI_IP_SUBMIT_AMT" NUMBER(14, 2) NOT NULL,
"EPI_MNG_ALLOW_AMT" NUMBER(14, 2) NOT NULL, "EPI_MNG_EM_CNT"
NUMBER(5) NOT NULL, "EPI_MNG_NETPAY_AMT" NUMBER(14, 2) NOT
NULL, "EPI_MNG_NRM_FEE" NUMBER(11, 2) NOT NULL,
"EPI_MNG_SUBMIT_AMT" NUMBER(14, 2) NOT NULL,
"EPI_NRM_OTH_AMT" NUMBER(11, 2) NOT NULL, "EPI_OP_ALLOW_AMT"
NUMBER(14, 2) NOT NULL, "EPI_OP_NETPAY_AMT" NUMBER(14, 2) NOT
NULL, "EPI_OP_NRM_FEE_AMT" NUMBER(11, 2) NOT NULL,
"EPI_OP_SUBMIT_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PCP_ALLOW_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PCP_NETPAY_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PCP_NRM_FEE" NUMBER(11, 2) NOT NULL,
"EPI_PCP_SUBMIT_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PRM_ALLOW_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PRM_NETPAY_AMT" NUMBER(14, 2) NOT NULL,
"EPI_PRM_NRM_FEE" NUMBER(11, 2) NOT NULL, "EPI_PRM_RVU_AMT"
NUMBER(11, 2) NOT NULL, "EPI_PRM_SUBMIT_AMT" NUMBER(14, 2)
NOT NULL, "EPI_RX_ALLOW_AMT" NUMBER(14, 2) NOT NULL,
"EPI_RX_CNT" NUMBER(5) NOT NULL, "EPI_RX_NETPAY_AMT"
NUMBER(14, 2) NOT NULL, "EPI_RX_SUBMIT_AMT" NUMBER(14, 2) NOT
NULL, "EPI_TOT_ALLOW_AMT" NUMBER(14, 2) NOT NULL,
"EPI_TOT_EM_CNT" NUMBER(5) NOT NULL, "EPI_TOT_NETPAY_AMT"
NUMBER(14, 2) NOT NULL, "EPI_TOT_NRM_FEE" NUMBER(11, 2) NOT
NULL, "EPI_TOT_RVU_AMT" NUMBER(11, 2) NOT NULL,
"EPI_TOT_SUBMIT_AMT" NUMBER(14, 2) NOT NULL, "EPI_USE_SCALE"
NUMBER(8, 3) NOT NULL, "MDST_UPDT_CNT" NUMBER(7) NOT NULL)
TABLESPACE "T_LARGE_ADVPROD" PCTFREE 5 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
PARALLEL ( DEGREE DEFAULT )
NOLOGGING
MONITORING

The 1st table has 213752505 records, while the 2nd has 32981237.
The 1st query returns all 213752505 records as expected, but the 2nd & 3rd queries each return 6671919 records.

Regards,

divroro12
Re: Parallel query returns wrong results [message #343251 is a reply to message #343243] Tue, 26 August 2008 14:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am really surprised still people are using CHAR datatype. I wish oracle never introduced it (unfortunately it has to be there because of ANSI, I believe).

Regards

Raj
Re: Parallel query returns wrong results [message #343262 is a reply to message #343205] Tue, 26 August 2008 15:34 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hi - Please re-read my post.

I am not suggesting you turn off parallelism, just dont use DEFAULT parallelism. Use a proper parallel degree.

When we had the same problem you are describing, only the DEFAULT parallel option would give incorrect results. If I set paralle at the query level, or at the table level to an integer value (like 4 or Cool, I would retain parallel performance, and get the right results.
Re: Parallel query returns wrong results [message #343266 is a reply to message #343205] Tue, 26 August 2008 15:43 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks for the clarification Coleing. Please see below:

SQL> desc a_epis_clm
Name Null? Type
----------------------------------------- -------- ----------------------------
EPI_ID NOT NULL CHAR(16)
MDST_RECORD_ID NOT NULL CHAR(12)
MDST_UPDT_CNT NOT NULL NUMBER(7)
PERSON_KEY NOT NULL NUMBER(9)

SQL> alter table a_epis_clm parallel degree 4;
alter table a_epis_clm parallel degree 4
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter table advprod.a_epis_clm parallel degree 4;
alter table advprod.a_epis_clm parallel degree 4
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


divroro12
Re: Parallel query returns wrong results [message #343268 is a reply to message #343205] Tue, 26 August 2008 15:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You would not get error if you used valid/correct syntax.
Re: Parallel query returns wrong results [message #343269 is a reply to message #343205] Tue, 26 August 2008 15:57 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Sorry, here is a proper example with correct syntax:-

SQL> 
SQL> create table test(field1 varchar2(1000));
 
Table created
SQL> select table_name, degree from user_Tables where table_name = 'TEST';
 
TABLE_NAME                     DEGREE
------------------------------ ----------
TEST                                    1
SQL> alter table test parallel;
 
Table altered
SQL> select table_name, degree from user_Tables where table_name = 'TEST';
 
TABLE_NAME                     DEGREE
------------------------------ ----------
TEST                              DEFAULT
SQL> alter table test noparallel;
 
Table altered
SQL> select table_name, degree from user_Tables where table_name = 'TEST';
 
TABLE_NAME                     DEGREE
------------------------------ ----------
TEST                                    1
SQL> alter table test parallel 4;
 
Table altered
SQL> select table_name, degree from user_Tables where table_name = 'TEST';
 
TABLE_NAME                     DEGREE
------------------------------ ----------
TEST                                    4
 
SQL> 
Re: Parallel query returns wrong results [message #343516 is a reply to message #343205] Wed, 27 August 2008 08:05 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks for your help Coleing. Will test on this & see what gives...

divroro12
Re: Parallel query returns wrong results [message #343631 is a reply to message #343516] Wed, 27 August 2008 12:35 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Well Coleing,

Following a series of tests after turning off parallel_max_servers dynamically from 30 to 0 & changing parallelism @ the table level from default to 4, then 30, i got the correct results for all queries, but this had a very -ve impact on performance (initially with parallel_max_servers @ 30, the queries took an average of 3 minutes, but with parallel_max_servers @ 0 & table parallelism degree @ 4, then 30, each query took over 15 minutes to run; i just had to stop at one point since it was taking so much time.

Any other ideas other than upgrading to 10..2.0.4?

Regards,

divroro12
Re: Parallel query returns wrong results [message #343701 is a reply to message #343205] Wed, 27 August 2008 18:41 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Increase the parallel from 4 on each table in the query (or better still, control the degree of parallelism via the query rather than the table).

In general, you can probably get away with 2xProcessors for the DOP.

I always advocate having the DOP (degree of parallelism) at the query level rather than the table level, as you have more control over what resources are used and needed for each individual application.

example:-

select /*+ PARALLEL(a,8) parallel(b,8) */ *
from table_a a, table_b b
where a.column =b.column;


But to be honest, if you have Oracle Support, you are better off getting them to sort it out - its what they are bound to do under contract. You are getting incorrect results, so they need to fix something.

Have you recently upgraded hardware as well? (ie. 32 bit to 64 bit). There is another patch for that also in metalink...

Previous Topic: using forall and bulk collect together
Next Topic: NestedTable
Goto Forum:
  


Current Time: Sat Feb 15 15:10:21 CST 2025