Re: Tuning Update query

How about indexing GTT_TAB?

SQL Plan Monitoring Details (Plan Hash Value=1406665007)
====================================================================================================================================================================================================
| Id |               Operation                |          Name          |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |            Activity Detail             |
|    |                                        |                        | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |              (# samples)               |
====================================================================================================================================================================================================
|  0 | UPDATE STATEMENT                       |                        |         |      |      1235 |  +2153 |     1 |        0 |      |       |     0.07 | Cpu (1)                                |
|    |                                        |                        |         |      |           |        |       |          |      |       |          | reliable message (2)                   |
|  1 |   UPDATE                               | GTT_TAB                |         |      |      4044 |     +1 |     1 |        0 |      |       |    10.62 | Cpu (429)                              |
|  2 |    TABLE ACCESS STORAGE FULL           | GTT_TAB                |      8G | 381K |      4042 |     +3 |     1 |      17M | 4467 |   4GB |     0.72 | Cpu (20)                               |
|    |                                        |                        |         |      |           |        |       |          |      |       |          | cell multiblock physical read (9)      |
|  3 |    FILTER                              |                        |         |      |      4042 |     +3 |   17M |       8M |      |       |     0.07 | Cpu (3)                                |
|  4 |     PARTITION RANGE SINGLE             |                        |       1 |   13 |      4042 |     +3 |    8M |       8M |      |       |     0.12 | Cpu (5)                                |
|  5 |      TABLE ACCESS BY LOCAL INDEX ROWID | PRODUCT_TAB            |       1 |   13 |      4043 |     +2 |    8M |       8M |   6M |  45GB |    55.79 | gc cr grant 2-way (1)                  |
|    |                                        |                        |         |      |           |        |       |          |      |       |          | Cpu (292)                              |
|    |                                        |                        |         |      |           |        |       |          |      |       |          | cell single block physical read (1961) |
|  6 |       INDEX RANGE SCAN                 | PROD_TAB_IX1           |       1 |    4 |      4042 |     +3 |    8M |       8M |   3M |  25GB |    32.60 | Cpu (218)                              |
|    |                                        |                        |         |      |           |        |       |          |      |       |          | cell single block physical read (1099) |
====================================================================================================================================================================================================

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(:B1='Y')
   5 - filter("P"."PART_DT"=:B1)
   6 - access("P"."TID"=:B1 AND "P"."CODE"='XX')

  
 ************************************************** 

Hello, We have a plsql procedure in which it  updates multiple columns of the global temporary table after loading it in different steps. It's updated for different filter criteria and each of these UPDATES are running for hours even if they are simple ones. I am trying to see if it's possible to do it in an efficient fashion by combining all different updates into one UPDATE statement or any other possible method? One initial thought was to do it as part of the INSERT itself rather than updating it through multiple statements at the later stage , but then I see the data load/INSERT is happening for multiple if/else conditions which may not be straight enough.

Attached is the sql monitor for few of the long running UPDATES. And i am seeing this is not really spending time for updating rows but rather in finding the eligible rows for update i.e. the SELECT part of it is actually taking time. So wanted to understand how these UPDATES can be done faster?

Its version 11.2.0.4 of Oracle Exadata-X5.

Regards

Lok

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com