Re: sql writers block
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: Scott <oraracdba@xxxxxxxxx>
- Date: Tue, 10 Feb 2009 11:18:41 -0600
Hi Scott
You should probably read about my papers in this topic :-)
<shameless plug>
http://orainternals.files.wordpress.com/2008/04/riyaj_tuning_with_sql_new_features_doc.pdf
http://orainternals.files.wordpress.com/2008/04/riyaj_exciting_sql_new_features_doc.pdf
</shameless_plug>
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Mon, Feb 9, 2009 at 7:42 PM, Scott <oraracdba@xxxxxxxxx> wrote:
> Thanks, This sounds bad, but I really had no idea these functions existed.
> This information is very useful.
>
> I have found that when troubleshooting large clusters comparing workload
> information across nodes is just not available (easily with GC and canned
> reports) but it is stored in the dba_hist* tables. So I was trying to fill a
> hole that GC and the awr and ash reports lack.
>
> Again thanks for the input,
>
> Scott
>
> ------------------------------
> *From:* Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
> *To:* oraracdba@xxxxxxxxx
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Sent:* Monday, February 9, 2009 5:52:21 PM
> *Subject:* Re: sql writers block
>
> Hi Scott
> I think, following SQL might be a good start.. I haven't tested this
> thoroughly and so, please test it..
>
> with stats as (
> select begin_interval_time, instance_number, snap_id,
> ...On Mon, Feb 9, 2009 at 3:32 PM, Scott <oraracdba@xxxxxxxxx> wrote:
>
>>
>> Either this can't be done in SQL, If it can then I am missing something.
>> The goal is break this information based on snap_id between a given time.
>> This probably can be done with lots of inline sql but I want to keep this as
>> simple as possible.
>>
>> Thanks in advance
>>
>> Scott
>>
>> select b.snap_id
>> ,sum(case when e.stat_name = 'DB time'
>> then e.value - b.value
>> else 0
>> end) tdbtim
>> , sum(case when e.stat_name = 'DB CPU'
>> then e.value - b.value
>> else 0
>> end) tdbcpu
>> , sum(case when e.stat_name = 'background elapsed time'
>> then e.value - b.value
>> else 0
>> end) tbgtim
>> , sum(case when e.stat_name = 'background cpu time'
>> then e.value - b.value
>> else 0
>> end) tbgcpu
>> from dba_hist_sys_time_model b
>> , dba_hist_sys_time_model e
>> where e.instance_number = b.instance_number
>> and b.snap_id = (select max(snap_id)
>> from dba_hist_snapshot
>> where
>> END_INTERVAL_TIME between to_date(:BDATE,'yyyymmddhh24mi')-(1/24)
>> and
>> to_date(:BDATE,'yyyymmddhh24mi'))
>> and e.snap_id = (select max(snap_id)
>> from dba_hist_snapshot
>> where
>> END_INTERVAL_TIME between to_date(:EDATE,'yyyymmddhh24mi')-(1/24)
>> and
>> to_date(:EDATE,'yyyymmddhh24mi'))
>> and b.stat_id = e.stat_id
>> and e.stat_name in ('DB time','DB CPU'
>> ,'background elapsed time','background cpu time')
>> group by b.snap_id
>>
>>
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
>
>
Other related posts:
