Re: Update query performance with some buggy behavior

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