This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Simple insert into table hanging

24

Answers

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

    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?

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 3:16PM

    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?

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown
    edited Nov 10, 2016 3:18PM

    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?

  • Unknown
    edited Nov 10, 2016 3:26PM
    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

    John Thorton
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 3:34PM
    Amarprem wrote:These are the execution plansSELECT STATEMENT (REMOTE) - HASH (GROUP BY) 9 - Access Predicates 9 A1.EDL_ID = A2.EDL - TABLE ACCESS (FULL) OFFERS 981915 - TABLE ACCESS (FULL) LIST 1786967INSERT 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 1How do I get the insert to use the same plan as the select?

    Your statement doesn't seem that simple if it's doing a group by and a not exists (or is that really a not in?)  on a remote DB.

    As I've said before, to get the same plan you can either hint it (by checking the outline and note that it's probably a driving_site hint you are after)

    OR you can use PLSQL..

    FWIW I would consider a plan exposed using dbms_xplan much easier to read than what you have presented, you can also get it to include the full outline. If you need help doing that then google it or ask - don't just ignore the request.

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 3:34PM

    insert into OFFERS_CHECK(
    EXTRCT,
    ISSUE,
    A_COUNT,
    SITE,
    SOURCE
    )
    select
    EXTRCT,
    ISSUE,
    A_COUNT,
    SITE,
    SOURCE from
    V_OFFERS_CHECK;

    CREATE V_OFFERS_CHECK
    AS
    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;

    Simple, just check for all offers not in the LIST.

    The select on it's own, less than a second. The insert just hangs, I just have to kill it.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown
    edited Nov 10, 2016 3:42PM

    where are the other databases ? OFFERS  and LIST tables, are these on different databases ?

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 3:49PM

    No they are on the same pluggable database LIVE, the are being accessed for reporting database.

    Why should it matter, they are all just pluggable database on the SAME oracle server.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 3:55PM
    Amarprem wrote:No they are on the same pluggable database LIVE, the are being accessed for reporting database.Why should it matter, they are all just pluggable database on the SAME oracle server.

    Remote inserts optimize differently.

    Remote could be different Pdb or different server completely.

    Where in the view/query are you specifying the db link? The view (I'm assuming that's what it is) is not valid DDL so I assume that wasn't copy and paste?

    Have you tried the PL/SQL solution yet (I think I've mentioned it 4 times now).

    begin

      for rec in (select EXTRCT,

                         ISSUE,

                         A_COUNT,

                         SITE,

                         SOURCE

                   from  V_OFFERS_CHECK

                  )

      loop

        insert into OFFERS_CHECK(EXTRCT,

                                 ISSUE,

                                 A_COUNT,

                                 SITE,

                                 SOURCE

                                 )

        values

        (rec.EXTRCT,

         rec.ISSUE,

         rec.A_COUNT,

         rec.SITE,

         rec.SOURCE);

      end loop;

    end;

    /

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 4:01PM

    OFFERS and LIST, are synonyms to tables on the 'remote' server.

    Thanks for this, I will try it, I will look at dbms_xplan, and take this up again tomorrow.

This discussion has been closed.