Skip to Main Content
  • Questions
  • Select for update statement too slow

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 19, 2020 - 10:21 am UTC

Answered by: Chris Saxon - Last updated: October 21, 2020 - 1:37 am UTC

Category: SQL - Version: 12.1.0.1

Viewed 100+ times

You Asked

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)

and we said...

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.

and you rated our response

  (1 rating)

Reviews

October 21, 2020 - 12:23 am UTC

Reviewer: A reader

Apart than what mention OP is mixing ANSI and legacy join.
Connor McDonald

Followup  

October 21, 2020 - 1:37 am UTC

yes...thats awkward :-)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.