Chuck wrote:
> DA Morgan wrote:
>> Chuck wrote:
>> I'm trying to come up with a query of v$undostat that will show the
>> total # of undo blocks used during any two hour period. The output
>> should look like this..
>>
>> begin_time end_time undoblks
>> ========== =========== ===========
>> 09:00 11:00 123
>> 09:10 11:10 145
>> 09:20 11:20 1032
>>
>> You get the picture. It's essentially the same thing that "Select
>> begin_time, end_time, undoblks from v$undostat" would show if each row
>> was 2 hours apart instead of 10 minutes apart.
>>
>> TIA
>
>> I just threw this together off the top of my head so it may well
>> have more than a few problems.
>
>> col begin_time format a10
>> col end_time format a10
>
>> SELECT TO_CHAR(begin_time, 'HH24:MI') BEGIN_TIME,
>> LEAD(TO_CHAR(end_time, 'HH24:MI'), 11, 0) OVER (ORDER BY begin_time)
>> END_TIME,
>> undoblks
>> FROM gv$undostat;
>
>> but hopefully will point you in one direction.
>
>
> Thanks Daniel. I was not familiar with LEAD/OVER. It's a much more
> elegant solution then my self join that I posted at the same time as
> yours. You pointed me in the right direction. I looked it up in the SQL
> reference and tweaked it to do what I want and it executes in a fraction
> of the time.
Check Tom Kyte's website and books. Tom is a big fan of the analytics
and they are extraordinarily valuable.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Dec 07 2006 - 14:44:33 CST
