Oracle SQL Tuning Tutorial -Tuning Long Full Table Scans | Have You Collected System Statistics?

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 (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 .  <<<<< 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
click to read the Transcript

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