Re: Update query performance with some buggy behavior

Thank you so much Andy and Lok.

On Mon, Nov 22, 2021 at 12:42 AM Lok P <loknath.73_at_gmail.com> wrote:

> I think you should fix the buggy code first as Andrew suggested as that is
> not anyway as per your business logic. However, there was a bug in 11.2 wrt
> cell smart scan disabled for DML. So mostly you are not seeing cell offload
> because of that. And fix for that would be either force the smartscan using
> _serial_direct_read to force true before this update or make it bulk update
> so that this querying part can be converted to cursor select query so that
> you will be benefited of cell smartscan automatically.
>
> On Sun, 21 Nov 2021, 2:40 am Pap, <oracle.developer35_at_gmail.com> wrote:
>
>> Thank you so much Andy.
>>
>> Never thought of this , that RID was supposed to come from the FROM
>> section of the query only. but as in this case , it's not available there
>> so oracle is doing something like ( GTT_TAB.RID = GTT_TAB.RID). So
>> basically in current query it must be updating all the rows in the GTT_TAB
>> which is definitely not the logic as i am seeing the comment section for
>> that query stating its there to update top ~20 RID'S only based on
>> difference in run time (i.e. je_ets-je_sts desc).
>>
>> With respect to the amount of CPU time consumption in the FTS of table
>> MRQ(and unavailability of any cell offloading percentage in sql monitor in
>> that step) , is that expected. I was trying to understand , if after
>> the fix, it will scan the table ~20 times , then if that behaviour is
>> expected one or still any issue with that?
>>
>>
>> On Sun, Nov 21, 2021 at 1:58 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>>
>>> " There is no such column as 'RID' in table MRQ, but this UPDATE runs
>>> without error in production"
>>> Then it is probably not doing what was probably intended and you should
>>> fix that before even considering making it faster.
>>>
>>> You should always specify where columns are supposed to be coming from.
>>> For:
>>> UPDATE GTT_TAB
>>> SET PRT = 999
>>> WHERE RID IN
>>> (SELECT RID..
>>> If RID cannot be found in that subquery, it will be treated as if you
>>> meant it to come from GTT_TAB. This is probably not what you wanted.
>>> Follow best practice and specify where the columns are coming from, do:
>>> UPDATE GTT_TAB
>>> SET GTT_TAB.PRT = 999
>>> WHERE GTT_TAB.RID IN
>>> (SELECT sq.RID
>>> FROM ( SELECT mrq.rid -- I've also removed the select *
>>> here
>>> FROM MRQ
>>> WHERE mrq.R_DATE = TRUNC ( :B1) - 2
>>> AND mrq.RP_STX = 'XX'
>>> AND mrq.MRF_NM = 'YYY'
>>> AND mrq.FR_NM NOT IN
>>> (SELECT tlsv.L_VL
>>> FROM TLSV
>>> WHERE tlsv.L_NM = 'XXXXXXX')
>>> ORDER BY mrq.JE_ETS - mrq.JE_STS DESC) sq
>>> WHERE ROWNUM <= 20)
>>>
>>> You will get the expected error before it tries to execute that subquery
>>> for every single row in GTT_TAB.
>>>
>>> Thanks,
>>> Andy
>>>
>>> On Sat, 20 Nov 2021 at 20:14, Pap <oracle.developer35_at_gmail.com> wrote:
>>>
>>>> Hi, We have the below update query running for 4-5hrs on 11.2.0.4
>>>> version Oracle database impacting one customer process. and it seems almost
>>>> all the time has been spent on step- 11. And also that step is getting full
>>>> scanned and executed ~13K times. It's updating a global temporary
>>>> table-GTT_TAB. This plan line id-11 is all on CPU, not able to understand
>>>> why?
>>>>
>>>> Another odd behaviour related to this:- There is no such column as
>>>> 'RID' in table MRQ, but this UPDATE runs without error in production. When
>>>> we try to execute the SELECT part of the query(i.e. SELECT RID...) It's
>>>> failing with "ORA-00904: "RID": invalid identifier" . So I wanted to
>>>> understand , how did that Update happen/succeed then? I have captured the
>>>> sql monitor for the same as below from prod. In any case, I was expecting
>>>> the plan_line_id -7 ( SORT ORDER BY STOPKEY ) should have returned
>>>> only ~20 actual rows(as we have rownum<=20 filter) and thus the table MRQ
>>>> would have been full scanned Max ~20 times , but it's resulting ~13k rows
>>>> and that many time the MRQ is getting full scanned. Are we hitting any bugs
>>>> here because of the buggy way it's written by the team?
>>>>
>>>> Also the execution time for this Update query is increasing day by day,
>>>> must be because either the table GTT_TAB is having rows increasing day by
>>>> day and thus the number of full tables scan increases for MRQ making the
>>>> query response time increase. or the number of rows in table MRQ is
>>>> increasing thus making that step-11 more CPU intensive for each full scan.
>>>>
>>>> Below is the table and column stats.
>>>>
>>>> Table MRQ having total ~2.3million rows.
>>>>
>>>> COLUMN_NAME NUM_DISTINCT DENSITY
>>>> NUM_NULLS HISTOGRAM
>>>> RP_STX 6
>>>> 2.11625295890333E-7 0
>>>> FREQUENCY
>>>> R_DATE 395
>>>> 0.0048780487804878 0
>>>> HEIGHT BALANCED
>>>> MRF_NM 4
>>>> 2.11625295890333E-7 0
>>>> FREQUENCY
>>>> FR_NM 30
>>>> 2.11625295890333E-7 0
>>>> FREQUENCY
>>>>
>>>> Table TSLV is a static data table holding just ~1099 rows. An index -
>>>> TLSV_IX2 is on column L_NM. And the L_NM column has ~416 distinct values.
>>>>
>>>> UPDATE GTT_TAB
>>>> SET PRT = 999
>>>> WHERE RID IN
>>>> (SELECT RID
>>>> FROM ( SELECT *
>>>> FROM MRQ
>>>> WHERE R_DATE = TRUNC ( :B1) - 2
>>>> AND RP_STX = 'XX'
>>>> AND MRF_NM = 'YYY'
>>>> AND FR_NM NOT IN
>>>> (SELECT L_VL
>>>> FROM TLSV
>>>> WHERE L_NM = 'XXXXXXX')
>>>> ORDER BY JE_ETS - JE_STS DESC)
>>>> WHERE ROWNUM <= 20)
>>>>
>>>> Global Information
>>>> ------------------------------
>>>> Status : DONE
>>>>
>>>> Instance ID : 4
>>>>
>>>> SQL Execution ID : 67108864
>>>>
>>>> Execution Started : 11/18/2021 18:15:25
>>>>
>>>> First Refresh Time : 11/18/2021 18:15:32
>>>>
>>>> Last Refresh Time : 11/18/2021 23:25:30
>>>>
>>>> Duration : 18605s
>>>>
>>>>
>>>> Binds
>>>>
>>>> ========================================================================================================================
>>>> | Name | Position | Type |
>>>> Value |
>>>>
>>>> ========================================================================================================================
>>>> | :B1 | 1 | DATE | 11/19/2021 00:00:00
>>>> |
>>>>
>>>> ========================================================================================================================
>>>>
>>>> Global Stats
>>>>
>>>> ============================================================================================
>>>> | Elapsed | Cpu | IO | Concurrency | Cluster | Other |
>>>> Buffer | Read | Read |
>>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
>>>> Gets | Reqs | Bytes |
>>>>
>>>> ============================================================================================
>>>> | 18605 | 18594 | 0.10 | 0.01 | 0.10 | 10 |
>>>> 4G | 219 | 19MB |
>>>>
>>>> ============================================================================================
>>>>
>>>> SQL Plan Monitoring Details (Plan Hash Value=3480899078)
>>>>
>>>> =======================================================================================================================================================================================
>>>> | Id | Operation | Name |
>>>> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
>>>> Mem | Activity | Activity Detail |
>>>> | | | |
>>>> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
>>>> (Max) | (%) | (# samples) |
>>>>
>>>> =======================================================================================================================================================================================
>>>> | 0 | UPDATE STATEMENT | |
>>>> | | | | 1 | | | |
>>>> | | |
>>>> | 1 | UPDATE | GTT_TAB |
>>>> | | 18599 | +7 | 1 | 0 | | |
>>>> | 0.01 | log file sync (1) |
>>>> | 2 | FILTER | |
>>>> | | 18599 | +7 | 1 | 13792 | | |
>>>> | | |
>>>> | 3 | TABLE ACCESS STORAGE FULL | GTT_TAB |
>>>> 12302 | 70 | 18599 | +7 | 1 | 13792 | 40 | 18MB |
>>>> | | |
>>>> | 4 | FILTER | |
>>>> | | 18599 | +7 | 13792 | 13792 | | |
>>>> | | |
>>>> | 5 | COUNT STOPKEY | |
>>>> | | 18599 | +7 | 13792 | 13792 | | |
>>>> | | |
>>>> | 6 | VIEW | |
>>>> 1122 | 52521 | 18599 | +7 | 13792 | 13792 | | |
>>>> | | |
>>>> | 7 | SORT ORDER BY STOPKEY | |
>>>> 1122 | 52521 | 18599 | +7 | 13792 | 13792 | | |
>>>> 96256 | 1.89 | Cpu (342) |
>>>> | 8 | HASH JOIN RIGHT ANTI NA | |
>>>> 1122 | 52520 | 18599 | +7 | 13792 | 255M | | |
>>>> 675K | 0.17 | Cpu (30) |
>>>> | 9 | TABLE ACCESS BY INDEX ROWID | TLSV |
>>>> 1 | 2 | 18599 | +7 | 13792 | 68960 | 116 | 928KB |
>>>> | | |
>>>> | 10 | INDEX RANGE SCAN | TLSV_IX2 |
>>>> 1 | 1 | 18599 | +7 | 13792 | 68960 | | |
>>>> | | |
>>>> | 11 | TABLE ACCESS STORAGE FULL | MRQ |
>>>> 1202 | 52518 | 18604 | +2 | 13792 | 255M | 47 | 376KB |
>>>> | 97.94 | Cpu (17744) |
>>>>
>>>> =======================================================================================================================================================================================
>>>>
>>>>
>>>>

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