Forum Stats

  • 3,851,626 Users
  • 2,264,005 Discussions
  • 7,904,793 Comments

Discussions

Categories

Simple insert into table hanging

124»

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Nov 11, 2016 8:35AM

    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

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Nov 11, 2016 9:12AM

    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

    · Share on TwitterShare on Facebook
  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 11, 2016 9:20AM

    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.

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 11, 2016 9:27AM 
    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.

    · Share on TwitterShare on Facebook
  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 11, 2016 9:40AM

    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.

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 11, 2016 9:51AM 
    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!

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Nov 11, 2016 10:12AM

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

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 11, 2016 10:14AM

    Everthing was executed in SQL developer.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Nov 11, 2016 12:23PM

    Well done.

    sysdate would be [email protected]! (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

    jgarryDejan T.
    · Share on TwitterShare on Facebook
This discussion has been closed.