sventhan
asked on
Oracle Delete running slow
Experts -
This simple SQL delete is running for ever to complete. It has only 200K rows to delete. It did delete fine in TEST but not in PROD.
delete FROM customer a WHERE exists ( SELECT x FROM purge_table b where a.customer_number = b.customer_number)
The explain plan is using the proper indexes on the customer_number. We've deleted all the child rows.
Thanks,
sve
This simple SQL delete is running for ever to complete. It has only 200K rows to delete. It did delete fine in TEST but not in PROD.
delete FROM customer a WHERE exists ( SELECT x FROM purge_table b where a.customer_number = b.customer_number)
The explain plan is using the proper indexes on the customer_number. We've deleted all the child rows.
Thanks,
sve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I concur with 40311967. For further reading, I chanced upon changing the commit statement from immediate to batch, and will play with this when I can. http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4010.htm#i2060233
ASKER
Thanks again.
I did try the controlled delete and it takes 10 minutes for 500 rows.
It uses both the indexes and going for a fast full scan. Still looking ....
I did try the controlled delete and it takes 10 minutes for 500 rows.
It uses both the indexes and going for a fast full scan. Still looking ....
ASKER
This issue not solved.
The problem is the explain plan. It does Index (Fast) full scan on both the indexes in the join column, which is customer number.
How can I change that to Index by row id? If I can change that it would execute more faster.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
| 0 | DELETE STATEMENT | | | | | 10353 (100)| |
| 1 | DELETE | DM_CUSTOMER_DIM | | | | | |
| 2 | MERGE JOIN SEMI | | 41295 | 1532K| | 10353 (4)| 00:02:05 |
| 3 | INDEX FULL SCAN | DM_CUSTOMER_DIM_AK3 | 3812K| 112M| | 9430 (3)| 00:01:54 |
| 4 | SORT UNIQUE | | 204K| 1397K| 6424K| 923 (7)| 00:00:12 |
| 5 | INDEX FAST FULL SCAN| IDX2_CUSTOMER_PURGE | 204K| 1397K| | 174 (6)| 00:00:03 |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
Query Block Name / Object Alias (identified by operation id):
-------------------------- ---------- ---------- ---------- -----
1 - SEL$3BA1AD7C
3 - SEL$3BA1AD7C / A@DEL$1
5 - SEL$3BA1AD7C / B@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINT S
OPTIMIZER_FEATURES_ENABLE( '11.2.0.3' )
DB_VERSION('11.2.0.3')
OPT_PARAM('_replace_virtua l_columns' 'false')
OPT_PARAM('_optimizer_elim inate_filt ering_join ' 'false')
OPT_PARAM('star_transforma tion_enabl ed' 'true')
OPT_PARAM('_fix_control' '4728348:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3BA1AD7 C")
UNNEST(@"SEL$1")
OUTLINE(@"DEL$1")
OUTLINE(@"SEL$1")
INDEX(@"SEL$3BA1AD7C" "A"@"DEL$1" ("DM_CUSTOMER_DIM"."CUSTOM ER_NUMBER" ))
INDEX_FFS(@"SEL$3BA1AD7C" "B"@"SEL$1" ("DM_CUSTOMER_PURGE"."CUST OMER_NUMBE R"))
LEADING(@"SEL$3BA1AD7C" "A"@"DEL$1" "B"@"SEL$1")
USE_MERGE(@"SEL$3BA1AD7C" "B"@"SEL$1")
END_OUTLINE_DATA
*/
The problem is the explain plan. It does Index (Fast) full scan on both the indexes in the join column, which is customer number.
How can I change that to Index by row id? If I can change that it would execute more faster.
--------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------
| 0 | DELETE STATEMENT | | | | | 10353 (100)| |
| 1 | DELETE | DM_CUSTOMER_DIM | | | | | |
| 2 | MERGE JOIN SEMI | | 41295 | 1532K| | 10353 (4)| 00:02:05 |
| 3 | INDEX FULL SCAN | DM_CUSTOMER_DIM_AK3 | 3812K| 112M| | 9430 (3)| 00:01:54 |
| 4 | SORT UNIQUE | | 204K| 1397K| 6424K| 923 (7)| 00:00:12 |
| 5 | INDEX FAST FULL SCAN| IDX2_CUSTOMER_PURGE | 204K| 1397K| | 174 (6)| 00:00:03 |
--------------------------
Query Block Name / Object Alias (identified by operation id):
--------------------------
1 - SEL$3BA1AD7C
3 - SEL$3BA1AD7C / A@DEL$1
5 - SEL$3BA1AD7C / B@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINT
OPTIMIZER_FEATURES_ENABLE(
DB_VERSION('11.2.0.3')
OPT_PARAM('_replace_virtua
OPT_PARAM('_optimizer_elim
OPT_PARAM('star_transforma
OPT_PARAM('_fix_control' '4728348:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3BA1AD7
UNNEST(@"SEL$1")
OUTLINE(@"DEL$1")
OUTLINE(@"SEL$1")
INDEX(@"SEL$3BA1AD7C" "A"@"DEL$1" ("DM_CUSTOMER_DIM"."CUSTOM
INDEX_FFS(@"SEL$3BA1AD7C" "B"@"SEL$1" ("DM_CUSTOMER_PURGE"."CUST
LEADING(@"SEL$3BA1AD7C" "A"@"DEL$1" "B"@"SEL$1")
USE_MERGE(@"SEL$3BA1AD7C" "B"@"SEL$1")
END_OUTLINE_DATA
*/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SD -
Thanks for looking and your time.
The indexes file is attached.
Looking at the 10046 trace file in PROD, the execution spent almost 5 minutes in the recursive query. This is not happening in TEST. This table DM_CUSTOMER_PRODUCT_SEGM_F ACT has a foreign-key constraint to DM_CUSTOMER_DIM through column DM_CUSTOMER_DKEY.
select /*+ all_rows */ count(1) from "DM"."DM_CUSTOMER_PRODUCT_ SEGM_FACT" where "DM_CUSTOMER_DKEY" = :1
END OF STMT
PARSE #4574288328:c=94681,e=1633 996,p=365, cr=1304,cu =14,mis=1, r=0,dep=1, og=1,plh=0 ,tim=97283 574143143
.
.
.
EXEC #4574288328:c=2052,e=30604 ,p=3,cr=12 ,cu=0,mis= 1,r=0,dep= 1,og=1,plh =279538023 3,tim=9728 3574173917
WAIT #4574288328: nam='Disk file operations I/O' ela= 64 FileOperation=2 fileno=588 filetype=2 obj#=3475408 tim=97283574174103
WAIT #4574288328: nam='db file sequential read' ela= 1188 file#=588 block#=543379 blocks=1 obj#=3475408 tim=97283574175314
WAIT #4574288328: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=464 filetype=2 obj#=3475408 tim=97283574175426
WAIT #4574288328: nam='db file sequential read' ela= 21325 file#=464 block#=652103 blocks=1 obj#=3475408 tim=97283574196770
.
.
.
WAIT #4574288328: nam='db file sequential read' ela= 525 file#=414 block#=3335987 blocks=1 obj#=3475408 tim=97283859585625
WAIT #4574288328: nam='db file sequential read' ela= 968 file#=414 block#=3336003 blocks=1 obj#=3475408 tim=97283859586638
WAIT #4574288328: nam='db file sequential read' ela= 1538 file#=414 block#=3336020 blocks=1 obj#=3475408 tim=97283859588272
FETCH #4574288328:c=8277712,e=28 5414391,p= 100083,cr= 100083,cu= 0,mis=0,r= 1,dep=1,og =1,plh=279 5380233,ti m=97283859 588350 <<<--- 285 s. = 4.75 min.
STAT #4574288328 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=100083 pr=100083 pw=0 time=285414386 us)'
STAT #4574288328 id=2 cnt=0 pid=1 pos=1 obj=3475408 op='INDEX SKIP SCAN IX1_CUSTOMER_PROD_SEGM_FAC T (cr=100083 pr=100083 pw=0 time=285414368 us cost=845 size=5700 card=950)'
CLOSE #4574288328:c=3,e=4,dep=1, type=3,tim =972838595 88526
.
indexes.txt
Thanks for looking and your time.
The indexes file is attached.
Looking at the 10046 trace file in PROD, the execution spent almost 5 minutes in the recursive query. This is not happening in TEST. This table DM_CUSTOMER_PRODUCT_SEGM_F
select /*+ all_rows */ count(1) from "DM"."DM_CUSTOMER_PRODUCT_
END OF STMT
PARSE #4574288328:c=94681,e=1633
.
.
.
EXEC #4574288328:c=2052,e=30604
WAIT #4574288328: nam='Disk file operations I/O' ela= 64 FileOperation=2 fileno=588 filetype=2 obj#=3475408 tim=97283574174103
WAIT #4574288328: nam='db file sequential read' ela= 1188 file#=588 block#=543379 blocks=1 obj#=3475408 tim=97283574175314
WAIT #4574288328: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=464 filetype=2 obj#=3475408 tim=97283574175426
WAIT #4574288328: nam='db file sequential read' ela= 21325 file#=464 block#=652103 blocks=1 obj#=3475408 tim=97283574196770
.
.
.
WAIT #4574288328: nam='db file sequential read' ela= 525 file#=414 block#=3335987 blocks=1 obj#=3475408 tim=97283859585625
WAIT #4574288328: nam='db file sequential read' ela= 968 file#=414 block#=3336003 blocks=1 obj#=3475408 tim=97283859586638
WAIT #4574288328: nam='db file sequential read' ela= 1538 file#=414 block#=3336020 blocks=1 obj#=3475408 tim=97283859588272
FETCH #4574288328:c=8277712,e=28
STAT #4574288328 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=100083 pr=100083 pw=0 time=285414386 us)'
STAT #4574288328 id=2 cnt=0 pid=1 pos=1 obj=3475408 op='INDEX SKIP SCAN IX1_CUSTOMER_PROD_SEGM_FAC
CLOSE #4574288328:c=3,e=4,dep=1,
.
indexes.txt
ASKER
Its strange. We just refresh the TEST from PROD. This delete ran fine in TEST in minutes using the same plan.
But in PROD it hung, after little the tuning/altering I got the delete moving but it is still slow.
We found recursive SQL from one of the referenced table mentioned above which took most of the time. After disable the FK reference I could be able to complete the Delete in minutes.
Thanks for all your help.
But in PROD it hung, after little the tuning/altering I got the delete moving but it is still slow.
We found recursive SQL from one of the referenced table mentioned above which took most of the time. After disable the FK reference I could be able to complete the Delete in minutes.
Thanks for all your help.
ASKER
Thanks for all your help.
ASKER
If I run a delete in a small group it works. This table has multiple constraints and relationships. The STAT is up to date.
The Select count(*) runs fine. The table has 5 million rows, but I noticed it reads 500 mil rows from the buffer when delete runs.
I'll run this again and will get you the details.
Thanks Wasim.