Two Ways to Count a Full Table Scan

Following table scan rows gotten through Oracle, GDB, RICO2 and bpftrace

1. One statistic, two accountants

Oracle has two accountants working on our full table scans. One checks rows individually. The other reads the number of row-directory entries in a block and adds the entire count. Give the second accountant another visit to the same block, and that count can appear in the ledger again. Both accountants use the same column: table scan rows gotten. Beer is recommended.

The interesting part is the accounting granularity. In the ordinary heap-table paths examined here, kdstgr increments the statistic as it returns eligible rows individually. The path through kdst_fetch0 adds the selected table-directory entry's kdbtnrow value when it executes its block-accounting code. Deleted slots and repeated visits can therefore affect the two paths differently.

We will follow both paths on HR.EMPLOYEES, change one row flag, restore it, and compare the SQL results with the session statistics. We will also change SQL*Plus ARRAYSIZE and watch the same block contribute repeatedly.

Oracle's reference describes table scan rows gotten as the number of rows processed during scanning operations. The experiments below give that description a concrete implementation context. Every function offset and context-member offset in the example commands is specific to the tested executable.

2. Prepare the two sessions and the queries

Let’s start our investigation.

First, we will create a table containing just nine rows from EMPLOYEES:

SQL> create table hr.employees9 as 
  2  select *
  3  from hr.employees  
  4  where dbms_rowid.rowid_block_number(rowid) = (
  5        select dbms_rowid.rowid_block_number(rowid) 
  6        from   hr.employees
  7        where employee_id=198);

Utworzono tabele.

SQL> select count(1) from hr.employees;

  COUNT(1)
----------
       107

SQL> select count(1) from hr.employees9;

  COUNT(1)
----------
         9

We will use two different queries:

-- Row-by-row path in this experiment
Select /*+ full(e) */ * 
from hr.employees9 e;

-- Block-accounting path in this experiment
select /*+ full(e) */ *
from hr.employees9 e
Where employee_id=199;

Open a target SQL*Plus session and a separate observer session in the same PDB. In the target session:

alter session set container=RICK1;
set arraysize 2

select p.spid, s.sid
from v$session s, v$process p 
where p.addr=s.paddr
and s.sid=userenv('sid');

Run each query once before measuring it to warm up its cursor.

In the observer session, run the following query immediately before and after each measured execution. Replace TARGET_SID with the SID recorded above:

select n.statistic#, n.name, s.value
from v$sesstat s join v$statname n
  on n.statistic#=s.statistic#
where s.sid=TARGET_SID
  and n.name in ('table scan rows gotten',
                'table scan blocks gotten')
order by n.name;

Subtract the value recorded before execution from the value recorded afterward. These are cumulative session counters. Using a separate observer session keeps the measurement focused on the target statement.

Now, let’s find our table's data_object_id so that we can confirm later in GDB that we are examining the correct object.

select data_object_id 
from dba_objects
where owner='HR' 
and object_name='EMPLOYEES9'
and object_type='TABLE';

3. GDB: follow the row and find its block

Attach to the foreground process of the target session:

sudo gdb -nx -q -p PID

Here, -nx skips startup files, -q suppresses the greeting, and -p selects the process. In GDB:

set pagination off
set language c
break *kdstgr
continue

Execute SELECT * FROM HR.EMPLOYEES9 in the target terminal. The break command, abbreviated b, sets a breakpoint. The asterisk in break *kdstgr selects the function's exact entry instruction. The continue command, abbreviated c, resumes execution. At function entry on AArch64, x0 holds the first argument: the scan context.

Once the context contains a valid pointer to a pinned block, we can locate the beginning of that block with this compact expression:

p/x (*(char **)($x0 + 0x60) - 0x14)

Read it from the inside out. Add 0x60 bytes to the context address, then read the pointer stored there. That pointer addresses ktbbh. Subtract 0x14, or 20 in decimal, to reach kcbh at the beginning of the block. This calculation uses the beginning of ktbbh, so it remains independent of that structure's variable length.

The first function entry can occur before a block is acquired. On later entries, the context can still refer to the block used by the preceding call.

After the first breakpoint, check whether the block address looks plausible:

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$1 = 0xffffffffffffffec

This address clearly does not point to a valid block, so we continue and check the next one:

