Re: reasonable length comment on variable execution speed
From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 30 Nov 2021 11:45:53 +0530
Message-ID: <CAEjw_fgoJO8awDUUpmtZ6rp3PGNd3zUEXSMiW-+yjcrZdABxng_at_mail.gmail.com>
| 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 |
| Id | Operation | Name |
Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem |
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
| 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 |
| 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 - filter("RN"<2)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "SBD"."BDID" ORDER BY INTERNAL_FUNCTION("SFE"."FID")
DESC )<2)
"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:
Date: Tue, 30 Nov 2021 11:45:53 +0530
Message-ID: <CAEjw_fgoJO8awDUUpmtZ6rp3PGNd3zUEXSMiW-+yjcrZdABxng_at_mail.gmail.com>
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 <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 <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 Tue Nov 30 2021 - 07:15:53 CET