Re: Tuning Update query

In all of those three sql monitors posted by LOK, it's clear that the major chunk(~80%) of time and resources is spent while accessing table PRODUCT_TAB (which is mostly range partitioned on column PART_DT). So I doubt the index of GTT_TAB will be of any help here. And also even the estimation is showing as 8G the actual number of rows coming out to be 17M. And this i assume it must be the error in dynamic sampling. Additionally , if you see the " gc cr grant 2-way" the #sample shows them as few seconds only out of ~1hr+ of DB time.

On Mon, May 3, 2021 at 5:22 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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')
>
>
> **************************************************
> Your plan says that GTT_TAB has around 8G rows and is accessed using full
> table scan. Since your update is using a correlated query, I would try
> indexing the GTT. The other thing I notice is "gc cr grant 2-way (1)"
> event. That means that PRODUCT_TAB is being updated from another node. You
> are essentially coordinating with the other node and that is slow. Can you
> perform the operation on the same node where the table is being modified?
>
> On 5/2/21 2:53 PM, Lok P wrote:
>
> 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-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l