Re: validity of script - using dba_hist views

On Mar 5, 2:36 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> On Mar 5, 5:37 pm, 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
>
> Steve,
>
> Its 8k block size tablespace which contains this table and there are
> no columns lying outside the table owning tablespace.  I confirmed it
> (no usage of blobs). Its not a warehouse too.
> It is highly dynamic/OLTP database.
>
> Since I have joined this firm recently, i have no idea how the
> application code was written. I'm in process of finding out pain
> points in application.
> Was quite surprised to see these high values, so i asked to confirm my
> feeling.
>
> Yes, agreed that this could have been done by sql rather than by pl/
> sql. Actually this code is part of bigger code where start/end snap
> ids were used. So used this code.
>
> With your experience, please suggest the way to move this issue
> towards resolution.
>
> Warm Regards,
> sachin
>
> David, Yes we are licensed to use these views :) I think I told you in
> some other forum.- Hide quoted text -
>
> - Show quoted text -

The comment was made prior to seeing this same post in ORACLE_DBA_EXPERTS. David Fitzjarrell Received on Wed Mar 05 2008 - 15:27:28 CST