Hello All,
I am facing a performance issue with a UPDATE statement on one of our database. The UPDATE statement is written with the intention of updating only (up to) 100 records in the table that contains more than 2 million records. The UPDATE statement does enormous amount of I/Os (many thousands per row). This happens mainly due to the table in question being accessed using full table scan, even though an indexed access would be beneficial (IMO)
I have so far gone through following references which, I believe, are similar to my case but I am not really sure if I can reduce the amount of I/O.
936895
http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/
http://jonathanlewis.wordpress.com/2010/09/30/rownum-effects-2/
http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/
http://afatkulin.blogspot.co.uk/2009/01/update-and-rownum-oddity.html
http://dioncho.wordpress.com/2009/01/30/89/
Can somebody help please?
Here is my attempt (test case) to simulate the issue (although I was not able to get the indexed access here)
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create table dtl (dtlid number(10), process_ind varchar2(10) not null, process_date date not null, hdrid number(10), dpad varchar2(100), constraint dtl_pk primary key (dtlid)) ;
Table created.
SQL> create index dtl_idx1 on dtl(process_ind, process_date) nologging ;
Index created.
SQL> create table hdr (hdrid number(10), hdr_ind number(2) not null, hpad varchar2(100), constraint hdr_pk primary key (hdrid)) ;
Table created.
SQL> insert into hdr select level, case when mod(level, 3) = 0 then 0 when mod(level, 7) = 0 then 2 else 6 end, dbms_random.string('a', 100) from dual connect by level <= 10000 ;
10000 rows created.
SQL> insert into dtl select rownum, ind, pdate, hdrid, hpad from (select level, 'PROCESSED' ind, (sysdate - dbms_random.value(1, 100)) pdate, case mod(level, 100) when 13 then null else level end hdrid, dbms_random.string('a', 100) hpad from dual connect by level <= 10000 UNION ALL select level, 'NEW', (sysdate + dbms_random.value(1, 100)), case mod(level, 100) when 13 then null else level end, dbms_random.string('a', 100) from dual connect by level <= 50000) ;
60000 rows created.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user, 'DTL', cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'HDR', cascade=>true);
PL/SQL procedure successfully completed.
Following are 3 versions of the query that I believe are functionally equivalent:
SQL> set autot traceon
SQL> select dtlid from (select dtlid from (select dtlid, process_date from dtl where hdrid is null and process_date < (sysdate + 30) and process_ind = 'NEW' UNION ALL select dtl.dtlid, dtl.process_date from dtl, hdr where dtl.hdrid = hdr.hdrid and dtl.process_date < (sysdate + 30) and dtl.process_ind = 'NEW' and hdr_ind in (0, 2)) order by dtlid, process_date) where rownum <= 10 ;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3926759265
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 54 (2)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 21 | 273 | 54 (2)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 21 | 462 | 54 (2)| 00:00:01 |
| 4 | VIEW | | 21 | 462 | 53 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | DTL | 11 | 264 | 15 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 10 | 310 | 38 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DTL | 29 | 696 | 32 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DTL_IDX1 | | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| HDR | 1 | 7 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | HDR_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
9 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
11 - access("DTL"."HDRID"="HDR"."HDRID")
Statistics
----------------------------------------------------------
704 recursive calls
0 db block gets
33741 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select dtlid from (select dtlid from (select dtlid, process_date, process_ind from dtl where hdrid is null UNION ALL select dtl.dtlid, dtl.process_date, dtl.process_ind from dtl, hdr where dtl.hdrid = hdr.hdrid and hdr_ind in (0, 2)) where process_date < (sysdate + 30) and process_ind = 'NEW' order by dtlid, process_date) where rownum <= 10 ;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3926759265
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 57 (2)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 22 | 286 | 57 (2)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 22 | 638 | 57 (2)| 00:00:01 |
| 4 | VIEW | | 22 | 638 | 56 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | DTL | 11 | 264 | 15 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 11 | 341 | 41 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DTL | 32 | 768 | 35 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DTL_IDX1 | | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| HDR | 1 | 7 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | HDR_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
9 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
11 - access("DTL"."HDRID"="HDR"."HDRID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33606 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select dtlid from (select dtl.dtlid, dtl.process_date from dtl, hdr where (dtl.hdrid = hdr.hdrid OR dtl.hdrid is null) and dtl.process_date < (sysdate + 30) and dtl.process_ind = 'NEW' and hdr_ind in (0, 2) order by dtlid, process_date) where rownum <= 10 ;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4216079878
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 11696 (1)| 00:02:21 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 392 | 5096 | 11696 (1)| 00:02:21 |
|* 3 | SORT ORDER BY STOPKEY | | 392 | 12152 | 11696 (1)| 00:02:21 |
| 4 | CONCATENATION | | | | | |
| 5 | MERGE JOIN CARTESIAN| | 390 | 12090 | 293 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL | DTL | 195 | 4680 | 249 (1)| 00:00:03 |
| 7 | BUFFER SORT | | 2 | 14 | 44 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | HDR | 2 | 14 | 0 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 2 | 62 | 252 (1)| 00:00:04 |
|* 10 | TABLE ACCESS FULL | HDR | 2 | 14 | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | DTL | 19308 | 452K| 249 (1)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
6 - filter("DTL"."HDRID" IS NULL AND "DTL"."PROCESS_IND"='NEW' AND
"DTL"."PROCESS_DATE"<SYSDATE@!+30)
8 - filter("HDR_IND"=0 OR "HDR_IND"=2)
9 - access("DTL"."HDRID"="HDR"."HDRID")
10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
11 - filter("DTL"."PROCESS_IND"='NEW' AND
"DTL"."PROCESS_DATE"<SYSDATE@!+30 AND LNNVL("DTL"."HDRID" IS NULL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2622 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
And, following are the actual UPDATE statements corresponding to 3 versions of SELECT above
SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (select dtlid from (select dtlid from (select dtlid, process_date from dtl where hdrid is null and process_date < (sysdate + 30) and process_ind = 'NEW' UNION ALL select dtl.dtlid, dtl.process_date from dtl, hdr where dtl.hdrid = hdr.hdrid and dtl.process_date < (sysdate + 30) and dtl.process_ind = 'NEW' and hdr_ind in (0, 2)) order by dtlid, process_date) where rownum <= 10) ;
10 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1321972013
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 32 | 557 (1)| 00:00:07 |
| 1 | UPDATE | DTL | | | | |
| 2 | NESTED LOOPS | | 1 | 32 | 557 (1)| 00:00:07 |
| 3 | VIEW | VW_NSO_1 | 10 | 130 | 555 (1)| 00:00:07 |
| 4 | SORT UNIQUE | | 1 | 130 | | |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 7055 | 91715 | 555 (1)| 00:00:07 |
|* 7 | SORT ORDER BY STOPKEY| | 7055 | 151K| 555 (1)| 00:00:07 |
| 8 | VIEW | | 7055 | 151K| 554 (1)| 00:00:07 |
| 9 | UNION-ALL | | | | | |
|* 10 | TABLE ACCESS FULL | DTL | 195 | 4680 | 249 (1)| 00:00:03 |
|* 11 | HASH JOIN | | 6860 | 207K| 305 (1)| 00:00:04 |
|* 12 | TABLE ACCESS FULL| HDR | 6667 | 46669 | 55 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| DTL | 19503 | 457K| 249 (1)| 00:00:03 |
|* 14 | INDEX UNIQUE SCAN | DTL_PK | 1 | 19 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM<=10)
7 - filter(ROWNUM<=10)
10 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND
"PROCESS_DATE"<SYSDATE@!+30)
11 - access("DTL"."HDRID"="HDR"."HDRID")
12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
13 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
14 - access("DTLID"="$nso_col_1")
Statistics
----------------------------------------------------------
663 recursive calls
52 db block gets
2593 consistent gets
0 physical reads
7688 redo size
554 bytes sent via SQL*Net to client
886 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> rollback ;
Rollback complete.
SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (select dtlid from (select dtlid from (select dtlid, process_date, process_ind from dtl where hdrid is null UNION ALL select dtl.dtlid, dtl.process_date, dtl.process_ind from dtl, hdr where dtl.hdrid = hdr.hdrid and hdr_ind in (0, 2)) where process_date < (sysdate + 30) and process_ind = 'NEW' order by dtlid, process_date) where rownum <= 10) ;
10 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1321972013
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 32 | | 616 (1)| 00:00:08 |
| 1 | UPDATE | DTL | | | | | |
| 2 | NESTED LOOPS | | 1 | 32 | | 616 (1)| 00:00:08 |
| 3 | VIEW | VW_NSO_1 | 10 | 130 | | 614 (1)| 00:00:08 |
| 4 | SORT UNIQUE | | 1 | 130 | | | |
|* 5 | COUNT STOPKEY | | | | | | |
| 6 | VIEW | | 7055 | 91715 | | 614 (1)| 00:00:08 |
|* 7 | SORT ORDER BY STOPKEY| | 7055 | 199K| 288K| 614 (1)| 00:00:08 |
| 8 | VIEW | | 7055 | 199K| | 554 (1)| 00:00:07 |
| 9 | UNION-ALL | | | | | | |
|* 10 | TABLE ACCESS FULL | DTL | 195 | 4680 | | 249 (1)| 00:00:03 |
|* 11 | HASH JOIN | | 6860 | 207K| | 305 (1)| 00:00:04 |
|* 12 | TABLE ACCESS FULL| HDR | 6667 | 46669 | | 55 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| DTL | 19504 | 457K| | 249 (1)| 00:00:03 |
|* 14 | INDEX UNIQUE SCAN | DTL_PK | 1 | 19 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM<=10)
7 - filter(ROWNUM<=10)
10 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
11 - access("DTL"."HDRID"="HDR"."HDRID")
12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
13 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
14 - access("DTLID"="$nso_col_1")
Statistics
----------------------------------------------------------
366 recursive calls
52 db block gets
2530 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
852 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> rollback ;
Rollback complete.
SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (select dtlid from (select dtl.dtlid, dtl.process_date from dtl, hdr where (dtl.hdrid = hdr.hdrid OR dtl.hdrid is null) and dtl.process_date < (sysdate + 30) and dtl.process_ind = 'NEW' and hdr_ind in (0, 2) order by dtlid, process_date) where rownum <= 10) ;
3 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 201283143
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10 | 320 | 22156 (1)| 00:04:26 |
| 1 | UPDATE | DTL | | | | |
| 2 | NESTED LOOPS | | 10 | 320 | 22156 (1)| 00:04:26 |
| 3 | VIEW | VW_NSO_1 | 10 | 130 | 22145 (1)| 00:04:26 |
| 4 | SORT UNIQUE | | 10 | 130 | | |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 1307K| 16M| 22145 (1)| 00:04:26 |
|* 7 | SORT ORDER BY STOPKEY | | 1307K| 38M| 22145 (1)| 00:04:26 |
| 8 | CONCATENATION | | | | | |
| 9 | MERGE JOIN CARTESIAN| | 1300K| 38M| 10688 (1)| 00:02:09 |
|* 10 | TABLE ACCESS FULL | DTL | 195 | 4680 | 249 (1)| 00:00:03 |
| 11 | BUFFER SORT | | 6667 | 46669 | 10439 (1)| 00:02:06 |
|* 12 | TABLE ACCESS FULL | HDR | 6667 | 46669 | 54 (2)| 00:00:01 |
|* 13 | HASH JOIN | | 6857 | 207K| 305 (1)| 00:00:04 |
|* 14 | TABLE ACCESS FULL | HDR | 6667 | 46669 | 55 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | DTL | 19309 | 452K| 249 (1)| 00:00:03 |
|* 16 | INDEX UNIQUE SCAN | DTL_PK | 1 | 19 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM<=10)
7 - filter(ROWNUM<=10)
10 - filter("DTL"."HDRID" IS NULL AND "DTL"."PROCESS_IND"='NEW' AND
"DTL"."PROCESS_DATE"<SYSDATE@!+30)
12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
13 - access("DTL"."HDRID"="HDR"."HDRID")
14 - filter("HDR_IND"=0 OR "HDR_IND"=2)
15 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30
AND LNNVL("DTL"."HDRID" IS NULL))
16 - access("DTLID"="$nso_col_1")
Statistics
----------------------------------------------------------
316 recursive calls
17 db block gets
2707 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
763 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> rollback ;
Rollback complete.