Oracle SQL Tuning Tutorial -Tuning Long Full Table Scans

Long Full Table Scans. A Free Tutorial.

Number Eight in the  “Performance tuning Guide, Top Ten Mistakes Found in Oracle Systems” Long Full Table Scans is described as follows:

“Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.”

Actually, there are many cases where the full table scan is your friend. But whether they are good or bad for the performance of the SQL that invokes them, there may be implications (for better or for worse) for performance of other statements. This is particularly true in 11.2.x, where direct reads are possible for serial scans, and indirect reads are possible for parallel scans. Do you trust the optimizer? The change in behaviour in recent releases may need some investigation, and revisiting older code.

As always with a SkillBuilders’ tutorial, we shall illustrate the issues and solutions with live demonstrations using release 11g (

Audience:  Operating System Administrators, Storage Administrators, Oracle Administrators and Management responsible for Storage and / or Oracle Databases.

Presenter: John Watson, Oracle Certified Master .  <<<<< Take class with John, March 27

This free tutorial is segmented into several separate lessons:

  1. Introduction (0:46)
  2. Have You Collected System Statistics? (6:33)
  3. Full Table Scans, Indexes (6:26)
  4. Direct (selfish) Reads and Indirect Reads (13:24)
  5. When is Full Table Scan Direct? (2:44) (click on video below)


Date: Mar 28, 2013

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


When is Full Table Scan Direct?

>>John:  When is a full table scan direct? Huge changes through the releases. And if it direct, will it be direct or indirect? That decision is sometimes good, sometimes bad. You need to consider your session. You need to consider other sessions. 




In summary, serial full table scans were always indirect before 11.2, parallel scans always direct before 11.2. These are the two critical parameters. 


That hidden parameter _serial_direct_read, remember, defaults to auto so your application may start behaving very differently as you upgrade from 11.2.01 or 11.2.02. 


Parallel_degree_policy, that defaults to manual. But again, it becomes an option when you go to 11.2 to enable the automatic facility which allows not only automatic tuning to the degree of parallelism but also enables the ability to make indirect reads when doing a full table scan. 




These two parameters were completely in the hands of Oracle. What is the algorithm that it uses? How does the cost-based optimizer actually decide how to do it? 




Well, it’s based on a whole set of hidden parameters. I’ve listed four of them here. Adaptive_direct_read – the name tells you that’s fairly critical. Then we have small_table_threshold, very_large_table_threshold. Also, direct_read_decision_statistics_driven. Whether that decision between direct to indirect should take account to a system statistics and object statistics. 


These are some of the parameters that it will consider. And looking at the default and experimenting with them, you can influence the algorithm. 




There are of course also other dependencies. It appears to consider the size of the buffer cache, and perhaps most interestingly, the number of blocks of the object that are currently cached. 




If you Google around, you’ll find various people who attempted to reverse engineer the algorithm. They’ll tell you that if the table is more than 5x small_table_threshold or whatever then you will always get a certain type of access method. I’m not going to present those algorithms because I haven’t reversed engineered them to my own satisfaction yet. But the end result of all of these is that the algorithm Oracle uses may not be right for you in terms of your session, but furthermore it may not be right for any other sessions. 


This changing behavior for serial full table scans and parallel full table scans can have a very huge effect on the performance of your applications and you have to investigate what’s going on.

Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!