SET FILESYSTEMIO_OPTIONS with SQL that does Full Table Scan
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.