Oracle 23ai — Track Table and Partition Scan Access

#oracle #data #database #linux

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

DEV Community

Oracle 23ai — Track Table and Partition Scan Access

Top comments (0)