Oracle 23ai — Track Table and Partition Scan Access

[MyrinNew]

In Oracle 23ai, a new view named [dba|all|user]_table_access_stats has been introduced, enabling users to view the access history of tables and partitions. This view shows how many times each table or partition has been scanned in each instance and the last time a table was accessed.






SQL> create table jadval(id number, name varchar2(100), last_name varchar2(100));
Table created.

SQL> insert into jadval values(1, 'payan', 'rafat');
1 row created.

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;
no rows selected

SQL> select * from jadval;
ID NAME LAST_NA
---------- ------- -------
1 payan rafat

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;
TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
---------- --------------- ----------- ---------- -------------------------
JADVAL 1 2 01-JAN-25 08.27.33 AM

SQL> select * from jadval;
ID NAME LAST_NA
---------- ------- -------
1 payan rafat


SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
---------- --------------- ----------- ---------- -------------------------
JADVAL 1 3 01-JAN-25 08.28.25 AM







Updating Table Statistics

Updating the statistics of a table increases the READ_COUNT as well:






SQL> exec dbms_stats.gather_table_stats(ownname => 'USEF', tabname => 'JADVAL');
PL/SQL procedure successfully completed.

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
---------- --------------- ----------- ---------- -------------------------
JADVAL 1 4 01-JAN-25 08.36.27 AM







Creating Another Table and Examining Index Effects

A new table with more data is created, and an index is added to one of its columns to observe the impact on the view’s output.






SQL> create table jadval_source as select * from dba_source;
Table created.

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;
no rows selected

SQL> create index indTYPE on jadval_source(TYPE);
Index created.

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NA INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
--------------- ------------ ----------- ---------- -------------------------
JADVAL_SOURCE 1 1 01-JAN-25 09.06.01 AM







Access via Index Range Scan

Accessing an index with INDEX RANGE SCAN does not affect the table’s READ_COUNT:






SQL> select line from JADVAL_SOURCE where TYPE='PACKAGE' and rownum=1;














SQL> select count(*) from JADVAL_SOURCE where TYPE = 'PACKAGE';

COUNT(*)
----------
241717

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
--------------- --------------- ----------- ---------- -------------------------
JADVAL_SOURCE 1 1 01-JAN-25 09.06.01 AM







Table Access by Index Rowid Batched

Similarly, TABLE ACCESS BY INDEX ROWID BATCHED does not increase the READ_COUNT:






SQL> select line from JADVAL_SOURCE where TYPE='PACKAGE' and rownum=1;














SQL> select line from JADVAL_SOURCE where TYPE = 'PACKAGE' and rownum = 1;

LINE
----------
1

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
--------------- --------------- ----------- ---------- -------------------------
JADVAL_SOURCE 1 1 01-JAN-25 09.06.01 AM







Partitioned Tables

For partitioned tables, the view behaves as follows:






SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
--------------- --------------- ----------- ---------- -------------------------
JADVAL_PARTS PARTITION1 1 1 01-JAN-25 08.42.30 AM
JADVAL_PARTS PARTITION2 1 1 01-JAN-25 08.42.30 AM
JADVAL_PARTS SYS_P602 1 2 01-JAN-25 08.42.30 AM
JADVAL_PARTS SYS_P603 1 1 01-JAN-25 08.42.30 AM

SQL> select count(*) from JADVAL_PARTS partition(PARTITION1);

COUNT(*)
----------
0

SQL> select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME
from user_table_access_stats;

TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME
--------------- --------------- ----------- ---------- -------------------------
JADVAL_PARTS PARTITION1 1 8 01-JAN-25 08.46.28 AM
JADVAL_PARTS PARTITION2 1 1 01-JAN-25 08.42.30 AM
JADVAL_PARTS SYS_P602 1 2 01-JAN-25 08.42.30 AM
JADVAL_PARTS SYS_P603 1 1 01-JAN-25 08.42.30 AM







The v$table_access_stats view provides similar information to this new feature.

Vahid Yousefzadeh

Oracle Database Administrator

✉vahidusefzadeh@gmail.com

Telegram channel :https://t.me/oracledb

More...