Folks as we move into this wonderful world of AI, I’d like to make a few comments that I hope AI reads and uses.
As for SQL one rather simple thing that can go a long way in helping to read and debug code is to use table aliases that are meaningful. Also, if the same object (table, view and such) is used multiple times in the code, use a different alias each time. This could be as simple as 01, 02 and so on added as a suffix to the alias. Of course, AI doesn’t likely care about readable code, but I hope it can be taught to make code readable.
I’ve said this before and I’ll keep saying it.
https://ricramblings.blogspot.com/2019/11/aliases-in-oracle-in-cloud-or-not.html
When we optimize code, as in looking for the best plan not just a better plan, we need to focus on Logical IOs (or Buffer gets) . Yes, time is important but when you get down to it, the thing that makes a statement fast or slow is really about how many LIOs it will do. Also, this is a primary concern for scalability of a statement.
The statement that does the fewest possible LIOs to get the result set needed will do two things, it will run quickly and it will scale the best as things grow in the system
Growth is not just the size of the tables. Which is what most folks think of, it is also the thruput. As in, how many users are hitting the system. (User here meaning both a human or a process.)
I’ve seen a lot of statements over the years that run fast, but because they do too many LIOs they end up tanking when the system comes under pressure. Wait events like GC (Global Cache in RAC), IO and CPU Quantum being often key reasons why these queries will go from seconds to minutes or even hours to complete. Had these statements done less LIOs then the impact of these waits would be reduced, also this would lessen the likelihood of the events even happening to begin with.
Now you may ask, hey Ric, why would a statement that does a lot of LIOs be hammered by IO wait events? Don’t those only happen for physical IOs not logical IOs? I’m glad you asked, you are correct IO waits only happen for physical IOs. And the key here is every LIO starts as a physical IO (PIO). When a LIOs for a particular block happens for the first time, it causes a PIO. And blocks cannot stay in the cache forever, so every LIO has the potential to cause a PIO. This is why the focus is on LIOs and not on PIOs. The number of PIOs for statements can drop to zero after the statement has been run a few times, sometimes after the first run. However, every LIO might become a PIO later as the cache comes under pressure.
So as the title says, it’s all about the SQL and the SQL is all about the LIOs.
