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?

AParm

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

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

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

AndrewSayer

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

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

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

AParm

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

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

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.