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@xxxxxxxx> wrote:
Snip of just the top of bounced message, sigh, lazy old guy forgot to snip
before:
*From:* Mark W. Farnham [mailto:mwf@xxxxxxxx ;<mwf@xxxxxxxx>]
*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@xxxxxxxxx ;<andysayer@xxxxxxxxx>]
*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.
