RE: reasonable length comment on variable execution speed
Date: Thu, 2 Dec 2021 09:39:49 -0500
Message-ID: <635901d7e78a$75db2c00$61918400$_at_rsiz.com>
Sigh. Andy’s recommended column additions will clearly be the best result for these queries.
And you really want the parallel execution to be local to avoid as many gc waits as possible and if possible restrict all the work on SFE to a single instance if that is easy to control in your environment.
But once again, if your numbers are accurate that the sum of XX, YY, and ZZ are under 92 million, SFE_IX4 is the best first filter of SFE from your EXISTING indexes, and is better than the indexed scanned rows from SFE for the binds used of any of your existing plans. And it completely avoids chances for your billion row results. So it IS more selective than what you are currently doing.
Andy’s index improvement suggestion is dramatically better for this query.
IF you can trivially add the columns Andy suggested, that should get you a dramatic improvement. IF it is a big deal to add those columns to the index, then please show the plan with the transitive DID on STD and SBD and join it to the inline view of SFE pruned to only the columns you need, the single predicate (ptcode) for XX, YY, and ZZ, using the other filters later. IF there are no included values other than “YY” between “XX” and “ZZ” I would change that predicate to a between “XX” and “ZZ”, if there might be, then I would add the predicate between “XX” and “ZZ” to the enumerated exact constants. IF XX, YY, and ZZ are fake values that are not lexically a range, nevermind on that, likewise even if they are a lexical range but the interceding values inflate the number of rows included well beyond the 92 million, then don’t add it. Finally, doing the creation of the inline pruned view *might* be best as the union all of three column selecting scans each using a single equality predicate on ptcode. Since one of the constants predominates, that might not help much, and if you leave out the “ALL” it will be a disaster because Oracle has to proof uniqueness to keep each row in a non-ALL union and that means either sorting or hash probing. It might also be inaccurate if the pruned select column list doesn’t include ptcode you might have duplicate rows, but I’m guessing you report ptcode anyway. I include this notion of parallel execution to produce the union all result mostly for folks reading this that have a similar situation but different in that the rows returned by their equivalent of ptcode for various values are of a relatively flat distribution with no need for sorting so that concatenation can be used. For your case two of the parallel union creations will finish very fast, doing all the set up work and occupying threads without much change on the elapsed time of the longest thread.
So: 1a) improve the index as Andy suggested for this query
1b) prune SFE first on ptcode if you cannot improve the index
2) Keep the transitive “did” predicate so the CBO can pick the best result for that
3) Try to keep this local to avoid global cache traffic
4) Please show the sql text in addition to the plans in case I have been unclear regarding the suggestions resulting in you coding up something different from what I meant to suggest.
Good luck,
mwf
From: Pap [mailto:oracle.developer35_at_gmail.com]
Sent: Tuesday, November 30, 2021 1:16 AM
To: Mark W. Farnham; Andy Sayer; Pap
Cc: Oracle L
Subject: Re: reasonable length comment on variable execution speed
Resending as it got bounced back.
Thank you Andy and Mark.
I tried running the same query for a different bind value(as old sql monitor bind values are now resulting in zero rows) and commented th STAT filter just to make sure its resulting nonzero rows. And I believe that the STAT column filter for bind :b2 was not filtering much so not having much impact. So what i see is, when i remove the hints and added the additional join on DID column i.e STD.DID=SBD.DID (which is logically correct), i do see optimizer by itself joined table SBD and STD first and then resulted rows are joined to SFE and also in this case SFE is scanned using index SFE_IX2(I.e on column FHID) , but still in this case i am seeing its visiting ~1billion rows and running longer as compared to the original hinted query. So it seems as Andy suggested the only option here is to create a composite index on eid,fhid and possibly adding column oid to it will make it further more selective. Correct me if I'm wrong.
- Original query*****************************
Global Information
Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 11/29/2021 06:33:30 First Refresh Time : 11/29/2021 06:33:30 Last Refresh Time : 11/29/2021 06:43:44 Duration : 614s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 3
Global Stats
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 661 | 161 | 433 | 0.56 | 67 | 3 | 11M | 547K | 4GB |
SQL Plan Monitoring Details (Plan Hash Value=2551627081)
| 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 | SELECT STATEMENT | | | | 2 | +613 | 1 | 7965 | | | | | |
| 1 | VIEW | | 8662 | 7M | 2 | +613 | 1 | 7965 | | | | | |
| 2 | WINDOW SORT PUSHED RANK | | 8662 | 7M | 613 | +2 | 1 | 12042 | | | 4M | | |
| 3 | NESTED LOOPS | | | | 612 | +2 | 1 | 12042 | | | | | |
| 4 | NESTED LOOPS | | 8662 | 7M | 612 | +2 | 1 | 12042 | | | | | |
| 5 | NESTED LOOPS | | 8662 | 7M | 612 | +2 | 1 | 12042 | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SBD | 9324 | 697 | 612 | +2 | 1 | 10000 | 253 | 2MB | | | |
| 7 | INDEX RANGE SCAN | SBD_IX1 | 9324 | 78 | 612 | +2 | 1 | 10000 | 9 | 73728 | | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 1 | 733 | 612 | +2 | 10000 | 12042 | 443K | 3GB | | 81.57 | gc buffer busy acquire (53) |
| | | | | | | | | | | | | | gc cr grant 2-way (9) |
| | | | | | | | | | | | | | gc current block 2-way (1) |
| | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | Cpu (94) |
| | | | | | | | | | | | | | cell single block physical read (280) |
| | | | | | | | | | | | | | read by other session (62) |
| 9 | INDEX RANGE SCAN | SFE_IX1 | 4688 | 251 | 613 | +1 | 10000 | 116M | 104K | 809MB | | 18.43 | Cpu (20) |
| | | | | | | | | | | | | | cell single block physical read (54) |
| | | | | | | | | | | | | | read by other session (39) |
| 10 | INDEX UNIQUE SCAN | STD_PK | 1 | 1 | 613 | +2 | 12042 | 12042 | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | STD | 1 | 2 | 612 | +2 | 12054 | 12042 | | | | | |
Predicate Information (identified by operation id):
1 - filter("RN"<2)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "SBD"."BDID" ORDER BY INTERNAL_FUNCTION("SFE"."FID") DESC )<2)
7 - access("SBD"."HD_ID"=:B1)
8 - filter(("SFE"."ptcode"='ZZ' OR "SFE"."ptcode"='YY' OR "SFE"."ptcode"='XX') AND "SFE"."FHID"="SBD"."SFHID" AND "SFE"."OID"="SBD"."SID" AND
NVL("SFE"."SCID",0)=NVL("SBD"."SCID",0) AND "SBD"."P_DT"="SFE"."P_DT")
9 - access("SFE"."etyp"='ZZZZ' AND "SFE"."EID"="SBD"."DID")
10 - access("SFE"."EID"="STD"."DID")
- With additional join condition between STD and SBT on column DID and Removing hints **********************
Global Information
Status : DONE (ALL ROWS) Instance ID : 2 SQL Execution ID : 33554432 Execution Started : 11/29/2021 06:35:36 First Refresh Time : 11/29/2021 06:35:40 Last Refresh Time : 11/29/2021 06:54:05 Duration : 1109s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 3
Global Stats
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 1160 | 891 | 218 | 0.00 | 50 | 3 | 62M | 628K | 5GB |
SQL Plan Monitoring Details (Plan Hash Value=3841551266)
| 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 | SELECT STATEMENT | | | | 1 | +1109 | 1 | 7965 | | | | | |
| 1 | VIEW | | 1 | 2M | 1 | +1109 | 1 | 7965 | | | | | |
| 2 | WINDOW SORT PUSHED RANK | | 1 | 2M | 1106 | +4 | 1 | 12042 | | | 4M | | |
| 3 | NESTED LOOPS | | | | 1106 | +4 | 1 | 12042 | | | | | |
| 4 | NESTED LOOPS | | 1 | 2M | 1106 | +4 | 1 | 1G | | | | | |
| 5 | NESTED LOOPS | | 9324 | 10062 | 1106 | +4 | 1 | 10000 | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SBD | 9324 | 697 | 1106 | +4 | 1 | 10000 | 32 | 256KB | | | |
| 7 | INDEX RANGE SCAN | SBD_IX1 | 9324 | 78 | 1106 | +4 | 1 | 10000 | 9 | 73728 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | STD | 1 | 2 | 1106 | +4 | 10000 | 10000 | | | | | |
| 9 | INDEX UNIQUE SCAN | STD_PK | 1 | 1 | 1106 | +4 | 10000 | 10000 | | | | | |
| 10 | INDEX RANGE SCAN | SFE_IX2 | 3071 | 27 | 1106 | +4 | 133K | 1G | 29520 | 231MB | | | |
| 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 1 | 173 | 1109 | +1 | 1G | 12042 | 599K | 5GB | | | |
Predicate Information (identified by operation id):
1 - filter("RN"<2)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "SBD"."BDID" ORDER BY INTERNAL_FUNCTION("SFE"."FID")
DESC )<2)
7 - access("SBD"."HD_ID"=:B1)
9 - access("STD"."DID"="SBD"."DID")
10 - access("SFE"."FHID"="SBD"."SFHID")
11 - filter("SFE"."etyp"='ZZZZ' AND ("SFE"."ptcode"='ZZ' OR "SFE"."ptcode"='YY' OR "SFE"."ptcode"='XX') AND "SFE"."EID"="STD"."DID" AND "SFE"."EID"="SBD"."DID" AND
"SFE"."OID"="SBD"."SID" AND NVL("SFE"."SCID",0)=NVL("SBD"."SCID",0) AND "SBD"."P_DT"="SFE"."P_DT")
On Mon, Nov 29, 2021 at 5:58 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
Snip of just the top of bounced message, sigh, lazy old guy forgot to snip before:
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Sunday, November 28, 2021 6:35 AM
To: 'Andy Sayer'
Cc: 'Pap'; 'Oracle L'
Subject: RE: Variable execution time of sql with same plan
By all means, if you can improve SFE_IX1 as Andy suggests, then it should directly prune to far fewer rows to later filter. You’ll pay maintenance costs for the extra columns, and it may change the behavior of other queries, but adding Andy’s suggested columns probably gets this query to return in time better expressed as seconds rather than minutes. Looking up a few tens of thousands of rows by an index instead of looking up 100 million to over a billion should definitely be an enormous win. The execution may remain highly variable by bind value, but you won’t care.
IF you cannot modify the indexes for whatever reason, then 91 million is fewer rows to filter than any of your presented cases, and doing that by creating just the columns you need for those 91 million rows via that index will need to be done with a sledge hammer to convince the CBO to use it. Ergo the inline view suggestion: presented initially with only ptcode as a predicate the CBO will use that index. That changes no indexes. It should get you fairly consistent timing results, slightly faster than your presented best case. But it still does an enormous amount of work that wouldn’t be needed at all with an improved SFE_IX1.
Changing the join order structurally is also probably a consistent win, but if you can improve SFE_IX1 to only pull tens of thousands of rows in the first place it is not going to matter. If you put the transitive additional equality in the query it probably gives the CBO a better chance of seeing whether it is a win, and that is a trivial code change. Only do it structurally if you are sure it is always a win or at least a tie.
mwf
From: Andy Sayer [mailto:andysayer_at_gmail.com]
Sent: Saturday, November 27, 2021 7:21 PM
To: Mark W. Farnham
Cc: Pap; Oracle L
Subject: Re: Variable execution time of sql with same plan
Pap,
My suggestion remains to index the SFE table using enough of the columns you're filtering on so you do less work.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 02 2021 - 15:39:49 CET