1 2 3 Previous Next 40 Replies Latest reply on Nov 11, 2016 5:23 PM by Jonathan Lewis Go to original post
      • 30. Re: Simple insert into table hanging
        AParm

        Okay, I have run explain plans, I have found that, it does NOT always run the select locally, as you can see in the second plan. What I did here was do a CTAS to create the table, which took less than a second, and the subsequent insert also execute as quick.

         

        Can somebody shed some light what the execution plans are different

         

        PLAN_TABLE_OUTPUT                                                                                                                                                                                     

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

        SQL_ID  8tah0qth71hdp, child number 1                                                                                                                                                                  

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

        INSERT INTO "DATQ"."OFFERS_CHECK" (EXTRCT,                                                                                                                                     

        ISSUE, A_COUNT, SITE, SOURCE) SELECT 'OFFERS'                                                                                                                                   

        EXTRCT, 'MISSING OFFERS' ISSUE, COUNT(*) A_COUNT,                                                                                                                                       

        OFFERS.SITE,OFFERS.SOURCE                                                                                                                                   

        FROM "DATQ".OFFERS WHERE "OFFERS".EDL_ID                                                                                                                               

        NOT IN (SELECT "EDL_ID" FROM "DATQ".LIST) AND SOURCE = 'PST'

        GROUP BY OFFERS.SITE, OFFERS.SOURCE                                                                                                                                

                                                                                                                                                                                                               

        Plan hash value: 371604923                                                                                                                                                                             

                                                                                                                                                                                                               

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

        | Id  | Operation                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|                                                                                

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

        |   0 | INSERT STATEMENT         |                            |       |       |    12G(100)|          |        |      |                                                                                

        |   1 |  LOAD TABLE CONVENTIONAL | OFFERS_CHECK               |       |       |            |          |        |      |                                                                                

        |   2 |   HASH GROUP BY          |                            |     6 |   228 |    12G  (1)|130:33:00 |        |      |                                                                                

        |*  3 |    FILTER                |                            |       |       |            |          |        |      |                                                                                

        |   4 |     REMOTE               | OFFERS                     |   983K|    35M|  3924   (1)| 00:00:01 | PDBPR~ | R->S |                                                                                

        |   5 |     REMOTE               | LIST                       |     1 |    25 | 12260   (1)| 00:00:01 | PDBPR~ | R->S |                                                                                

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

                                                                                                                                                                                                               

        Predicate Information (identified by operation id):                                                                                                                                                    

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

                                                                                                                                                                                                               

           3 - filter( IS NULL)                                                                                                                                                                                

                                                                                                                                                                                                               

        Remote SQL Information (identified by operation id):                                                                                                                                                   

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

                                                                                                                                                                                                               

           4 - SELECT /*+ OPAQUE_TRANSFORM */ "SOURCE", "EDL_ID", "SITE" FROM                                                                                                

               "DATQ"."OFFERS" "OFFERS" WHERE "SOURCE"='PST' (accessing 'PDBLIVE' )                                                                                              

                                                                                                                                                                                                               

           5 - SELECT /*+ OPAQUE_TRANSFORM */ "EDL_ID" FROM "DATQ"."LIST"                                                                                           

               "A1" WHERE LNNVL("EDL_ID"<>:1) (accessing 'PDBLIVE' )         

         

         

         

        This is the table I first create using a CTAS, and the truncate, followed by the insert to match the scenario above....

         

         

         

         

         

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

        PLAN_TABLE_OUTPUT                                                                                                                                                                                     

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

        SQL_ID  6y057jm6wt29p, child number 0                                                                                                                                                                  

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

        insert into OFFERS_CHECK2 select  'OFFERS' EXTRCT, 'MISSING OFFERS'                                                                                                                                     

        ISSUE,  count (*) A_Count, OFFERS.SITE, OFFERS.SOURCE from OFFERS                                                                                                                                      

        WHERE OFFERS.EDL_ID not in (select EDL_ID from LIST) and SOURCE = 'PST'                                                                                                                                

        GROUP BY OFFERS.SITE, OFFERS.SOURCE                                                                                                                                                                    

                                                                                                                                                                                                               

        Plan hash value: 1788691278                                                                                                                                                                            

                                                                                                                                                                                                               

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

        | Id  | Operation                | Name         | Cost  | Inst   |IN-OUT|                                                                                                                              

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

        |   0 | INSERT STATEMENT         |              |     1 |        |      |                                                                                                                              

        |   1 |  LOAD TABLE CONVENTIONAL | OFFERS_CHECK |       |        |      |                                                                                                                              

        |   2 |   REMOTE                 |              |       | PDBPR~ | R->S |                                                                                                                              

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

                                                                                                                                                                                                               

        Remote SQL Information (identified by operation id):                                                                                                                                                   

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

                                                                                                                                                                                                               

           2 - SELECT 'OFFERS','MISSING OFFERS',COUNT(*),"A1"."SITE","A1"."SOURC                                                                                                                               

               E" FROM "DATQ"."OFFERS" "A1" WHERE "A1"."EDL_ID"<>ALL (SELECT                                                                                                                               

               "A2"."EDL_ID" FROM "DATQ"."LIST" "A2") AND "A1"."SOURCE"='PST'                                                                                                                              

               GROUP BY "A1"."SITE","A1"."SOURCE" (accessing 'PDBLIVE' )                                                                                                                                       

                                                                                                                                                                                                               

                                                                                                                                                                                                               

        Note                                                                                                                                                                                                   

        -----                                                                                                                                                                                                  

           - cpu costing is off (consider enabling it)    

        • 31. Re: Simple insert into table hanging
          Jonathan Lewis

          Interesting.

          There are a couple of odd details I'd like to check:

           

          Your second plan shows an insert into OFFERS_CHECK despite the fact that the insert statement itself insert into offers_check2.

           

          Your second plan shows a note "cpu costing is off" the first doesn't, was this just a shortened cut and paste or a different configuration; are there any other parameters which set to non-standard values (can you show use the OUTLINE section so we can check any other optimizer parameters). The plan where the query operates as two separate remote statement is likely to be the more interesting one.

           

          It's a little odd that the first insert show various names double-quoted - what tool is the SQL coming from ?

           

          Are DATQ.offers and DATQ.lists synonyms for the remote tables ?

           

          Regards

          Jonathan Lewis

          • 32. Re: Simple insert into table hanging
            Jonathan Lewis

            I've just run up a little test modelling your code.

            My comments about "fully remote" inserts behaving the same as distributed is wrong - even in 11.2.0.4 I got a query like yours to execute fully remotely.

             

            It looks as if the difference MIGHT be related in some way to "null aware" anti joins, so do check the optimizer parameters for both versions of the statement.

             

            Regards

            Jonathan Lewis

            • 33. Re: Simple insert into table hanging
              AParm

              I have just had to replace some name, for confidentiality reasons, I might have made some typos, it's all sql developer.

               

              DATQ.offers and DATQ.lists are synonyms for the remote tables.

              • 34. Re: Simple insert into table hanging
                John Thorton

                Amarprem wrote:

                 

                I have just had to replace some name, for confidentiality reasons, I might have made some typos, it's all sql developer.

                 

                DATQ.offers and DATQ.lists are synonyms for the remote tables.

                Are both "remote tables" in the same remote database?

                 

                What happens if you "push" the desired rows from the remote database into the target table?

                This way no extraneous data needs to traverse the network.

                The current implementation needs to bring all row across the network in order to decide which rows get inserted.

                • 35. Re: Simple insert into table hanging
                  AParm

                  Yes, both remote tables are in the same remote database.

                   

                  This is a dml statement that forms part of a process, with other similar statements, it's a process that worked on a single database, now some of our schemas have moved to another database, and I'd prefer to maintain the same process.

                   

                  The test show the select part of the insert can be done remotely, it's just a case of working out how oracle is doing it.

                  • 36. Re: Simple insert into table hanging
                    John Thorton

                    Amarprem wrote:

                     

                    Yes, both remote tables are in the same remote database.

                     

                    This is a dml statement that forms part of a process, with other similar statements, it's a process that worked on a single database, now some of our schemas have moved to another database, and I'd prefer to maintain the same process.

                     

                    The test show the select part of the insert can be done remotely, it's just a case of working out how oracle is doing it.

                     

                    SQL does not know or care about what may have existed in the past & the past has ZERO relevance to the current environment.

                    Injecting the past into this discussion is a distraction & does not get you closer to any resolution of the present.

                    You are free to live with what you have.

                     

                    ENJOY!

                    • 37. Re: Simple insert into table hanging
                      Jonathan Lewis

                      How was the run which produced the first execution plan (with the FILTER operation) executed ?

                       

                       

                      Regards

                      Jonathan Lewis

                      • 38. Re: Simple insert into table hanging
                        AParm

                        Everthing was executed in SQL developer.

                        • 39. Re: Simple insert into table hanging
                          AParm

                          Ok, I think I've worked out why it was hanging, the table had a default value of SYSDATE for a date column, which isn't in the insert select statement, I've removed the default and now it inserts on 0.85 sec.

                           

                          If I put sysdate in the select statement...

                           

                          insert table(

                          ..

                          ..

                          DATE_COLUMN)

                          select

                          ..

                          ..

                          SYSDATE

                          from  TABLE;

                           

                           

                          it chooses the first plan, and it hangs.

                          • 40. Re: Simple insert into table hanging
                            Jonathan Lewis

                            Well done.

                            sysdate would be sysdate@! (the local database) which makes the query a distributed query rather than a fully remote query, which is why it then can't be operated remotely but has to be operated from the local host.

                            This gives you the plan where, for each row you pull back from OFFERS you have to run the subquery against LISTS.

                             

                            Regards

                            Jonathan Lewis

                            1 2 3 Previous Next