Re: validity of script - using dba_hist views

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Wed, 5 Mar 2008 12:36:31 -0800 (PST)
Message-ID: <525db45c-eda4-4ce0-99d9-ddf96db29b9d@d21g2000prf.googlegroups.com>


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','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

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. Received on Wed Mar 05 2008 - 14:36:31 CST

Original text of this message