validity of script - using dba_hist views
Date: Wed, 5 Mar 2008 02:55:17 -0800 (PST)
Message-ID: <37eed40d-340d-46b2-a0a3-44e8ed280698@s12g2000prg.googlegroups.com>
Hi,
Oracle version: 10.2.0.2
OS: Solaris 10 x86
I wrote a script to capture the top queries doing disk io. cumulatively for a day.
Here is the code. Let me know if i have done something wrong in this.
set serverout on
declare
b_snap_id number;
e_snap_id number;
sql_txt clob;
db_name varchar2(200);
disk_reads number;
begin
select name into db_name from v$database;
select snap_id into b_snap_id from (select snap_id from
dba_hist_snapshot
where trunc(begin_interval_time)
=trunc(sysdate) order by BEGIN_INTERVAL_TIME) where rownum < 2;
select snap_id into e_snap_id from (select snap_id from
dba_hist_snapshot
where trunc(begin_interval_time)=trunc(sysdate) order by
BEGIN_INTERVAL_TIME desc) where rownum < 2;
--dbms_output.put_line(b_snap_id || ' ' || e_snap_id);
dbms_output.put_line('###
###');
dbms_output.put_line('SECTION 2:');
dbms_output.put_line('DB Name: '|| db_name);
dbms_output.put_line('SQLs ordered by DISK READS');
dbms_output.put_line('###
###');
dbms_output.put_line('############################################################');
for i in (select sql_id,sum(disk_reads_delta)*8192/1024/1024
dr,parsing_schema_name from dba_hist_sqlstat
where PARSING_SCHEMA_NAME not in
('SYS','SYSTEM','SYSMAN','REPADMIN','DBSNMP','XDB','PERFSTAT','WKSYS','SNAPADMIN') and snap_id between b_snap_id and e_snap_id group by sql_id,parsing_schema_name having
sum(disk_reads_delta)*8192/1024/1024>10000 order by dr desc) loop disk_reads:=i.dr;
select sql_text into sql_txt from dba_hist_sqltext where sql_id=i.sql_id;
if length(sql_txt) > 32000 then
--sql_txt:='The length of SQL is too huge to be displayed here';
sql_txt:=substr(sql_txt,1,32760);
end if;
dbms_output.put_line('Parsing Schema: '|| i.parsing_schema_name);
dbms_output.put_line('Disk Reads in MB: '|| round(disk_reads));
dbms_output.put_line('SQL: '|| sql_txt);
dbms_output.put_line('-----------------------');
end loop;
dbms_output.put_line('END OF SECTION');
dbms_output.put_line('############################################################');
end;
/
############################################################Parsing Schema: CONTEXT_PROD
Disk Reads in MB: 236231
SQL: SELECT COUNT_ID, FILE_NAME, COUNT,
IS_PRIORITY FROM TEST_TABLE
WHERE SITE_ID = :1
Along with the output pasted above, there were other SQLs also, but for reference, i'm using this.
Does that really mean that this query did 230GB of IO?
Please clarify this doubt. Received on Wed Mar 05 2008 - 04:55:17 CST