DIFFERENCE BETWEEN PHYSICAL_READ_BYTES_DELTA AND DISK_READS_DELTA IN dba_hist_sqlstat

DIFFERENCE BETWEEN PHYSICAL_READ_BYTES_DELTA AND DISK_READS_DELTA IN dba_hist_sqlstat

What does these columns in dba_hist_sqlstat denote and what makes the difference between these values?

Trace the query with 10046 if needed.

kIsH@xHydra<>alter session set statistics_level=all;

Session altered.

kIsH@xHydra<>alter session set tracefile_identifier='10046_dbss';

Session altered.

kIsH@xHydra<>alter session set events '10046 trace name context forever, level 12';

Session altered.

kIsH@xHydra<>SELECT /*+parallel(8)*/ * from dba_source;


kIsH@xHydra<>alter session set events '10046 trace name context off';

Session altered.

kIsH@xHydra<>exit

Check dba_hist_sqlstat view for PHYSICAL_READ_BYTES_DELTA and DISK_READS_DELTA

SQL> select SQL_ID,PHYSICAL_READ_BYTES_DELTA,DISK_READS_DELTA from dba_hist_sqlstat where SQL_ID='3hbbrg389qazm';

SQL_ID        PHYSICAL_READ_BYTES_DELTA DISK_READS_DELTA
------------- ------------------------- ----------------
3hbbrg389qazm                  61587456             7518

Let us verify the and sum up the metric “physical read total bytes” from v$mystat which shows the same statistics as dba_hist_sqlstat

SQL> SELECT a.name,sum(b.value) PHYSICAL_READ_BYTES_DELTA ,c.sql_id from v$statname a,v$mystat b, v$sql c where c.sql_id='3hbbrg389qazm' and a.name in ('physical read total bytes') group by a.name,c.sql_id;

NAME                      SUM(B.VALUE) SQL_ID
------------------------- ------------ -------------
physical read total bytes   61587456 3hbbrg389qazm

In autotrace, the "physical reads" output from execution plan statistics shows the same value as DISK_READS_DELTA.

SQL> set lines 200 pages 1000
SQL> set autot traceonly
SQL> SELECT /*+parallel(8)*/ * from dba_source;

Statistics
----------------------------------------------------------
        168  recursive calls
          0  db block gets
       8610  consistent gets
       7518  physical reads <===============
          0  redo size
   29862441  bytes sent via SQL*Net to client
     213836  bytes received via SQL*Net from client
      19405  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     291053  rows processed

Summary:

PHYSICAL_READ_BYTES_DELTA – represents the number of data in bytes read from disk between begin interval and end interval time of snapshot

DISK_READS_DELTA – is the number of data blocks read from disk between begin interval and end interval time of snapshot

Leave a Reply

%d bloggers like this: