Re: validity of script - using dba_hist views

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 5 Mar 2008 04:37:43 -0800 (PST)
Message-ID: <7857f384-bf94-4045-a909-4ee8b956b35e@d4g2000prg.googlegroups.com>


On Mar 5, 5: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','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.

Hi,

If the segment is in an 8K tablespace, that should be accurate. If you really want to know, I would suggest checking the tablespace block size. Even that may not be accurate, if you have some columns stored out of row in a different size tablespace. That also means it has done over 30 million physical reads in less than a day.

Is this a data warehouse? If not, then you have a bigger problem than block sizes :)

Also, you can write this with straight SQL, you shouldn't need PLSQL for it.

HTH, Steve Received on Wed Mar 05 2008 - 06:37:43 CST

Original text of this message