How to Tune Full Table Scans

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 (11.2.0.3).

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

Presenter: John Watson, Oracle Certified Master