My Oracle Support Banner

High "Direct Path Read" waits Caused by Full Table Scan on Large Table (Doc ID 3001047.1)

Last updated on FEBRUARY 27, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.21.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Specific SQL was found to be responsible for lot of "direct path reads" causing slow IO.
For example:
4axnn108pv0d4 - SELECT C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15 FROM <table name> WHERE C1 = :1 AND C3 = :3;

In the older AWR reports, this same query would work well, was not using "direct path reads" and was also not the top IO consumer.
We also see from the above, the query is using "TABLE ACCESS - FULL" (FTS), which was also the same case earlier.

Changes

The table size has grown from earlier and no longer "fits" into the definition of a small table.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.