Database Tuning (MOSC)

MOSC Banner

SET FILESYSTEMIO_OPTIONS with SQL that does Full Table Scan

in Database Tuning (MOSC) 10 commentsAnswered ✓

We have a stored procedure that does Full Table Scans of tables that are 2G to 20G. The SP is run once per might on most of our databases as a database Scheduler Job. The Linux O/S is oversized to handle multiple Oracle instances.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0

SUSE Linux Enterprise Server 15 SP6

The SP takes 40 minutes on some databases and 10 minutes on others. I found that the difference is that:

The slower databases have SET FILESYSTEMIO_OPTIONS=SETALL

The faster databases have SET FILESYSTEMIO_OPTIONS=NONE

It looks like the FTS does DIRECT READ which bypasses the buffer cache. The slower databases are doing physical reads for every page of the FTS. The faster databases get the benefit of the Linux I/O cache.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center