Quiz Night

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 11.2.0.3, 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 ?

Update 13th Jan:

The answer is 10,000; the interesting observations are in this comment and the reply.