reasonable length comment on variable execution speed

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 29 Nov 2021 07:28:00 -0500
Message-ID: <5f7d01d7e51c$8d0ec780$a72c5680$_at_rsiz.com>

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-l

Received on Mon Nov 29 2021 - 13:28:00 CET