This content has been marked as final. Show 4 replies
DBA112 wrote:I agree that query below does not provide correct details
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
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;
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.
1 person found this helpful
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;
You can try this1 person found this helpful
You can get this from the SQL DEveloper --reports-database administration-Topsql-diskreads
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