Forum Stats

  • 3,827,128 Users
  • 2,260,743 Discussions
  • 7,897,179 Comments

Discussions

Simple insert into table hanging

AParm
AParm Member Posts: 354 Blue Ribbon
edited Nov 11, 2016 12:23PM in General Database Discussions

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

John ThortonjgarryDejan T.

Best Answer

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 11, 2016 10:45AM Answer ✓

    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.

«134

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 10, 2016 2:10PM

    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.


  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 2:16PM
    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 thisI 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)

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Nov 10, 2016 2:18PM
    Amarprem wrote:Hi there,despite the query running in less than a second..

    How did you measure this?

    MK

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 2:23PM

    I'm in sql developer.

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 2:26PM

    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?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 2:30PM
    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.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 10, 2016 2:31PM
    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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited Nov 10, 2016 2:54PM

    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.

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 2:56PM

    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?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 3:01PM
    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 [email protected], [email protected] where a.col = b.col)

    loop

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

    end loop;

    end;

    /

This discussion has been closed.