This discussion is archived
5 Replies Latest reply: Jul 19, 2013 2:45 AM by user10458939 RSS

Same update query with same tables and data involved in production and uat gives different results.

user10458939 Newbie
Currently Being Moderated

Hi Folks,

 

Today I facing a weird issue on my production database.

 

There is a update query which selects the data from three different tables.

 

Now result of the query gives 1 lac approx. rows updated (which is wrong output).

 

Now same three tables are imported to UAT.

 

Same update query is fired but here I get only 1 row update(which is correct output).

 

Only difference between two scenarios is that Production is 2-node RAC and UAT is single instance.

 

Oracle Version used on UAT: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

 

Oracle version used on Production: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

 

Can anyone help me on this?

  • 1. Re: Same update query with same tables and data involved in production and uat gives different results.
    user10458939 Newbie
    Currently Being Moderated

    Hi,

     

    Please reply.

    If it's known issue can anyone share the post of same kind.

     

    Thanks in advance.

  • 2. Re: Same update query with same tables and data involved in production and uat gives different results.
    FreddieEssex Pro
    Currently Being Moderated

    If the data in the underlying tables is the same then the number of rows updated by the sql statement will be the same.

     

    RAC or single instance is not an issue.

     

    So either your your statement that you are running is not the same...or the data is not the same.

     

    When you run the export/import are you sure you are exporting the entire data for all the tables used in your sql statement?

     

    There is no concept of right or wrong output.  Oracle is returning the results of a query full stop.

  • 3. Re: Same update query with same tables and data involved in production and uat gives different results.
    TusharThakker Explorer
    Currently Being Moderated

    Can you share the SQL so that we can advise but as Freddie said, the only thing which can make difference is data, nothing related to server, instance, nodes, hardware at all.

     

    Regards

    Tushar

  • 4. Re: Same update query with same tables and data involved in production and uat gives different results.
    user10458939 Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for the reply.

     

    Yes,  we already had a debate on this 'Data is different on UAT and PROD'.

    So to recreate the issue tables were imported to UAT again as follows:

     

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    @PRODUCTION

     

    select count(*) from Tblpainissue  --- 113520

    select count(*) from Tblpainpayment-- 112808

    select count(*) from Tmp_Upd_T_Payment_Details -- 3684

     

    Same tables were imported under UAT using IMP utility.

    Note:  Data count in Tmp_Upd_T_Payment_Details is same due to same file was uploaded by user in both environments.

    Data in Tmp_Upd_T_Payment_Details populated by user is by uploading the file on production while on UAT it was populated by creating insert script based on upload file.

     

     

    @UAT

     

    command while import --> imp file=tbl_TMP.dmp log=imp_tbl_TMP.log tables=TBLPAINISSUE,TBLPAINPAYMENT fromuser=XYZ touser=PQR feedback=1000 buffer=1000 commit=y

     

    select count(*) from Tblpainissue  --- 113520

    select count(*) from Tblpainpayment-- 112808

    select count(*) from Tmp_Upd_T_Payment_Details -- 3684

     

     

     

    Query which is giving the wrong result.

     

     

    update tblPainIssue  b

    set b.RED_STATUS = 'T' , b.RED_AMT_GRNTED = ( select sum(a.payment_amt) payment_amount

    from TMP_UPD_T_PAYMENT_DETAILS a ,

    (select distinct client_code,redeem_id from  tblPainPayment where nvl(status,'T')='T') c

    where a.UPLD_BY = 1811 and a.client_code=b.client_code

    and b.red_status='A' and (b.RED_AMT_GRNTED is null or b.RED_AMT_GRNTED=0)

    and b.client_code=c.client_code and b.c_redeem_id= c.redeem_id

    group by a.client_code)

    where EXISTS ( select sum(a.payment_amt) payment_amount from TMP_UPD_T_PAYMENT_DETAILS a ,

    (select distinct client_code,redeem_id from  tblPainPayment where nvl(status,'T')='T') c

    where a.UPLD_BY = 1811 and a.client_code=b.client_code

    and b.red_status='A' and (b.RED_AMT_GRNTED is null or b.RED_AMT_GRNTED=0) 

    and b.client_code=c.client_code and b.c_redeem_id= c.redeem_id

    group by a.client_code);

     

     

     

    rows updated @UAT ---> 1 row update.

     

     

    rows updated @PRODUCTION --> count(*) of table Tblpainissue i.e. 113520 rows update.

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    RAC environment  was just extra information given nothing more than that.

     

    Thanks & Regards,

    Jay.

  • 5. Re: Same update query with same tables and data involved in production and uat gives different results.
    user10458939 Newbie
    Currently Being Moderated

    Hi,

     

    I raised SR with Oracle for this issue. It's a bug in 10.2.0.4 which got resolved in 10.2.0.5.

    This is the reply which I got. Hope this will help you all in future if you come across it -->

     

    I reproduced the wrong results here and my conclusion is that you are running into bug 7142215. You may find additional information about this bug in Doc ID 7142215.8 but please note the workaround mentioned in the doc does not work in 10.2.0.4 - this problem has been reported in Bug 7325866 : SETTING "_FIX_CONTROL"='5844495:OFF' FAILS WITH ORA-2097 ON 10.2.0.4


    There are 3 options to fix this:


    1. Download and apply one-off patch 7142215
    or
    2. Apply PSR 10.2.0.5 which includes the fix to bug 7142215
    or
    3. Re-write the UPDATE statement and remove the GROUP BY clause from EXISTS subquery.


    Thanks all for your support.


    Thanks.

    Jay.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points