when i am running below query, it is taking around 50 hours in dev environment. same query is completed in other environment with in a minute.
data is same in both the environments. Any thoughts?
delete
FROM uet_extract_clinical uec
WHERE 1 =1
AND --(--uec.subjectid,uec.visitid,uec.formid,uec.formindex,uec.itemsetid,uec.itemid) IN
exists (SELECT 1
FROM uet_extract_clinical uec1 ,
pf_itemsetcell pic
WHERE uec.subjectid =uec1.subjectid
AND uec.visitid =uec1.visitid
AND uec.formid =uec1.formid
AND uec.formindex =uec1.formindex
AND uec.itemsetid =uec1.itemsetid
AND uec.itemid =uec1.itemid
AND pic.itemsetrow =1
AND pic.predefinedelementid<>0
AND pic.isblank =0
AND pic.itemsetid =uec1.itemsetid
AND NOT EXISTS
(SELECT 1
FROM uet_extract_clinical uec2
WHERE uec2.subjectid=uec1.subjectid
AND uec2.visitid =uec1.visitid
AND uec2.formid =uec1.formid
AND uec2.formindex =uec1.formindex
AND uec2.itemsetid =uec1.itemsetid
AND uec2.itemid <>uec1.itemid
)
)
AND formindex>1;
Explain plan in live :
Plan hash value: 1600486780
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1927K| 18G| | 1122K (1)| 03:44:32 | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1927K| 18G| 1069M| 1122K (1)| 03:44:32 | | |
| 2 | VIEW | VW_SQ_1 | 12M| 926M| | 105K (1)| 00:21:03 | | |
|* 3 | HASH JOIN | | 12M| 2174M| | 105K (1)| 00:21:03 | | |
| 4 | REMOTE | PF_ITEMSETCELL | 400 | 10800 | | 7 (0)| 00:00:01 | DEGLI~ | R->S |
|* 5 | HASH JOIN ANTI | | 1896K| 282M| 165M| 105K (1)| 00:21:03 | | |
|* 6 | TABLE ACCESS FULL| UET_EXTRACT_CLINICAL | 1927K| 143M| | 44392 (1)| 00:08:53 | | |
|* 7 | TABLE ACCESS FULL| UET_EXTRACT_CLINICAL | 1927K| 143M| | 44392 (1)| 00:08:53 | | |
|* 8 | TABLE ACCESS FULL | UET_EXTRACT_CLINICAL | 1927K| 18G| | 44413 (1)| 00:08:53 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("UEC"."SUBJECTID"="SUBJECTID" AND "UEC"."VISITID"="VISITID" AND "UEC"."FORMID"="FORMID" AND
"UEC"."FORMINDEX"="FORMINDEX" AND "UEC"."ITEMSETID"="ITEMSETID" AND "UEC"."ITEMID"="ITEMID")
3 - access("PIC"."ITEMSETID"="UEC1"."ITEMSETID")
5 - access("UEC2"."SUBJECTID"="UEC1"."SUBJECTID" AND "UEC2"."VISITID"="UEC1"."VISITID" AND
"UEC2"."FORMID"="UEC1"."FORMID" AND "UEC2"."FORMINDEX"="UEC1"."FORMINDEX" AND
"UEC2"."ITEMSETID"="UEC1"."ITEMSETID")
filter("UEC2"."ITEMID"<>"UEC1"."ITEMID")
6 - filter("UEC1"."FORMINDEX">1)
7 - filter("UEC2"."FORMINDEX">1)
8 - filter("FORMINDEX">1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "ITEMSETID","ITEMSETROW","PREDEFINEDELEMENTID","ISBLANK" FROM "DEGNN12184101"."PF_ITEMSETCELL"
"PIC" WHERE "ITEMSETROW"=1 AND "ISBLANK"=0 AND "PREDEFINEDELEMENTID"<>0 (accessing
'DEGLINKNN12184101.INT.ORACLEINDUSTRY.COM' )
Note
-----
- dynamic sampling used for this statement (level=2)
explain plan in dev :
Plan hash value: 3883672990
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10084 | 1783M (1)|999:59:59 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS FULL | UET_EXTRACT_CLINICAL | 817K| 7864M| 14224 (1)| 00:03:20 | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 105 | 1093 (1)| 00:00:16 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| UET_EXTRACT_CLINICAL | 1 | 78 | 1091 (1)| 00:00:16 | | |
|* 6 | INDEX RANGE SCAN | UEI_EXTRACT_CLINICAL_IDX_I2 | 17338 | | 31 (0)| 00:00:01 | | |
| 7 | REMOTE | PF_ITEMSETCELL | 1 | 27 | 2 (0)| 00:00:01 | DEGLN~ | R->S |
|* 8 | TABLE ACCESS BY INDEX ROWID | UET_EXTRACT_CLINICAL | 1 | 78 | 1091 (1)| 00:00:16 | | |
|* 9 | INDEX RANGE SCAN | UEI_EXTRACT_CLINICAL_IDX_I2 | 17338 | | 31 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "PIC","UET_EXTRACT_CLINICAL" "UEC1" WHERE NOT EXISTS (SELECT 0 FROM
"UET_EXTRACT_CLINICAL" "UEC2" WHERE "UEC2"."FORMID"=:B1 AND "UEC2"."SUBJECTID"=:B2 AND "UEC2"."VISITID"=:B3 AND
"UEC2"."FORMINDEX"=:B4 AND "UEC2"."ITEMSETID"=:B5 AND "UEC2"."ITEMID"<>:B6) AND "UEC1"."FORMID"=:B7 AND
"UEC1"."SUBJECTID"=:B8 AND "UEC1"."VISITID"=:B9 AND "UEC1"."FORMINDEX"=:B10 AND "UEC1"."ITEMID"=:B11 AND
"UEC1"."ITEMSETID"=:B12 AND "PIC"."ITEMSETID"=:B13 AND "PIC"."ITEMSETROW"=1 AND "PIC"."ISBLANK"=0 AND
"PIC"."PREDEFINEDELEMENTID"<>0 AND "PIC"."ITEMSETID"="UEC1"."ITEMSETID"))
2 - filter("FORMINDEX">1)
3 - filter( NOT EXISTS (SELECT 0 FROM "UET_EXTRACT_CLINICAL" "UEC2" WHERE "UEC2"."FORMID"=:B1 AND
"UEC2"."SUBJECTID"=:B2 AND "UEC2"."VISITID"=:B3 AND "UEC2"."FORMINDEX"=:B4 AND "UEC2"."ITEMSETID"=:B5 AND
"UEC2"."ITEMID"<>:B6))
5 - filter("UEC1"."SUBJECTID"=:B1 AND "UEC1"."VISITID"=:B2 AND "UEC1"."FORMINDEX"=:B3 AND "UEC1"."ITEMID"=:B4 AND
"UEC1"."ITEMSETID"=:B5)
6 - access("UEC1"."FORMID"=:B1)
8 - filter("UEC2"."SUBJECTID"=:B1 AND "UEC2"."VISITID"=:B2 AND "UEC2"."FORMINDEX"=:B3 AND "UEC2"."ITEMSETID"=:B4
AND "UEC2"."ITEMID"<>:B5)
9 - access("UEC2"."FORMID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
7 - SELECT "ITEMSETID","ITEMSETROW","PREDEFINEDELEMENTID","ISBLANK" FROM "DEGNN12184101NEW"."PF_ITEMSETCELL" "PIC"
WHERE "ITEMSETID"=:1 AND "ITEMSETROW"=1 AND "ISBLANK"=0 AND "PREDEFINEDELEMENTID"<>0 AND "ITEMSETID"=:2 (accessing
'DEGLNKNN12184101NEW' )
Note
-----
- dynamic sampling used for this statement (level=2)
In the first instance, we've effectively transformed the 'exists'/'not exists' into hash joins and hash anti joins.
ie, something like:
select * from T1 where exists ( select * from T2 ... )
becomes
select * from T1 where (cols) in ( select * from T2 ... )
And similar for not exists, ie,
select * from T1 where not exists ( select * from T2 ... )
becomes
select * from T1 where (cols) not in ( select * from T2 ... )
That transformation is done on two basis:
1) the cost (ie, does the transformation appear cheaper)
2) allowability (ie, will the transformation keep results valid).
For 1, you say the data is the same, but the statistics in the plans suggest otherwise..so double check your optimizer stats in each environment.
For 2, check the nullability of the objects in the definition, because when nulls come into play, transformations might not be possible, because you could get different results, eg
SQL> create table t as select * from dba_objects;
Table created.
SQL> create table t1 as select * from t;
Table created.
SQL>
SQL> select count(*)
2 from T
3 where not exists ( select 1 from t1 where t1.object_id = t.object_id );
COUNT(*)
----------
7
SQL>
SQL> select count(*)
2 from T
3 where object_id not in ( select object_id from t1 );
COUNT(*)
----------
0