Update statement is slow with sum and nvl function

Question

I have a procedure , in which a table's columns is being filled using sum and nvl functions on other tables' column. These update queries are slow and which is making overall Proc slow.One of such update query is below:

 UPDATE t_final wp
    SET PCT =
        (
        SELECT SUM(NVL(pct,0))
        FROM t_overall
        WHERE rid  = 9
        AND rtype  = 1
        AND sid = 'r12'
        AND pid = 21
        AND mid   = wp.mid
        )
    WHERE rid  = 9 AND rtype  = 1 AND sid = 'r12' AND  pid = 21;

Here t_overall and t_final , both the tables do not have any indexes as they have multiple updates in the overall procedure. Number of records for table t_final is around 8500 and for table t_overall is around 13000. Is there any other way , I can write above query in more optimized way?

Edit 1: Here SUM(NVL(pct,0)) function is first replacing null to 0 in 'pct' column of table t_overall and then adds all pct values using sum function and updates pct column of the table t_final depending on the criteria.

Explain plan returns below:

OPERATION                OBJECT_NAME   CARDINALITY  COST
UPDATE STATEMENT                               6     424
 UPDATE                     T_FINAL
   TABLE ACCESS(FULL)       T_FINAL            6     238
   .  Filter Predicates
   .   AND
   .   RTYPE=6
   .   SID='R12'
   .   RID=9    
   .   PID=21
   SORT(AGGREGATE)                             1
    TABLE ACCESS(FULL)      T_OVERALL          1      30
       Filter Predicates
         AND
         MID-:B1
         RTYPE=6
         SID='R12'
         RID=9  
         PID=21

Updated number of rows are around 2200

Edit 2: I have run update query with hint /*+ gather_plan_statistics */ as below:

 ALTER session SET statistics_level=ALL;
 UPDATE /*+ gather_plan_statistics */ t_final wp
        SET PCT =
            (
            SELECT SUM(NVL(pct,0))
            FROM t_overall
            WHERE rid  = 9
            AND rtype  = 1
            AND sid = 'r12'
            AND pid = 21
            AND mid   = wp.mid
            )
        WHERE rid  = 9 AND rtype  = 1 AND sid = 'r12' AND  pid = 21;

 select * from
    table (dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));

The result is:

SQL_ID  gypnfv5nzurb0, child number 1
-------------------------------------
select child_number from v$sql   where sql_id = :1     order by 
child_number

Plan hash value: 4252345203

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                           |      1 |        |      2 |00:00:00.01 |       |       |          |
|   1 |  SORT ORDER BY           |                           |      1 |      1 |      2 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|*  2 |   FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |      1 |      1 |      2 |00:00:00.01 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("KGLOBT03"=:1 AND "INST_ID"=USERENV('INSTANCE')))

Thank you.

