Re: validity of script - using dba_hist views

On Mar 5, 2:55 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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','SNAP­ADMIN')
> 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.

Remember, this is from Oracle's viewpoint. The OS viewpoint may be quite different, if you are using a filesystem that may be buffered, and you may have buffering in your I/O system too, especially if a SAN is involved. You might consider googling for a paper by Cary Milsap about lio v. pio [IIRC] to see how to approach this issue. His book might be even better.

You should also check the performance manual in the docs about how to see what is in your SGA, you may be seeing the effects of this test_table thrashing everything around. You want to both fix the app and smooth the I/O, often fixing the app does both.

Are people complaining?

jg

--
@home.com is bogus.
One can only laugh sometimes.  http://www.w3.org/blog/systeam/2008/02/08/w3c_s_excessive_dtd_traffic