This discussion is archived
1 Reply Latest reply: Jan 4, 2013 2:09 PM by DBA112 RSS

Logical Reads for a SQL ID

DBA112 Newbie
Currently Being Moderated
Dear Experts,

Any idea how we can find out Logical reads for a SQL ID over a 24 hour period? AWR shows physical reads info.. any data dictionary view that captures logical I/O?

Thanks
  • 1. Re: Logical Reads for a SQL ID
    DBA112 Newbie
    Currently Being Moderated
    Figured it out.. This is it:
    col Time_Taken for 999999999999
    col log_reads for 9999999999999
    
    select sql_id, sum(disk_reads_delta) as phy_reads, sum(BUFFER_GETS_DELTA) as log_reads, sum(ELAPSED_TIME_DELTA/1000000) as Time_Taken 
    from dba_maint.DB_HIST_SQLSTATS
    where PARSING_SCHEMA_NAME='USERNAME' and BEGIN_INTERVAL_TIME>='03-JAN-13' and sql_id in ('abcdefghijkl')
    group by sql_id order by  phy_reads, log_reads, time_taken desc;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points