Warning – this is a catch question, and I haven’t given you enough information to have any idea of the right answer; though, by telling you that I haven’t given you enough information to have any idea of the right answer, you now have some information that might help you to get closer to the right answer.
I have a simple heap table with no indexes. Immediately after flushing the buffer_cache I’ve run a query that looks ike this:
select max(column_ZZZ) from table_X;
The most significant session stats for this operation are as follows:
Name Value ---- --------- session logical reads 20,651 consistent gets 20,651 consistent gets direct 20,649 physical reads 655 physical reads cache 1 physical reads direct 654 Number of read IOs issued 6 no work - consistent read gets 20,649 table scan rows gotten 329,922 table scan blocks gotten 10,649 table fetch continued row 645 buffer is not pinned count 10,000
The instance is 18.104.22.168, so serial direct path reads have been used for the (necessary) tablescan. The tablespace is using 8KB blocks, 1MB uniform extent sizing, and manual (freelist) segment space management. The data in the table was created by a pl/sql loop of inserts with commits, there have been no updates, deletes, merges or rollbacks. Here’s the code (with one crtical detail hidden) that populated the table:
begin for i in 1..X loop insert into member(member_id, block_age_max_nbr) values (1,1); commit; end loop; end; / execute dbms_stats.gather_table_stats(user,'member',method_opt => 'for all columns size 1')
Roughly how many rows are there in the table ?
The answer is 10,000; the interesting observations are in this comment and the reply.