the size of a thumb

The size of a thumb was used by humans as a measurement for very long time. Used by craftsmen, the size of body parts like a thumb, hand, forearm, foot and so on were both good enough to be used within a team, and even the ratio between those measurement were acceptable accurate and comparable for each individual. 

But such a system comes to its limits. Different regions had different commonly agreed definitions for the same unit. This makes it hard to properly compare and convert, especially when it comes to written contracts between different regions. 

The metric system was designed to overcome problems in converting units between regions and types of units. 

Still it took some time and effort to show the advantage of this system. As an example, around 1999 NASA invested approx $125 million to teach some engineers in Colorado the difference between pound and newton.    

For me it seems, this problem is also exists in rules of thumb. Some of them are similar, still different. 

My current example is a rule of thumb to decide, if an access path of a SQL statement is acceptable.

The first rule I found by Jeff Holt and Cary Millsap in Optimize Oracle Performance (2003):

High LIO count (more than about 10 LIO calls per nonaggregate row returned per table in the FROM clause)  

It was explained in more detail by Cary in Mastering Oracle Trace Data (2019): 

The quantity 10 is a nice round number that represents the most touches on the buffer cache you should need for plunging an index from root to leaf, plus accessing a data block (even if you have row migration or row chaining). On average, you shouldn’t need more than 10 LIOs to find a row from a single-table query. You shouldn’t need more than 20 LIOs to find a row from a two-table join, more than 30 LIOs to find a row from a three-table join, and so on. You shouldn’t need more than 420 LIOs to retrieve 7 rows from a 6-table join.

A similar, but different rule by Chris Antognini in Troubleshooting Oracle Performance (2019): 

• Access paths that lead to less than about 5 logical reads per returned row are probably good.
• Access paths that lead to up to about 10–15 logical reads per returned row are probably acceptable.
• Access paths that lead to more than about 20 logical reads per returned row are probably inefficient. In other words, there’s probably room for improvement.

These different rules might be based on different assumptions. 

The example of 420 LIOs for 7 rows from a 6 table join is acceptable for Cary, maybe because he accepts the schema which enforces this 6 table join as given, but Chris might suggest a schema redesign or physical design optimization. 

At the end, all rules of thumb must be considered with reasonable care. It's never a law, but can be seen as a starting point. 

---

Update 2020-06-30: Chris Antognini informed me about a very important error I made:
He is referring to Access paths, not full complete execution plans.
Access paths are defined as

An access path is a technique used by a query to retrieve rows from a row source.

This means there is no big difference between Chris and Carys rules - which makes this post kind of useless. Still it will stay to document the importance of accurate reading!
I have to apologize to Chris and Cary for artificially generating a disagreement.