Hi Connor, Chris,
I have a FOR UPDATE SQL used to lock certain rows in ORDERS table but it seems to be bit slow (takes around 1 min).
I tried getting plan from dbms_xplan.display_awr. Could you please give me some lead from your past experience and I can look for the any SQL tuning stuff.
SELECT PT.ORDER_ID
FROM STAGING_001 PN
JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID,
XMLTABLE (
'hsbcEnvelope/hsbcMessageBody/pymtTran'
PASSING PN.XML_MSG
COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001',
REF_002 VARCHAR2 (50) PATH 'REF_001',
REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT,
ORDERS PT
WHERE 1 = 1
AND ( ( PMT.REF_002 IS NOT NULL
AND PMT.REF_001 IS NOT NULL
AND PMT.REF_002 = PT.REF_002
AND PT.REF_001 = PMT.REF_001
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
OR ( PMT.REF_002 IS NOT NULL
AND PMT.REF_002 = PT.REF_002
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
OR ( PMT.REF_001 IS NOT NULL
AND PT.REF_001 = PMT.REF_001
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
)
FOR UPDATE OF PT.ORDER_ID NOWAIT;
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27043 (100)| | | |
| 1 | FOR UPDATE | | | | | | | |
| 2 | BUFFER SORT | | | | | | | |
| 3 | CONCATENATION | | | | | | | |
| 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | |
| 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | |
| 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | |
| 10 | XPATH EVALUATION | | | | | | | |
| 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID |
| 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 3480 | 4652K| 249 (1)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | |
| 16 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | |
| 18 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | |
| 19 | XPATH EVALUATION | | | | | | | |
| 20 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID |
| 21 | INDEX RANGE SCAN | IDX_PT_REF_002 | 1 | | 3 (0)| 00:00:01 | | |
| 22 | NESTED LOOPS | | 1 | 1976 | 899 (1)| 00:00:01 | | |
| 23 | NESTED LOOPS | | 204 | 272K| 287 (1)| 00:00:01 | | |
| 24 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | |
| 25 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | |
| 27 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | |
| 28 | XPATH EVALUATION | | | | | | | |
| 29 | TABLE ACCESS BY GLOBAL INDEX ROWID | ORDERS | 1 | 607 | 3 (0)| 00:00:01 | ROWID | ROWID |
| 30 | INDEX UNIQUE SCAN | ORDERS_UK1 | 1 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
First up, to really help you we need to see the work the statement is doing - how many rows it processes, how this compares to the estimates, the consistent gets done, etc.
To see this information, you need to:
- Run the query with the gather_plan_statistics hint or set statistics_level = all in the session before running the query
- Get the plan with the format ALLSTATS LAST
Once you've done this, please post the updated execution plan for us to check.
That said, I doubt the where clause is doing what the author intended.
Remember:
NULL = <anything> => UNKNOWN
NULL AND <anything> => UNKNOWN
Queries never return these rows!
This means the IS NOT NULL checks are irrelevant. If the columns have a null value, the comparison of them will return null anyway.
While this doesn't really matter, removing unnecessary clauses makes the query easier to read.
The situation is more interesting with this predicate:
nvl ( pmt.ref_003, :b1 ) = pt.ref_003
It maps pmt.ref_003 to the bind variable when it's null, so the expression reduces to:
:b1 = pmt.ref_003 => :b1 = null => UNKNOWN
So all this really does is return rows where pmt.ref_003 is not null. I suspect it should have the bind first in the nvl:
NVL (:B1, PMT.REF_003) = PT.REF_003
Secondly if the conditions:
pmt.ref_002 = pt.ref_002 and pt.ref_001 = pmt.ref_001
are true, then both of the second two ORs clauses are true too. So EITHER:
- You can remove the first clause checking ( pmt.ref_002 = pt.ref_002 and pt.ref_001 = pmt.ref_001 )
- You can remove the second two OR clauses
- The conditions need rewriting so they're mutually exclusive (e.g. pmt.ref_002 = pt.ref_002 and pt.ref_001 <> pmt.ref_001, etc.)
Exactly which you need to do depends on the business logic for this process.