1 2 Previous Next 15 Replies Latest reply: Mar 24, 2014 4:10 AM by 1042494 RSS

    Remote query - inconsistent results

    1042494

      Hello,

       

      Here is a test I did on 10gR2:

       

      --local DB

      create or replace view v1 as select count(*) c1 from t1@l1;

      select * from v1;

      --result: 0

      --remote DB

      insert into t1 values('a');

      commit;

      insert into t1 values('a');

      commit;

      --local DB

      select * from v1;

      --result: 1

      select * from v1;

      --result: 2

       

      Results of the last two queries are inconsistent (the cause of it is explained in docs) - what bothers me is that if I change:

      select * from v1;

      to:

      select count(*) from t1@l1;

      then each query gives same (consistent results).

       

      What's the difference (in the context of read consistency in distributed db) between issuing query using a view based on a remote query and issuing the same remote query directly?

       

      Regards,

      Andrzej

        • 1. Re: Remote query - inconsistent results
          Hemant K Chitale

          >the cause of it is explained in docs

          How is it "explained" ?   Repeating the query without changing the source data should return the same result.

           

           

          Hemant K Chitale

          • 2. Re: Remote query - inconsistent results
            1042494

            10gR2 Administrator's Guide -> 33 Managing Distributed Transactions -> Managing Read Consistency

             

            Andrzej

            • 3. Re: Remote query - inconsistent results
              Dom Brooks

              Curious.

              I read the post and I thought it must be user error.

              But I can reproduce on 11.2.0.3 some of the time, depending on what is done/how long between each execution

               

              I have observed that often first execution after the data has changed and been committed on the remote db appears to be be wrong but second execution gives correct result.

               

              I need to read the explanation because I was not expecting this.

              • 4. Re: Remote query - inconsistent results
                Dom Brooks

                The docs explains why two consecutive SELECT statements can return different data even though no DML has been executed between the two statements.

                The reason is down to the remote:local SCN synchronisation which is used to manage read consistency.

                 

                Here is a link to the explanation in the 11.2 docs:

                Managing Distributed Transactions

                 

                The docs explains why two consecutive SELECT statements can return different data even though no DML has been executed between the two statements.

                 

                I tried committing before the remote SELECT was issued and this avoids the issue.

                The docs mention this as one of two workarounds, the other being to issue another remote query.

                 

                Thanks for highlighting - one for the "learn something new every day" bucket.

                 

                i.e. either one of these avoids the read consistency issue for the second select:

                select sysdate from dual@l1;

                select * from v1;

                 

                or

                 

                 

                commit;

                select * from v1;

                • 5. Re: Remote query - inconsistent results
                  1042494

                  It's well explained in the documentation - they even give an example where a view is used - the question is why a view must be used to get such behaviour, there should be no difference when just issuing the base query (according to the documentation results should be inconsistent in both cases).

                  • 6. Re: Remote query - inconsistent results
                    Dom Brooks

                    Yes, good question.

                     

                    I would guess that the select directly against the remote db results must result in different recursive (internal) SQL against that remote db which then means the SCNs have been synchronised before the query is run whereas this does not happen with the view.

                    Would a sql trace reveal more?

                    • 7. Re: Remote query - inconsistent results
                      1042494

                      When running select on a view several times:

                      select * from v1;

                      first few runs involve two SQLs in remote DB:

                      select * from t1; - SCNs get synchronised

                      select c1 from ( select count(*) c1 from t1 );

                       

                      but after these few runs there's only one remote SQL in consecutive executions:

                      select c1 from ( select count(*) c1 from t1 );

                       

                      On the other hand - when running:

                      select count(*) from t1@l1;

                      there are two SQLs in remote DB:

                      select * from t1; - this is where SCNs get synchronised

                      select count(*) from t1;

                      In my test no matter how many times the query was run at local DB there are always two SQLs at remote DB.

                       

                      I don't know how to explain why in case of a view the recursive SQL is not needed after a few executions, while in case of a table query it's needed with each execution.

                      • 8. Re: Remote query - inconsistent results
                        1042494

                        One more update:

                        In previous post I wrote that this additional SQL performed in remote DB - that is "select * from  t1" is synchronising SCNs - unfortunately it seems it isn't.

                        Trace shows that two SQLs are issued against remote DB (when running "select * from v1" at local DB) but in spite of that the first execution of local statement gives an old SCN result from remote DB... SCNs are synchronised only after the whole statement is executed at local DB - so after running the query second time it gives consistent results.

                        • 9. Re: Remote query - inconsistent results
                          Dom Brooks

                          The call to SELECT /*+ FULL(P) +*/ * FROM "V1" P is a parse only call

                          Based on very limited testing, I thought too that this was issued every time when using the direct select against the remote DB (not via view) but it's not.

                          So, it looks like a red herring.

                           

                          I don't have access right now to a db where I can access sql trace files so I'm relying on observations of v$sql - not ideal.

                          • 10. Re: Remote query - inconsistent results
                            Mark D Powell

                            "according to the documentation results should be inconsistent in both cases,"  No, the documentation says the query results can be inconsistent with each other not that the results have to be.  It is a timing issue and the load on the two environments and the amount of distributed traffic between the two systems impacts how likely this is to occur.

                            - -

                            HTH -- Mark D Powell --

                            • 11. Re: Remote query - inconsistent results
                              1042494

                              Should be, can be, have to be - it doesn't help in answering the question - the problem is that in real tests one query never gives inconsistent results, and the other query always gives inconsistent results.

                              • 12. Re: Remote query - inconsistent results
                                Dom Brooks

                                Mark - I agree that the behaviour we are seeing is consistent with the documentation.

                                 

                                The question of why is still of interest and value though, particularly as there seems to be this consistent(ish) difference between using a local view referencing a remote table and just referencing the remote table directly.

                                • 13. Re: Remote query - inconsistent results
                                  Hemant K Chitale

                                  Hmm..  That's not nice.

                                   

                                  I wonder how many people do build in the check (query once before reading the data OR commit before reading the data) into distributed applications.

                                   

                                   

                                  Hemant K Chitale

                                  • 14. Re: Remote query - inconsistent results
                                    Dom Brooks

                                    Agree. Not nice and not widely known.

                                    But then again,perhaps the conditions are fairly niche - you have to have a database link open and then have dml happen on the remote db after your last query.

                                    1 2 Previous Next