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  |
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. , 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 #343241 is a reply to message #343205] |
Tue, 26 August 2008 13:37   |
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   |
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 #343266 is a reply to message #343205] |
Tue, 26 August 2008 15:43   |
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 #343269 is a reply to message #343205] |
Tue, 26 August 2008 15:57   |
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 #343631 is a reply to message #343516] |
Wed, 27 August 2008 12:35   |
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  |
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...
|
|
|
Goto Forum:
Current Time: Sat Feb 15 15:10:21 CST 2025
|