(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$2 = 0x1c4132000

If we continue, we will see nine more calls to the function, each time with the context pointing to the same block:

(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$3 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$4 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$5 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$6 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$7 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$8 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$9 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$10 = 0x1c4132000
(gdb) c
Continuing.

The scan-row counter matches the number of returned rows:

STATISTIC# NAME                                VALUE
---------- ------------------------------ ----------
      1015 table scan blocks gotten                2
      1010 table scan rows gotten                  9

Now it looks nice! We can start RICO2 and examine the block in memory.

4. RICO2: inspect exactly the same memory

Start Python in an environment where RICO2 is available and you have permission to read the foreground process's address space. Enter the PID and block address obtained from your session:

import rico2

r = rico2.Rico()
r.PID = int(input('Foreground PID: '))
block = int(input('Block address from GDB, hex: '), 16)
r.get_block_memory(block)
>>> r.map()
 File: N/A(4)
 Block: 50891                   Dba: 0x100c6cb
------------------------------------------------------------
 DATA Table/Cluster

 struct kcbh, 20 bytes                          @0

 struct ktbbh,  96 bytes                        @20

 struct kdbh, 14 bytes                          @124

 struct kdbt[1], 4 bytes                        @138

 sb2 kdbr[9]                                    @142

 ub1 freespace[7400]                            @160

 ub1 rowdata[628]                               @7560

 ub4 tailchk                                    @8188

get_block_memory reads the live process memory. The map shows nine row-directory entries in the block: kdbr[9].

Let’s play with our data a bit and make one row “invisible”.

>>> r.p_kdbr_data(0)
rowdata[554]                            @8114   0x2c
-------------
flag@8114:      0x2c
lock@8115:      0x0
cols@8116:      11


col    0[3]     @8117: c20263                                   198 [NUMBER?]
col    1[6]     @8121: 446f6e616c64                             Donald [TEXT?]
col    2[8]     @8128: 4f436f6e6e656c6c                         OConnell [TEXT?]
col    3[8]     @8137: 444f434f4e4e454c                         DOCONNEL [TEXT?]
col    4[12]    @8146: 3635302e3530372e39383333                 650.507.9833 [TEXT?]
col    5[7]     @8159: 786b0615010101                           2007-06-21 00:00:00 [DATE?]
col    6[8]     @8167: 53485f434c45524b                         SH_CLERK [TEXT?]
col    7[3]     @8176: c30511                                   41600 [NUMBER?]
col    8[0]     @8180: *NULL*
col    9[3]     @8181: c20219                                   124 [NUMBER?]
col   10[2]     @8185: c133                                     50 [NUMBER?]

The flag at byte offset 8114 is 0x2c. If we change it to 0x3c, the row will disappear from this scan because Oracle interprets the new flag as marking the row as deleted.

5. Change one flag and compare the two accountants

Changing 0x2c to 0x3c sets bit 0x10, the deleted-row bit in these ordinary heap-row flags. The experiment changes the byte presented to the row-processing code. Transaction visibility also involves consistent reads and transaction state; this intervention isolates a single flag check in an already prepared buffer.

To change the flag in memory on Linux, we can open the file that exposes the process's address space:

>>> f = open("/proc/45954/mem", "rb+")

Then we can seek to the flag's address—the block address plus the flag's byte offset within the block—and change one byte:

>>> f.seek(int('0x1c4132000',16)+8114)
7584563122
>>> f.write(binascii.unhexlify("3c"))
1
>>> f.flush()
>>> 

Let’s repeat the query without a WHERE clause:

The kdstgr run

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$12 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$13 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$14 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$15 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$16 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$17 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$18 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 1, 0x000000000e3b8e1c in kdstgr ()
(gdb) p/x (*(char **)($x0 + 0x60) - 0x14)
$19 = 0x1c4132000

With the context pointing to our block, the breakpoint was hit eight times, as expected. The table scan rows gotten counter grew from 9 to 17. Everything looks as expected…

Now we will run the same query with a WHERE clause, and our world will collapse…

The kdst_fetch0 run

When we execute the second query, we discover that things are no longer so simple. Our kdstgr function is not called at all, and suddenly table scan rows gotten increases by 18 on each run!

The function responsible for this is kdst_fetch0.

Let’s trace it:

(gdb) b *kdst_fetch0
Breakpoint 2 at 0xc35791c
(gdb) c
Continuing.

When we execute the following query:

SQL> ;
  1  Select /*+ full(e) */ *
  2  from hr.employees9 e
  3* where employee_id=199
SQL> /

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY
------------------------- -------------------- -------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        199 Douglas              Grant
DGRANT                    650.507.9844         08/01/13 SH_CLERK        41600
                      124            50

we see the following breakpoint hits:

(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$20 = 0xffffffffffffffec
(gdb) c
Continuing.

Breakpoint 2, 0x000000000c35791c in kdst_fetch0 ()
(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$21 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 2, 0x000000000c35791c in kdst_fetch0 ()
(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$22 = 0x1c4132000
(gdb) c
Continuing.

We can see one call without a valid block pointer and two calls with the block address we already know!

I started wondering: if this function increases table scan rows gotten by 18 on each run, which counter does it use? The block still has nine row slots, but one row is marked as deleted by the 0x3c flag we set earlier. It must be using one of the block counters that records the number of rows.

One of these counters is in the kdbt structure. Let’s examine it in RICO2:

>>> r.p_kdbt()
struct kdbt[0], 4 bytes                         @138
        sb2 kdbtoffs                            @138    0
        sb2 kdbtnrow                            @140    9

What happens if I change kdbtnrow from 9 to, say, 6?

Let’s try it in Python, just as we changed the flag from 0x2c to 0x3c earlier:

>>> f.seek(int('0x1c4132000',16)+138)
7584555134
>>> f.write(binascii.unhexlify("06"))
1
>>> f.flush()

What happens when we run the query again?

Before this run, my session statistics look like this:

STATISTIC# NAME                                VALUE
---------- ------------------------------ ----------
      1015 table scan blocks gotten               23
      1010 table scan rows gotten                173

Now we run the query again and check GDB:

(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$33 = 0xffffffffffffffec
(gdb) c
Continuing.

Breakpoint 2, 0x000000000c35791c in kdst_fetch0 ()
(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$34 = 0x1c4132000
(gdb) c
Continuing.

Breakpoint 2, 0x000000000c35791c in kdst_fetch0 ()
(gdb) p/x (*(char **)($x1 + 0x60) - 0x14)
$35 = 0x1c4132000
(gdb) c
Continuing.

We encounter the same block twice again, and my statistics now look like this:

STATISTIC# NAME                                VALUE
---------- ------------------------------ ----------
      1015 table scan blocks gotten               25
      1010 table scan rows gotten                185

So table scan rows gotten increased by… 12 rows! Each time kdst_fetch0 executes its block-accounting code, it updates the statistic using the kdbtnrow field in the kdbt structure. Even deleted row slots can therefore contribute to table scan rows gotten. This is crazy! But it gets even more interesting.

Let’s automate the counting in GDB:

(gdb) b kdst_fetch0
Breakpoint 1 at 0xc357964
(gdb) set $cnt=0
(gdb) command 1 
Type commands for breakpoint(s) 1, one per line.
End with a line saying just "end".
>if (*(char **)($x1 + 0x60) - 0x14) == (char *) 0x1c4132000 
 >set $cnt = $cnt + 1
 >end 
>print $cnt 
>c
>end
(gdb) c
Continuing.

This increments $cnt by one each time the breakpoint in kdst_fetch0 is hit with the context pointing to block address 0x1c4132000.

SQL> set arraysize 2
SQL> select employee_id, salary*1.1
  2  from hr.employees9;

EMPLOYEE_ID SALARY*1.1
----------- ----------
        199      45760
        200      77440
        201     228800
        202     105600
        203     114400

So, as a simple row count, our table scan rows gotten statistic is basically worthless!

6. Follow the block-accounting path

Let’s return to the full EMPLOYEES table for a few more tests.

kdst_fetch0 participates in a group-fetch path that also handles projections, predicates and sorting. For the following executions, we first warmed up each cursor, then used the same table and SQL*Plus ARRAYSIZE 15. GDB counted calls at each function's exact entry instruction:

In the abbreviated predicate label, id means employee_id. The non-aggregate variants used FULL(e) and NO_PARALLEL(e). Their actual cursor plans showed TABLE ACCESS FULL. The sorted statement also showed SORT ORDER BY, and the aggregate showed SORT AGGREGATE. The predicate returned 54 rows; the projection and sorted statement each returned 107.

The plain SELECT, the query with a predicate and the arithmetic projection shared plan hash 1445457117 in this lab. Their execution paths and accounting differed despite that identical plan hash.

The arithmetic projection provides a useful controlled comparison:

set arraysize 15
select /*+ full(e) no_parallel(e) */
       employee_id, salary*1.1 from hr.employees e;

set arraysize 1000
select /*+ full(e) no_parallel(e) */
       employee_id, salary*1.1 from hr.employees e;

We measured one execution at each setting, fetching all result rows. bpftrace showed two populated blocks: one with kdbtnrow=98 and one with kdbtnrow=9. At ARRAYSIZE 15, the first block contributed eight times and the second twice:

8 * 98 + 2 * 9 = 802

At ARRAYSIZE 1000, the same SQL returned the same 107 rows. The first block contributed twice and the second once:

2 * 98 + 1 * 9 = 205

The trace recorded identical buffer addresses and RDBAs for repeated contributions. Client fetch boundaries affect when this path re-enters its block-accounting code. Even ARRAYSIZE 1000 produced two contributions from the first block in this SQL*Plus run; the observed sequence of fetches determines the result.

The block counter changed too: +13 at ARRAYSIZE 15 and +6 at ARRAYSIZE 1000. The table and its physical layout stayed unchanged. These counters describe accesses and accounting events, including repeated encounters with a block.

Unfortunately, this experiment made me wonder how many other statistics can mislead a performance investigation. I wanted to create a simple scan for JAS-MIN that would indicate whether your database might suffer from fragmentation and scans of empty blocks. Instead, I fell down the rabbit hole of internal statistics counters…

Well, at least I had fun investigating it 😂🍻