DB version: 19c
I have an UPDATE statement like below.
It does an UPDATE on SKUD_GMD_PICKTICKETBRDG table based on a subquery on the same table.
The below query goes for a full table scan and currently it takes around 26 seconds in production.
Application wants to improve the performance of this query.
SKUD_GMD_PICKTICKETBRDG table is 3 GB in size and has 17+million records and its stats are up-to-date.
The UPDATE uses its primary key column GMD_PICKTICKETBRDG_ID and another column called SYNBRDG_STATUS_CD in its WHERE clause. SYNBRDG_STATUS_CD column has only 3 distinct values (as shown below).
-- Some info on SKUD_GMD_PICKTICKETBRDG table
-- DESCRIBE output of SKUD_GMD_PICKTICKETBRDG table
SQL> desc D00SKUD01.SKUD_GMD_PICKTICKETBRDG
Name Null? Type
------------------------------------------- -------- ------------------------------------
1 GMD_PICKTICKETBRDG_ID NOT NULL NUMBER(19)
<snipped for readability>
15 SYNBRDG_STATUS_CD NOT NULL NUMBER(2)
-- 17 million+ records
SQL> select count(*) from D00SKUD01.SKUD_GMD_PICKTICKETBRDG;
COUNT(*)
----------
17158090
-- SYNBRDG_STATUS_CD has only 3 distinct values
SQL> select count(distinct SYNBRDG_STATUS_CD) from D00SKUD01.SKUD_GMD_PICKTICKETBRDG;
COUNT(DISTINCTSYNBRDG_STATUS_CD)
--------------------------------
3
SQL> select SYNBRDG_STATUS_CD, count(*) from D00SKUD01.SKUD_GMD_PICKTICKETBRDG group by SYNBRDG_STATUS_CD order by count(*) desc;
SYNBRDG_STATUS_CD COUNT(*)
----------------- ----------
4 13092746
1 3022923
2 1042428
--- The UPDATE statement that needs to be tuned
-- Application team said the ORDER BY GMD_PICKTICKETBRDG_ID ASC seen in the subquery is needed (for some reason)
UPDATE D00SKUD01.SKUD_GMD_PICKTICKETBRDG SET BGSTAND_TRNBR = :1, WYSR_DT = :2 , WYSR_USER = 'SKUD', ROWVERSION = ROWVERSION + 1
WHERE GMD_PICKTICKETBRDG_ID IN
(SELECT GMD_PICKTICKETBRDG_ID FROM
(SELECT GMD_PICKTICKETBRDG_ID FROM SKUD_GMD_PICKTICKETBRDG
WHERE SYNBRDG_STATUS_CD IN ('1','5')
ORDER BY GMD_PICKTICKETBRDG_ID ASC ) WHERE ROWNUM <= 1000001 );
-- The execution plan before the index creation
set lines 200 pages 3000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2063131147
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1000K| 34M| | 272K (1)| 00:00:11 |
| 1 | UPDATE | SKUD_GMD_PICKTICKETBRDG | | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1000K| 34M| 23M| 272K (1)| 00:00:11 |
| 3 | VIEW | VW_NSO_1 | 1000K| 12M| | 137K (1)| 00:00:06 |
|* 4 | COUNT STOPKEY | | | | | | |
| 5 | VIEW | | 7995K| 99M| | 137K (1)| 00:00:06 |
|* 6 | SORT ORDER BY STOPKEY| | 7995K| 76M| 153M| 137K (1)| 00:00:06 |
|* 7 | TABLE ACCESS FULL | SKUD_GMD_PICKTICKETBRDG | 7995K| 76M| | 105K (1)| 00:00:05 |
| 8 | TABLE ACCESS FULL | SKUD_GMD_PICKTICKETBRDG | 17M| 376M| | 105K (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GMD_PICKTICKETBRDG_ID"="GMD_PICKTICKETBRDG_ID")
4 - filter(ROWNUM<=1000001)
6 - filter(ROWNUM<=1000001)
7 - filter("SYNBRDG_STATUS_CD"=1 OR "SYNBRDG_STATUS_CD"=5)
23 rows selected.
-- table has the following indexes
--- Listing info only relevant to this UPDATE
col index_name for a35
col COLUMN_NAME for a35
SET LINES 200
break on index_name SKIP PAGE on INDEX_NAME SKIP 1
select idx_cols.index_name, idx.index_type, idx_cols.COLUMN_NAME, idx_cols.COLUMN_POSITION
from dba_ind_columns idx_cols inner join dba_indexes idx on (idx_cols.index_name = idx.index_name)
where idx_cols.table_name='SKUD_GMD_PICKTICKETBRDG'
and idx_cols.table_owner = 'D00SKUD01'
ORDER BY 1,4 ASC ;
-- listing only the ones used in the WHERE clause of the UPDATE
INDEX_NAME INDEX_TYPE COLUMN_NAME COLUMN_POSITION
----------------------------------- --------------------------- ----------------------------------- ---------------
SKUD_GMD_PICKTICKETBRDG_IF3 NORMAL SYNBRDG_STATUS_CD 1
INDEX_NAME INDEX_TYPE COLUMN_NAME COLUMN_POSITION
----------------------------------- --------------------------- ----------------------------------- ---------------
SKUD_GMD_PICKTICKETBRDG_PK NORMAL GMD_PICKTICKETBRDG_ID 1
8 rows selected.
So, to improve the performance, on UAT environment, where the table is 2 GB in size(1GB smaller than Prod), I created the below index. And manually gathered stats with no_invalidate => false
The optimizer has used this new index and execution time is reduced to 10.44 seconds partly because there are no live business sessions doing DML in UAT environment.
But, why do I still see a FULL TABLE SCAN in the explain plan for step 9 below ? Or is it not a 'real' full table scan ?
Any other suggestions to improve the performance of this UPDATE ?
— My new index with compression
CREATE INDEX D00SKUD01.SKUD_GMD_PICKTICKETBRDG_IDX3 ON D00SKUD01.SKUD_GMD_PICKTICKETBRDG (SYNBRDG_STATUS_CD, GMD_PICKTICKETBRDG_ID) COMPRESS 1 ONLINE PARALLEL 2 tablespace EGMD_INDEX_LS ;
alter index D00SKUD01.SKUD_GMD_PICKTICKETBRDG_IDX3 NOPARALLEL;
--BTW, Optimizer did not pick up another index I created with the column positions like below (PK column as the leading column)
(GMD_PICKTICKETBRDG_ID, SYNBRDG_STATUS_CD)
-- So, running the UPDATE (with row source stats) after the creation of SKUD_GMD_PICKTICKETBRDG_IDX3 index creation.
—
14:34:49 SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */ D00SKUD01.SKUD_GMD_PICKTICKETBRDG SET BGSTAND_TRNBR = 968492, WYSR_DT = SYSDATE , WYSR_USER = 'EGMD', ROWVERSION = ROWVERSION + 1
WHERE GMD_PICKTICKETBRDG_ID IN
14:34:53 3 (SELECT GMD_PICKTICKETBRDG_ID FROM
14:34:53 4 (SELECT GMD_PICKTICKETBRDG_ID FROM SKUD_GMD_PICKTICKETBRDG
14:34:53 5 WHERE SYNBRDG_STATUS_CD IN ('1','5')
14:34:53 6 ORDER BY GMD_PICKTICKETBRDG_ID ASC ) WHERE ROWNUM <= 1000001 );
282202 rows updated.
Elapsed: 00:00:10.44
14:35:09 SQL>
14:35:15 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last +cost'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID gxbgn8ntcv9n5, child number 0
-------------------------------------
UPDATE /*+ GATHER_PLAN_STATISTICS */ D00SKUD01.SKUD_GMD_PICKTICKETBRDG
SET BGSTAND_TRNBR = 968492, WYSR_DT = SYSDATE , WYSR_USER = 'EGMD',
ROWVERSION = ROWVERSION + 1 WHERE GMD_PICKTICKETBRDG_ID IN (SELECT
GMD_PICKTICKETBRDG_ID FROM (SELECT GMD_PICKTICKETBRDG_ID FROM
SKUD_GMD_PICKTICKETBRDG WHERE SYNBRDG_STATUS_CD IN ('1','5') ORDER BY
GMD_PICKTICKETBRDG_ID ASC ) WHERE ROWNUM <= 1000001 )
Plan hash value: 2738643585
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 84745 (100)| 0 |00:00:10.41 | 2247K| 12321 | | | |
| 1 | UPDATE | SKUD_GMD_PICKTICKETBRDG | 1 | | | 0 |00:00:10.41 | 2247K| 12321 | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 282K| 84745 (1)| 282K|00:00:02.77 | 237K| 0 | 16M| 5786K| 14M (0)|
| 3 | VIEW | VW_NSO_1 | 1 | 282K| 1746 (1)| 282K|00:00:00.15 | 675 | 0 | | | |
|* 4 | COUNT STOPKEY | | 1 | | | 282K|00:00:00.15 | 675 | 0 | | | |
| 5 | VIEW | | 1 | 282K| 1746 (1)| 282K|00:00:00.15 | 675 | 0 | | | |
|* 6 | SORT ORDER BY STOPKEY| | 1 | 282K| 1746 (1)| 282K|00:00:00.15 | 675 | 0 | 20M| 1678K| 18M (0)|
| 7 | INLIST ITERATOR | | 1 | | | 282K|00:00:00.05 | 675 | 0 | | | |
|* 8 | INDEX RANGE SCAN | SKUD_GMD_PICKTICKETBRDG_IDX3 | 2 | 282K| 669 (1)| 282K|00:00:00.03 | 675 | 0 | | | |
| 9 | TABLE ACCESS FULL | SKUD_GMD_PICKTICKETBRDG | 1 | 9561K| 64520 (1)| 9561K|00:00:01.72 | 236K| 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GMD_PICKTICKETBRDG_ID"="GMD_PICKTICKETBRDG_ID")
4 - filter(ROWNUM<=1000001)
6 - filter(ROWNUM<=1000001)
8 - access(("SYNBRDG_STATUS_CD"=1 OR "SYNBRDG_STATUS_CD"=5))
34 rows selected.
Elapsed: 00:00:00.02
14:35:18 SQL> rollback;
Rollback complete.
Elapsed: 00:00:08.91
14:35:33 SQL>