Hermann, if I may I would like to use your thread to explore more this estimated vs actual plan thing (in Oracle).
In Oracle we also have this use-the-FIRST-plan logic, aka bind peeking. Usually bind peeking is regarded as something you don't have to worry about unless you have histograms: since without histograms no detail is known about the distribution, every value will result in the same explain plan. From what I understand, however, that is not entirely true.
In my system I have only one mandt - 121. These are all from ST05:
1) Bind variables:
select * from vbap where mandt = :a1
SELECT STATEMENT ( Estimated Costs = 53.072 , Estimated #Rows = 2.218.599 )
1 TABLE ACCESS FULL VBAP
( Estim. Costs = 53.072 , Estim. #Rows = 2.218.599 )
Estim. CPU-Costs = 14.782.624.065 Estim. IO-Costs = 51.100
2) "Correct" client:
select * from vbap where mandt = '121'
SELECT STATEMENT ( Estimated Costs = 53.072 , Estimated #Rows = 2.218.599 )
1 TABLE ACCESS FULL VBAP
( Estim. Costs = 53.072 , Estim. #Rows = 2.218.599 )
Estim. CPU-Costs = 14.782.624.065 Estim. IO-Costs = 51.100
3) Non-existent client:
select * from vbap where mandt = '000'
SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 1 )
5 2 TABLE ACCESS BY INDEX ROWID VBAP
( Estim. Costs = 1 , Estim. #Rows = 1 )
Estim. CPU-Costs = 6.851 Estim. IO-Costs = 1
1 INDEX RANGE SCAN VBAP~0
( Estim. Costs = 1 , Estim. #Rows = 1 )
Search Columns: 1
Estim. CPU-Costs = 4.313 Estim. IO-Costs = 1
This happens because oracle keeps 1 bucket even without histograms. In this case, this bucket would be minimum 121, maximum 121.
So my doubt is: if, in oracle, there is a first query with mandt = '000' and then subsequent queries with mandt ='121', all these subsequent queries will use VBAP~0 even if FULL is better, right?
And how does this relate with substitute literals? How I think it works:
1) Without substitute literals, the first query (with 000) will be parsed, and the value 000 will be used to calculate the actual plan (so will use VBAP~0). Subsequent queries (with 121) will use this cached plan (and not a FULL).
2) With substitute literals, each query (with 000 and with 121) will have its own entry in the cache, so they will behave differently.
Comments?
Bluesky
