Re: validity of script - using dba_hist views

On Mar 5, 6:37 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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','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.
>
> 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- Hide quoted text -
>
> - Show quoted text -

And let's hope you're licensed to use AWR.

David Fitzjarrell Received on Wed Mar 05 2008 - 07:51:34 CST