Oracle Database Discussions

  1* select * from v$version
09:39:59 sql>/

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

        Solaris Version:         Oracle Solaris 10 8/11 s10s_u10wos_17b
                                 Assembled 23 August 2011
        Build Date:
        Kernel Patch:            Generic_147440-27
        Architecture:            sun4v sparc sun4v
        Processors:              256 x 2998 MHz
        Memory:                  261632 Megabytes

With the basics out of the way, I'm looking at the view dba_hist_sqlstat and I'm seeing oddities - specifically in the columns

PHYSICAL_READ_REQUESTS_DELTA
DISK_READS_DELTA


I'm seeing sql_ids with a zero in the read requests value, but showing non-zero disk reads.

What am I missing?

To save time, here are the documentation notes on the columns:

I wonder if the key is "monitored" SQL - I know that not all is, but regardless it's counter intuitive to me to explain how this is reading from disk, yet doing so with zero requests...

Any help/pointers would be appreciated, as always.

Here is an example of what I'm talking about:

09:41:04 sql>l
  1  SELECT s.snap_id
  2        ,s.begin_interval_time
  3        ,s.end_interval_time
  4        ,ss.sql_id
  5        ,SS.PLAN_HASH_VALUE
  6  --      ,ss.parsing_schema_name
  7        ,ss.executions_delta
  8        ,ROUND((ss.elapsed_time_delta/NULLIF(ss.executions_delta, 0))/1000000, 3) avg_elapsed
  9        ,round(ss.buffer_gets_delta/NULLIF(ss.executions_delta, 0)) avg_buffer_gets
 10        ,round(ss.iowait_delta/(NULLIF(ss.executions_delta, 0)))  avg_iowait
 11        ,ROUND((ss.cpu_time_delta/NULLIF(ss.executions_delta, 0))/1000000, 3) avg_cpu_time
 12        ,ROUND((ss.rows_processed_delta/NULLIF(ss.executions_delta, 0)), 3) rows_exe
 13        ,ROUND((ss.physical_read_requests_delta/NULLIF(ss.executions_delta, 0)), 3) PhyReq_exe
 14        ,ROUND((ss.disk_reads_delta/NULLIF(ss.executions_delta, 0)), 3) diskreads_exe
 15  FROM   dba_hist_sqlstat SS
 16  join dba_hist_snapshot s
 17  on s.snap_id = ss.snap_id
 18  join dba_hist_sqltext st
 19  on ss.sql_id=st.sql_id
 20  where ss.sql_id = :b1
 21  --and plan_hash_value = '2580563947'
 22  ORDER BY ss.snap_id
 23*         ,ss.sql_id
09:41:06 sql>/

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SQL_ID        PLAN_HASH_VALUE EXECUTIONS_DELTA AVG_ELAPSED AVG_BUFFER_GETS AVG_IOWAIT AVG_CPU_TIME   ROWS_EXE PHYREQ_EXE DISKREADS_EXE
---------- ------------------------- ------------------------- ------------- --------------- ---------------- ----------- --------------- ---------- ------------ ---------- ---------- -------------
     18008 05-DEC-12 12.00.30.201    05-DEC-12 12.15.34.091    dxmnq1c0fpzs6      4167211524            41020        .002              24       1760            0      1.001          0          .937
     18009 05-DEC-12 12.15.34.091    05-DEC-12 12.30.38.892    dxmnq1c0fpzs6      4167211524          1837452        .002              25       1569            0       .981          0         1.444
     18010 05-DEC-12 12.30.38.892    05-DEC-12 12.45.42.753    dxmnq1c0fpzs6      4167211524          1000618        .002              25       1927            0       .987          0         1.919
     19204 17-DEC-12 23.00.20.730    17-DEC-12 23.15.24.211    dxmnq1c0fpzs6      4167211524           928645        .002              25       1448            0      1.001          0         1.329
     19205 17-DEC-12 23.15.24.211    17-DEC-12 23.30.29.017    dxmnq1c0fpzs6      4167211524          1456914        .002              25       1817            0       .965          0         1.868
     19206 17-DEC-12 23.30.29.017    17-DEC-12 23.45.33.148    dxmnq1c0fpzs6      4167211524           508630        .002              25       2006            0      1.001          0         2.289
     20134 27-DEC-12 15.30.55.845    27-DEC-12 15.46.00.727    dxmnq1c0fpzs6      4167211524           446419        .002              24       1555            0      1.001          0         1.034
     20135 27-DEC-12 15.46.00.727    27-DEC-12 16.00.05.872    dxmnq1c0fpzs6      4167211524          1274685        .002              25       1835            0       .987          0         1.633

We can see the disk reads are non zero, but the physreq are.