Re: DBA_HIST_SQLSTAT/SQLTEXT View and Function I created

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Fri, 30 Nov 2012 20:34:49 -0600
Message-Id: <09B3596D-A949-46A3-9409-CFC0E6A2A1C2_at_gmail.com>

Also should point out that the scripts roll up SQL statements based on force_matching_signature - it taxes the max(sql_id) instead of all SQL ids and displays that sql text as a representative sample (in the case of changing literals).

Sent from my mobile device. Please ignore any typos or misspellings.

On Nov 30, 2012, at 7:08 PM, <Christopher.Taylor2_at_parallon.net> wrote:

> Guys/Gals,
> Here are a couple of scripts (very rough probably - but hey, I'm proud of them).
>
> The first is a pretty standard view - it limits the query time range to a specific window of 12am to 5am (because that is the period I most interested in usually)
> 1. http://codepaste.net/2a9o83
>
> The second script is a Pipelined function I wrote to use the above view but enabled to pass in a specific start time and end time interval. This interval needs to encompass your AWR snapshot intervals. So if your snapshot interval is one hour, then the start datetime/end datetime will need to be at least an hour apart.
> 2. http://codepaste.net/pdcfo5
>
> I thought these might be useful to someone besides me. (Caveat: The hints I used in the SQL may or may not be useful but seemed to give a bit of better performance overall though I didn't actually test whether that was true).
>
>
>
> Chris Taylor
> Oracle DBA
> Parallon IT&S
> christopher.taylor2_at_parallon.net<mailto:christopher.taylor2_at_parallon.net>
> www.parallon.net
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 01 2012 - 03:34:49 CET