4 Replies Latest reply: Jan 3, 2013 8:49 PM by Maran Viswarayar RSS

    SQL - Highest Physical Reads

    DBA112
      Dear Experts,

      I want to find out the query (sql id) that contributed to highest number of physical reads over a 24 hours period (snap id's 1 to 24) by a specific user - 'USER'

      I'm using query as below, don't think it's giveing me right..any suggestions pls
      select sql_id, PHYSICAL_READ_BYTES_DELTA from DBA_HIST_SQLSTAT 
      where PARSING_SCHEMA_NAME = 'USER' and snap_id between 1 and 24 order by PHYSICAL_READ_BYTES_DELTA;
        • 1. Re: SQL - Highest Physical Reads
          sb92075
          DBA112 wrote:
          Dear Experts,

          I want to find out the query (sql id) that contributed to highest number of physical reads over a 24 hours period (snap id's 1 to 24) by a specific user - 'USER'

          I'm using query as below, don't think it's giveing me right..any suggestions pls
          I agree that query below does not provide correct details

          AWR or STATSPACK contains what you desire

          >
          select sql_id, PHYSICAL_READ_BYTES_DELTA from DBA_HIST_SQLSTAT 
          where PARSING_SCHEMA_NAME = 'USER' and snap_id between 1 and 24 order by PHYSICAL_READ_BYTES_DELTA;
          • 2. Re: SQL - Highest Physical Reads
            Salman Qureshi
            Hi,
            Generate AWR report for these 24 hours and in this report, you can find a section called "SQL ordered by Reads". This will give you top SQLs with more physical reads during this interval.

            Salman
            • 3. Re: SQL - Highest Physical Reads
              sb92075
              SELECT To_char(c.begin_interval_time, 'YYYY-MM-DD Day') "DATE", 
                     a.object_name, 
                     SUM(b.physical_reads_delta) 
                     total_physical_reads_today 
              FROM   dba_objects a, 
                     dba_hist_seg_stat b, 
                     sys.wrm$_snapshot c 
              WHERE  a.object_id = b.obj# 
                     AND b.snap_id >= (SELECT Min(snap_id) 
                                       FROM   sys.wrm$_snapshot 
                                       WHERE  begin_interval_time >= Trunc(SYSDATE)) 
                     AND Upper(a.object_name) LIKE Upper('%') 
                     AND b.physical_reads_delta > 0 
                     AND c.instance_number = (SELECT instance_number 
                                              FROM   v$instance) 
                     AND c.snap_id = b.snap_id 
              GROUP  BY To_char(c.begin_interval_time, 'YYYY-MM-DD Day'), 
                        a.object_name 
              ORDER  BY 1, 3; 
              • 4. Re: SQL - Highest Physical Reads
                Maran Viswarayar
                You can try this
                select substr(sql_text,1,500) "SQL",
                                                      (cpu_time/1000000) "CPU_Seconds",
                                                      disk_reads "Disk_Reads",
                                                      buffer_gets "Buffer_Gets",
                                                      executions "Executions",
                                                      case when rows_processed = 0 then null
                                                           else round((buffer_gets/nvl(replace(rows_processed,0,1),1))) 
                                                           end "Buffer_gets/rows_proc",
                                                      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
                                                      (elapsed_time/1000000) "Elapsed_Seconds",
                                                      module "Module"
                                                 from v$sql s
                                                order by disk_reads desc nulls last
                You can get this from the SQL DEveloper --reports-database administration-Topsql-diskreads