-
30. Re: Simple insert into table hanging
Okay, I have run explain plans, I have found that, it does NOT always run the select locally, as you can see in the second plan. What I did here was do a CTAS to create the table, which took less than a second, and the subsequent insert also execute as quick.
Can somebody shed some light what the execution plans are different
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8tah0qth71hdp, child number 1
-------------------------------------
INSERT INTO "DATQ"."OFFERS_CHECK" (EXTRCT,
ISSUE, A_COUNT, SITE, SOURCE) SELECT 'OFFERS'
EXTRCT, 'MISSING OFFERS' ISSUE, COUNT(*) A_COUNT,
OFFERS.SITE,OFFERS.SOURCE
FROM "DATQ".OFFERS WHERE "OFFERS".EDL_ID
NOT IN (SELECT "EDL_ID" FROM "DATQ".LIST) AND SOURCE = 'PST'
GROUP BY OFFERS.SITE, OFFERS.SOURCE
Plan hash value: 371604923
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 12G(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | OFFERS_CHECK | | | | | | |
| 2 | HASH GROUP BY | | 6 | 228 | 12G (1)|130:33:00 | | |
|* 3 | FILTER | | | | | | | |
| 4 | REMOTE | OFFERS | 983K| 35M| 3924 (1)| 00:00:01 | PDBPR~ | R->S |
| 5 | REMOTE | LIST | 1 | 25 | 12260 (1)| 00:00:01 | PDBPR~ | R->S |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( IS NULL)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT /*+ OPAQUE_TRANSFORM */ "SOURCE", "EDL_ID", "SITE" FROM
"DATQ"."OFFERS" "OFFERS" WHERE "SOURCE"='PST' (accessing 'PDBLIVE' )
5 - SELECT /*+ OPAQUE_TRANSFORM */ "EDL_ID" FROM "DATQ"."LIST"
"A1" WHERE LNNVL("EDL_ID"<>:1) (accessing 'PDBLIVE' )
This is the table I first create using a CTAS, and the truncate, followed by the insert to match the scenario above....
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6y057jm6wt29p, child number 0
-------------------------------------
insert into OFFERS_CHECK2 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
Plan hash value: 1788691278
-------------------------------------------------------------------------
| Id | Operation | Name | Cost | Inst |IN-OUT|
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | |
| 1 | LOAD TABLE CONVENTIONAL | OFFERS_CHECK | | | |
| 2 | REMOTE | | | PDBPR~ | R->S |
-------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT 'OFFERS','MISSING OFFERS',COUNT(*),"A1"."SITE","A1"."SOURC
E" FROM "DATQ"."OFFERS" "A1" WHERE "A1"."EDL_ID"<>ALL (SELECT
"A2"."EDL_ID" FROM "DATQ"."LIST" "A2") AND "A1"."SOURCE"='PST'
GROUP BY "A1"."SITE","A1"."SOURCE" (accessing 'PDBLIVE' )
Note
-----
- cpu costing is off (consider enabling it)
-
31. Re: Simple insert into table hanging
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
-
32. Re: Simple insert into table hanging
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
-
33. Re: Simple insert into table hanging
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.
-
34. Re: Simple insert into table hanging
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.
-
35. Re: Simple insert into table hanging
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.
-
36. Re: Simple insert into table hanging
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!
-
37. Re: Simple insert into table hanging
How was the run which produced the first execution plan (with the FILTER operation) executed ?
Regards
Jonathan Lewis
-
-
39. Re: Simple insert into table hanging
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.
-
40. Re: Simple insert into table hanging
Well done.
sysdate would be sysdate@! (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
Register and Participate in Oracle's online communities. Learn from thousands of experts, get answers to your questions and share knowledge with peers.