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