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) (click on video below)
  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)


Date: Mar 28, 2013

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


Have You Collected System Statistics?

>>John:  To begin with, we have to determine what the problem actually is, if indeed it is a problem because there are many occasions when a full table scan is what you want, it’s going to be the fastest way to run any particular query. Full table scans can most definitely be your friend. In any case, is it the table that’s long or is it the scan that’s long?




Those of you who work with Exadata will be very familiar with this and the various techniques of pushing the cost-based optimizer to all its full table scans. It really doesn’t want to do one. Never forget, an Exadata environment, it is only full table scan and index fast full scan that can be offloaded to the storage tier. It’s also the only way to offload Bloom Filter joins. I mentioned Exadata here particularly because that’s the environment where you may not be able to trust the optimizer at all. But in general, trust Uncle Oracle. 




If the cost-based optimizer is using a full table scan, then probably that is the best thing to do. If it is not the best thing to do, most likely your cost-based optimizer is making a mistake. Why? Because you are giving it bad information. The information you have to give it is of course statistics. We all know about object statistics, but I want to make particular mention of system statistics as well.




System statistics are sometimes forgotten about at a surprising number of sites. So I do want to summarize what they actually are so you can actually check whether you do indeed have system statistics. 


Looking at this database here, if I select * from sys.aux_stats$ – we’re doing a [01:45 inaudible] table here. This is what you want to see. If I had a dozen rows and you want to see every row reasonably well populated, what is this telling me? It’s telling me that system statistics have indeed been collected. 


When were they collected? The collection started at about 1:00 and finished an hour and 12 minutes later. So we got over an hour of monitoring the hardware to see how the system was performing. 




Then we see flags – don’t worry about that – and then three figures that are on default. CPU speed, NW is normal workload, I/O seek time, I/O transfer speed. These figures are the defaults. Heaven knows where Oracle gets those defaults from, but they’re often totally inappropriate.


The next six rows are much more interesting because these are calculated by Oracle monitoring the hardware itself. This tells me that even though by default, it was under the impression that it would take 10 milliseconds for the I/O seek time of the disk this machine is running on. In fact, we can do a single block read in about three and a half milliseconds. 


But particularly interesting, we can do a multi-block read in a bit over 4 milliseconds. So it’s taking virtually the same time to read a set of blocks as to read just one block. That’s going to be a big push towards the full table scan because Oracle knows they can read a lot more data per second with full table scan multi-block reads. 


There’s the CPU scan speed and that isn’t relevant to our topic today. You can see it’s a lot less than the assumed default.


MBRC is the multi-block read count. That’s going to be automatically tuned from 10.2 onwards. It simply means that during the statistics gathering period, the multiple read count size was typically six blocks, which isn’t actually very big.




Then we see the last two, max throughput and slave throughput. This is the I/O rates that Oracle measured. 




Up here, we see its default, the I/O transfer speed. Units are bytes per millisecond. So Oracle guessed that they could do about some 4 megabytes a second. In fact, they could do 15 megabytes per second. 


More interestingly, when running parallel query, each parallel execution server could do about 3.5 megabytes per second. By dividing that into that, you get an interesting approximation for how many parallel execution servers. It’s actually sensible to run on this box.




Then by contrast – look at this database here – this is same release,, but this is a Linux system that I happened to have around. If I select * from sys.aux_stats$, we see something very different. Half the table isn’t populated properly at all. The part that is populated is pretty disastrous. 


According to this, yes, we have gathered statistics. It looks as though we gathered them for less than a second back in 2011. 




All that means is we’ve been able to have gathered them at all, only created this database yesterday. This is just default values. 


Default values – they’re all in the documentation that Oracle assumed. This machine’s [05:10 inaudible] are totally different. This is a fairly powerful Linux box. This one down here is just my little Windows laptop. But Oracle has put in the same assumptions for I/O seek time and transfer speed.




In this case, Oracle has absolutely no idea of how long it takes actually to get data on and off disk. If you run this simple query on your production databases, you may well be horrified by what you see. 


I’ve seen systems where the statistics were last gathered perhaps five years previously. When suddenly being 11g on the [05:45 inaudible] cluster, it was [05:46 inaudible] on Linux. The statistics have never been gathered since. Why? Because the statistics are not gathered automatically.


That’s the first point we want to emphasize, that you must give Oracle object statistics of course and it’s not your job to do that. But system statistics are also vital. But system statistics you have to gather by hand. 


Final point on slide, db_file_multiblock_read_count. That is self-tuning from 10.2. I’m very glad it is 2. I did a lot of experimenting with that with earlier releases but I never really found an algorithm. Let me work out the optimal value for it. But for 11.2 onwards, Oracle tunes that internally through its monitoring of I/O rates through its use of system statistics.    



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!