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 -

What does the explain plan show for this query? What information do you see when you set autotrace on and execute this query?

David Fitzjarrell Received on Wed Mar 05 2008 - 15:31:52 CST