1 2 3 Previous Next 40 Replies Latest reply on Nov 11, 2016 5:23 PM by Jonathan Lewis

    Simple insert into table hanging

    Amarprem

      Hi there,

       

      I have a query that joins 2 tables, 1 table has 2 million records and other 1 million records, the query returns 1 record in less than 1 second, both the 2 tables being joined are accessed across a database link, despite the query running in less than a second, if I do a simple insert using the query it just hangs, and I don't understand why. The table is not locked, I have tried dropping the table before the insert and recreating it, to ensure there are no locks.

       

      If I look at the wait events all  I a can see this

       

      I am running 12c on Windows, the database link is to database in another pluggable database in the same container.

       

      SQL*Net message from dblink

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

          How do I ask a question on the forums?

           

          We can't say what you do wrong since you decided to NOT show us exactly what you do & how Oracle responds.

           


          • 2. Re: Simple insert into table hanging
            Andrew Sayer

            Amarprem wrote:

             

            Hi there,

             

            I have a query that joins 2 tables, 1 table has 2 million records and other 1 million records, the query returns 1 record in less than 1 second, both the 2 tables being joined are accessed across a database link, despite the query running in less than a second, if I do a simple insert using the query it just hangs, and I don't understand why. The table is not locked, I have tried dropping the table before the insert and recreating it, to ensure there are no locks.

             

            If I look at the wait events all I a can see this

             

            I am running 12c on Windows, the database link is to database in another pluggable database in the same container.

             

            SQL*Net message from dblink

            Does query only return 1 row or was that all that you fetched?

             

            What is being waited on at the remote DB? Is it using the same execution plan to execute the select part of the insert than if it was just doing the select?

             

            You *may* be better off writing pl/sql to do the select statement and insert the result into your target table (if it is just one row, I wouldn't be too concerned with the plsql overhead)

            • 3. Re: Simple insert into table hanging
              Mike Kutz

              Amarprem wrote:

               

              Hi there,

               

              despite the query running in less than a second..

              How did you measure this?

               

              MK

              • 4. Re: Simple insert into table hanging
                Amarprem

                I'm in sql developer.

                • 5. Re: Simple insert into table hanging
                  Amarprem

                  The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

                  • 6. Re: Simple insert into table hanging
                    Andrew Sayer

                    Amarprem wrote:

                     

                    The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

                    Consider answering the questions asked of you.

                     

                    It is doing something different, perhaps your simple query is not as simple as you think, it does operate on remote objects after all.

                     

                    Oracle can use a different execution plan for an insert statement than the select on it's own. You have the power of finding out if this is the case. You can view the sql that is being remotely executed from your local statement by looking at it's execution plan. You can view the execution plan of the remote sqls by connecting to the remote DB and checking there.

                     

                    If you don't know how to do what's asked of you then have a google first and if that doesn't help then ask here. Don't just avoid the question.

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

                      Amarprem wrote:

                       

                      The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

                      It is waiting for more input

                      • 8. Re: Simple insert into table hanging
                        Mustafa KALAYCI

                        you said table is not locked, what if there is a lock on a row ? did you select v$session for blocking_session and event fields  while your insert statement was hanging ? do that and post the result please.

                        • 9. Re: Simple insert into table hanging
                          Amarprem

                          I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

                           

                          The insert statement, has REMOTE for the 2 table, I presume, it fetching all the data for the 2 tables and doing the join locally, is that right? And if that is the case how do I get it to do the select on the remote server?

                           

                          This is a pluggable database fetching data from another pluggable database, so it's all in the same server process space, why would a remote call be any different from a local call?

                          • 10. Re: Simple insert into table hanging
                            Andrew Sayer

                            Amarprem wrote:

                             

                            I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

                             

                            The insert statement, has REMOTE for the 2 table, I presume, it fetching all the data for the 2 tables and doing the join locally, is that right? And if that is the case how do I get it to do the select on the remote server?

                             

                            This is a pluggable database fetching data from another pluggable database, so it's all in the same server process space, why would a remote call be any different from a local call?

                            Don't just tell us, SHOW us. We can only interpret what we can see, not your own interpretation. Use copy and paste.

                             

                            If you want it to use the select plan then you could hint for it (usually a driving site hint, you can view the full outline of the select plan in dbms_xplan.display_cursor with the appropriate format parameter). Or you could just do as I suggested my first response and run the select statement in PLSQL and run the return through an insert statement like:

                             

                            begin

                            for rec in (select column_a from a@remote, b@remote where a.col = b.col)

                            loop

                            insert into local_Table (column_a) values (rec.column_a);

                            end loop;

                            end;

                            /

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

                              How do I ask a question on the forums?

                               

                              I can't answer your questions since I have no idea what you have, what you do or what you see.

                               

                              Is COPY & PASTE broken for you?

                              • 12. Re: Simple insert into table hanging
                                Amarprem

                                These are the execution plans

                                 

                                SELECT STATEMENT (REMOTE)

                                  - HASH (GROUP BY)                                                         9

                                    - Access Predicates                                                          9

                                       A1.EDL_ID = A2.EDL

                                    - TABLE ACCESS (FULL)           OFFERS             981915

                                    - TABLE ACCESS (FULL)           LIST                  1786967

                                   

                                INSERT STATEMENT

                                -LOAD TABLE CONVENTIONAL

                                  - HASH (GROUP BY)                                                         9

                                    - Filter Predicates                                                             9

                                        NOT EXISTS(SEELCT 0 FROM A1 LNNVL(LIST.EDL_ID)<>:B1)

                                    - REMOTE          OFFERS                                      982004

                                    - REMOTE          LIST                                                       1

                                 

                                 

                                How do I get the insert to use the same plan as the select?

                                • 13. Re: Simple insert into table hanging
                                  Mustafa KALAYCI

                                  now next step, post your sqls please (both of them). you should really look at the link that john said: How do I ask a question on the forums?

                                  • 14. Re: Simple insert into table hanging
                                    rp0428

                                    I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

                                    Ok - first make sure you understand this concept. A query can only execute on ONE database instance.

                                     

                                    So if you have a query that needs data from MORE THAN ONE db (e.g. across a link) then Oracle has to decide what db to execute the query on.

                                     

                                    Whichever one it picks it then has to send the data from the other DB(s) to the db executing the query.

                                     

                                    So for your simple 'select' query it could be executing it on the remote db but for the 'insert' query it could be executing it on the local db.

                                     

                                    Depending on the size of the local and remote tables it can make a HUGE difference which way the data is sent.

                                     

                                    1. The last storm blew down, and destroyed, the fence between your property and your neighbor.

                                     

                                    2. You decide to build a concrete block wall to replace it and it will take 1000 blocks to build it.

                                     

                                    3. Your neighbor has 900 concrete blocks on his property he will let you use if YOU build the wall.

                                     

                                    4. You need 100 more and the store delivered them to your property.

                                     

                                    5. You can only build the wall from ONE SIDE - yours or your neighbors.

                                     

                                    Would you rather:

                                     

                                    A. Carry your 100 blocks to your neighbors property?

                                     

                                    B. Carry your neighbors 900 blocks to your property?

                                     

                                    Post your answer.

                                     

                                    We have NO IDEA who has more concrete blocks - you or your neighbor.

                                     

                                    Which is why you need to SHOW US:

                                     

                                    1. WHAT you do

                                    2. HOW you do it

                                    3. WHAT results you get

                                    1 2 3 Previous Next