@user7294900 , yes pct is a column , it's name is different in the table. Is it confusing ? If it is , i will change the name. — Khushi, Jul 03 '18 at 06:38
@Khushi Does it take time to execute your sub-query? And why not add indexes to your tables? — Jacob, Jul 03 '18 at 06:53
Have you considered using a materialised view to keep a running total of the pct? You can then join the view back to the t_final (use indexes!) and let the DB do the work as it happens. — LoztInSpace, Jul 03 '18 at 07:05
Any performance impact of NVL is bound to drown in the full scans required as there are no indexes. The tables are very small, so perhaps that is ok, but I don't think you can improve performance much here without adding an index or two. The optimizer has an easy job when the only possible plan is "let's do a full scan", but it won't be fast. — ewramner, Jul 03 '18 at 07:26
The first step is to understand how the statement is executed. Post the **execution plan**. See [here](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database/34975420#34975420) some hints how to do it. The most probable cause of the slow performance is, that you repeatedly calculates the subquery for each row if the main table and the subquery makes **FULL SCAN**. Post the updated row count and the (avg) count of rows in the subquery and the total rows in the subquery table. — Marmite Bomber, Jul 03 '18 at 07:27
Please post the execution plan - run `explain plan for update ... rest your's update command` then run `select * from table(dbms_xplan.display)` and append a result of last command to the question. — krokodilko, Jul 03 '18 at 07:40
@All, Both the tables do not have indexes as these tables involves insertion and update also in the same amount. I fear, if i add indexes , it would result in slowness of other queries — Khushi, Jul 03 '18 at 08:34
13000 rows is too less for something to run this slow. Are you sure there aren't any other transactions happening on the table simultaneously from other sessions? — Kaushik Nayak, Jul 03 '18 at 08:34
@KaushikNayak, i am running proc from the db itself and no other sessions are active. Is it possible to have join rather than subquery for the given query? — Khushi, Jul 03 '18 at 08:35

score 0 · Answer 1 · answered Jul 03 '18 at 17:39

You did not provide enough information to make unique diagnose, so I can only hint you how to troubleshoot your query.

Here is my setup simulation your data

create table t_final as
select  rownum  mid, 8 + mod(rownum,4) rid,  1 rtype, 'r12' sid, 21 pid, 0 pct from dual
connect by level <= 8800;

drop table T_OVERALL;
create table T_OVERALL as
select  mod(rownum,8800) mid, 8 + mod(rownum,4) rid,  1 rtype, 'r12' sid, 21 pid, rownum pct from dual
connect by level <= 13000;

Now I run the query activating the statistics gathering to see what the query is doing:

SQL> UPDATE /*+ gather_plan_statistics */ t_final wp
  2      SET PCT =
  3          (
  4          SELECT SUM(NVL(pct,0))
  5          FROM t_overall
  6          WHERE rid  = 9
  7          AND rtype  = 1
  8          AND sid = 'r12'
  9          AND pid = 21
 10          AND mid   = wp.mid
 11          )
 12      WHERE rid  = 9 AND rtype  = 1 AND sid = 'r12' AND  pid = 21;

2200 rows updated.

Elapsed: 00:00:00.97

So nearly one second elapsed time, which is is slow if you have lot of such updates. To see the cause we display the cursor and the statsitics (hist is possible using the hint /*+ gather_plan_statistics */)

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID  3ctaz5gvksb54, child number 0
-------------------------------------
UPDATE /*+ gather_plan_statistics */ t_final wp     SET PCT =         (
        SELECT SUM(NVL(pct,0))         FROM t_overall         WHERE rid
 = 9         AND rtype  = 1         AND sid = 'r12'         AND pid =
21         AND mid   = wp.mid         )     WHERE rid  = 9 AND rtype  =
1 AND sid = 'r12' AND  pid = 21

Plan hash value: 1255260726

-------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |           |      1 |        |      0 |00:00:00.96 |     116K|
|   1 |  UPDATE             | T_FINAL   |      1 |        |      0 |00:00:00.96 |     116K|
|*  2 |   TABLE ACCESS FULL | T_FINAL   |      1 |   2200 |   2200 |00:00:00.01 |      33 |
|   3 |   SORT AGGREGATE    |           |   2200 |      1 |   2200 |00:00:00.92 |     112K|
|*  4 |    TABLE ACCESS FULL| T_OVERALL |   2200 |     33 |   3250 |00:00:00.85 |     112K|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------


   2 - filter(("RID"=9 AND "RTYPE"=1 AND "PID"=21 AND "SID"='r12'))
   4 - filter(("RID"=9 AND "RTYPE"=1 AND "PID"=21 AND "MID"=:B1 AND "SID"='r12'))

So you see the main problem was in the FULL TABLE SCAN on T_OVERALL which was called 2200 times (columns Starts, line 4).

A remedy could provide an Index based on the filter predicate of line 4:

create index T_OVERALL_IDX on T_OVERALL(mid, rid, rtype, sid, pid);

On the same data now I got:

Elapsed: 00:00:00.05

with the changed plan using now 2200 INDEX RANGE SCANs

--------------------------------------------------------------------------------------------------------- 
| Id  | Operation                     | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |               |      1 |        |      0 |00:00:00.05 |   10272 |
|   1 |  UPDATE                       | T_FINAL       |      1 |        |      0 |00:00:00.05 |   10272 |
|*  2 |   TABLE ACCESS FULL           | T_FINAL       |      1 |   2200 |   2200 |00:00:00.01 |      33 |
|   3 |   SORT AGGREGATE              |               |   2200 |      1 |   2200 |00:00:00.01 |    5755 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T_OVERALL     |   2200 |     33 |   3250 |00:00:00.01 |    5755 |
|*  5 |     INDEX RANGE SCAN          | T_OVERALL_IDX |   2200 |      1 |   3250 |00:00:00.01 |    2505 |
---------------------------------------------------------------------------------------------------------

Simple recheck the same approach with your data, if you observe a different behavior feel free to post it.

Hello Marmite, thank you for your suggestion.I observed that after adding index , execution time is reduced for that particular query.But at the same time, there is another insert statement in stored proc(where t_final is getting filled from t_overall based on some more criteria) so after adding index that query's runtime increased. so overall no impact on performance by adding index. — Khushi, Jul 04 '18 at 05:12
@Khushi So are you telling that parallel to your `UPDATE`there is an other `INSERT` process, which is possible taking some time to **commit** the inserted row (mayby waiting on user interaction?). This will explain the *slowness* with your limited data. Run the `UPDATE` with **10046 trace** and see where is your *wait time* going. — Marmite Bomber, Jul 04 '18 at 07:09
Hello Marmite, insert and update statements are a part of Procedure.so first insertion takes place and after some steps update take place.I will check update with 10046 trace.Thank you. — Khushi, Jul 04 '18 at 09:19
Hello Marmite, I have not taken 10046 trace yet as I need to check where dump file will be created and on it's usage.could you please tell me one thing , if i create index with 5 column as suggested by you above and there is 1 type of query which has more or less of those 5 columns in predicate in the Procedure and second type of query has join with other table. So my question is Should I create any index then , while having different predicate columns in different queries or if joins are there with other tables? Please let me know if u get it, otherwise i will post an example.Thanks a lot. — Khushi, Jul 05 '18 at 05:11
@Khushi start simple - first find *where* your proble is. How long take the update? It is executed once or thousends times? Run the `UPDATE`isoletad with the hint `gather_plan_statistics` and se where th eelapsed time is going. Advice to index is not possible without knowing your data distribution. E.g. if the column `mid` is unique or very selective, it will be fine to define index only on it. — Marmite Bomber, Jul 05 '18 at 07:09
Hello Marmite, I executed query with hint and added results in my questions. I see that it is not taking much time?cud u get anything from stats which points out that the query is slow? One question here , is it possible to add this hint in procedure and see the result? while update statement would be somewhere in middle of procedure. Thanks. — Khushi, Jul 06 '18 at 07:48
@Khushi you must run the statement in SQLPlus to be able to use teh `LAST` option. in SQL Developer etc. you get statistics for other statement (see the SQL Text). But aparently the `UPDATE` is not your problem - to get a big picture **run teh 10046 trace**. `alter session set events '10046 trace name context forever, level 12';`, your DBA will assist you to get the trace file. — Marmite Bomber, Jul 06 '18 at 08:57
Hello Marmite, I have got the 10046 trace file and it has so much information. I am trying to read it. thanks. — Khushi, Jul 19 '18 at 09:47

Update statement is slow with sum and nvl function

1 Answers1