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

Last updated on JULY 20, 2024

Applies to:

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

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