RE: Update query performance with some buggy behavior
Date: Mon, 22 Nov 2021 07:04:36 -0500
Message-ID: <59a001d7df99$1eb2bea0$5c183be0$_at_rsiz.com>
We have no idea from this thread what is in GTT_TAB. We can guess from the name that it is a global temporary table, but I’m not a fan of guessing.
IF the intent is NOT correlation of something in GTT_TAB with the select, then it seems to me a “with” clause hinted materialize that produces just the list of RID that are to be updated to the constant probably puts Oracle in a good position to do something smart. If “with” clause mechanics are complicated for your staff, you can create another GTT with just the intended RID and just the RID column values you intend. IF GTT_TAB happens to have been created with physically ordered RID value (which you’d have to ask), and a new gtt with just the desired matching RID values is also created in RID order, then if Oracle chooses a nested loop, then the number of loops will be exactly 1, which should be cheaper than building a hash.
Good luck. Optimizing buggy code starts with fixing the bug. It really doesn’t matter how fast you do the wrong thing. The only way that comes to mind that code could even have the correct results is if it is a bizarre way of coding up the equivalent of an exists clause on some unspecified relationship between the rows in GTT_TAB and the other tables.
… and we have no clue from the thread about the contents of GTT_TAB other than it apparently has at least two columns RID and PRT.
good luck!
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lok P
Sent: Sunday, November 21, 2021 2:12 PM
To: Pap
Cc: Andy Sayer; Oracle L
Subject: Re: Update query performance with some buggy behavior
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-lReceived on Mon Nov 22 2021 - 13:04:36 CET