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) (click on video below)
  4. Direct (selfish) Reads and Indirect Reads (13:24)
  5. When is Full Table Scan Direct? (2:44)


Date: Mar 28, 2013

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


Full Table Scans, Indexes

>>John:  We know what a full table scan is. We read every block at the table. But when do you actually get one?


The most obvious reason is when the query requires all the rows in the table or of course a reasonable proportion of them. What is the reasonable proportion? That definition is being revised downwards all the time. However, we just have to trust Oracle on that. 


There are some other reasons which are basically errors, inappropriate or missing indexes. There are also certain well-known SQL constructs that we see again and again that suppress index usage, functions on the wrong side of the predicate, and data type mismatches in predicates. So Oracle has to do automatic typecasting. Any predicate on the lines of where a value is null. There are many SQL constructs that can suppress index usage, but these would normally come in the form of bugs and SQL tuning is going to get rid of them. 




Moving on though, when will you typically have a full table scan (and you should have one)? It’s in joins. Why? Nested loop joins will usually use indexes more often than not – not necessarily, but usually. Hash joins or sort-merge joins will usually use full table scan. Let’s take a look at how efficient these two techniques are. 




By the way, I’m going to work in the SCOTT schema. This is the absolute normal SCOTT schema. The only change I’ve made is that I have index. 


If you look at emp, I’ve declared DEPTNO as not null on that index disk. But that’s just following absolutely normal, best practice to index your foreign key columns, which I’m sure you will do already. 




Let’s just run a very simply query and see what Oracle decides to do. 




Select ename, dname from emp natural join dept where dept no=10.




We set autotrace on. I am aware of autotrace may tell lies but it’s going to be adequate for the purpose of these little demonstrations.




How did Oracle do it? Well, Oracle went indexes all the way. Index unique scan of PK DEPT applying the filter, DEPT NO=10, perfect access method. And then an I/O on the DEPT table to retrieve the dept name objective column.




Then for that one row that we retrieved, it did the same thing on emp. It didn’t index range scan on my foreign key index. Then twice there were three rows. Note statistics are absolutely perfect. Oracle expected three, Oracle got three. So statistics are spot-on here. Having gotten those three key values, retrieve the emp name. 


What was involved in doing that? A total of 6 consistent gets – 6 consistent gets to run that. 




Now, the sort of test we need to do, what it actually corrects to use those indexes? A very basic way of pushing the optimizer towards another plan would be to make the index invisible. I’ll make just one of them invisible at this point, which should be the foreign key index on emp. It’s still there. It’s still going to be used for the foreign key constraints, but it’s now being removed from consideration by the optimizer. 




If I now rerun my query, what happens? Well, clearly it can no longer use that index because the index isn’t there as far as the optimizer is concerned. So it has to do a full table scan. Now these figures are a bit ridiculous because that includes the parting workload so I’ll need to run the statement a couple of times for the figure to stabilize. 


That’s better. Five consistent gets.




Run it again. Five consistent gets. 


Forcing Oracle to use a full table scan has in fact reduced the I/O because its default plan based on indexes was a total of 6 consistent gets. But remembering that index is an option. It’s gone to 5 consistent gets and that’s a trivial change of course if the volumes of data are so small. But note throughout all these, the statistics are absolutely perfect. 


Oracle knows what it’s going to get. It just made the wrong decision. Why? Probably because of the system statistics being correct. But there could be some more interesting variations.




Now, moving on from there, we’ll take a look at joins. 




I’ll remove the filter and apply a different join technique. In this statement, I’m forcing Oracle to use a hash join, join emp to dept. See what it comes up with now.




Full table scans all the way. We scan dept into memory, convert it into a hash table, and then we scanned emp, probing the hash table for the appropriate values to implement the join. And what do we see here? Eleven consistent gets. That sounds worse. But think that through. Go back to Oracle’s original choice.


We’ve got Oracle’s choice here. This one was 5 consistent gets. But think what was involved. That would have been at least one I/O operation on the PK DEPT INDEX. If the index were more than one level, it will be multiple I/Os and they would not be multiple block I/Os. They would be single block reads. 


There would have been at least one, possibly several single block reads to navigate the index. Haven’t got the key, there would have been another single block read on the department table to get that row back as well. Then the full table scan, there would have been a minimum of one more single block read if the entire table fits in one, so one block. That is an absolute minimum using a nested loop with an index of three I/Os. 


However, this plan here it’s quite possible because these tables are so small that the full scan of dept was a single I/O because full scans can use multiple block reads that could well have been a single I/O and that could well have been a single I/O. So even though the total was 11 consistent gets, it might have been significantly reduced actual I/O workload on the I/O subsystem. 


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!