Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!w24g2000prd.googlegroups.com!not-for-mail
From: raja <dextersunil@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: AWR Sample Report
Date: Fri, 12 Dec 2008 04:39:27 -0800 (PST)
Organization: http://groups.google.com
Lines: 49
Message-ID: <f61ffa10-1f2d-49d2-ba40-11de7878a453@w24g2000prd.googlegroups.com>
References: <f04e51c8-57d0-49b2-a3dd-368dd07be2ab@u18g2000pro.googlegroups.com> 
 <6034c969-c16b-4ec7-88c6-b0ac34893060@c2g2000pra.googlegroups.com> 
 <fea2395d-4cbb-4043-92b8-cc2330fdc947@d42g2000prb.googlegroups.com> 
 <ebc7afb7-c463-4ce4-9d80-715b1f004e5f@d36g2000prf.googlegroups.com> 
 <c7b8bc7d-9da2-4647-b495-2ece924fd65a@a12g2000yqm.googlegroups.com> 
 <fd9b1de4-9c38-4e0b-8d19-5a36ff187540@x16g2000prn.googlegroups.com> 
 <a7144d36-a11a-45f7-86ed-e827c4e0ccab@s1g2000prg.googlegroups.com> 
 <9843e44c-97ce-4e55-8a63-71529080ea7b@a3g2000prm.googlegroups.com> 
 <1046232a-6815-4745-be1b-46c1f70b0866@z27g2000prd.googlegroups.com> 
 <8a1b6759-9d76-4638-ad39-74adf489a622@c1g2000yqg.googlegroups.com> 
 <918d086a-7fe8-4bec-b208-530fb3594b3b@v5g2000prm.googlegroups.com> 
 <fccef781-ffac-4262-ae00-79c7e18bb152@20g2000yqt.googlegroups.com>
NNTP-Posting-Host: 203.99.195.98
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1229085597 27369 127.0.0.1 (12 Dec 2008 12:39:57 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 12 Dec 2008 12:39:57 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: w24g2000prd.googlegroups.com; posting-host=203.99.195.98; 
 posting-account=Eb5apgoAAABb6owQC8W5VV_zJ9zDBG1A
User-Agent: G2/1.0
X-HTTP-Via: 1.1 CTSINGMRISAA, 1.1 CTSINTCOISA9
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 
 2.0.50727; MS-RTC LM 8; InfoPath.2),gzip(gfe),gzip(gfe)
Xref:  news.cambrium.nl

Hi Charles/ Steeve,

Query 1 ( Query given by charles ) :
select
  to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
  dhse.instance_number,
  time_waited_micro - nvl(lag(time_waited_micro, 1, 0) over (partition
by dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
time_waited,
  total_waits - nvl(lag(total_waits, 1, 0) over (partition by
dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
total_waits
from dba_hist_snapshot dhs,
     dba_hist_system_event dhse
where dhs.snap_id = dhse.snap_id
  and dhs.instance_number = dhse.instance_number
order by 1, 2;

The output of your query is present in excel file named as : wait1


Query 2 ( Query given by steeve ) :
select * from (
select event_name, wait_class,to_char(begin_interval_time,'YYYY_MM_DD
HH24:MI'),
       dhse.instance_number,
       time_waited_micro - lag(time_waited_micro, 1, 0) over (order by
dhse.instance_number, dhse.snap_id) as time_waited,
       total_waits - lag(total_waits, 1, 0) over (order by
dhse.instance_number, dhse.snap_id) as total_waits
from dba_hist_snapshot dhs,
     dba_hist_system_event dhse
where dhs.snap_id = dhse.snap_id
  and dhs.instance_number = dhse.instance_number
  and begin_interval_time > sysdate - num_of_days_to_analyze
order by 5 desc)
where rownum < 10000;

The output of your query is present in excel file named as : wait2
Here, the wait event : "PX qref latch" tops the list

Since i am not able to post those files in our group's thread, i am
sending those files to ur mail ids.
Please check these 2 files and tell me your observations.
Also, another doubt is : whether should be neglect the idle events
always ? then why are they notified to us...

With Regards,
Raja.