hi,
I'm using Oracle 19c. I ran a query on a large table and studied the execution plan on OEM. The majority of the DB time was I/O waits and the query completed in about 7 mins. Specifically, the majority of the waits was DB file sequential read. Then, I reran the query immediately after the first attempt and got pretty much the same result. Physical reads were the same between runs too. I was expecting the second execution to pull from the buffer cache and not show the file sequential reads plus 0 physical reads. Could anyone explain this behavior? Here's the query for reference. The trades table is huge - probably about 100 million rows with index on trade_date.
select order_date, count(*)
from orders
where order_date between to_date('01/01/2021','MM/DD/YYYY') and to_date('12/31/2021','MM/DD/YYYY')
and order_status = 'Complete'
group by order_date