DBA_HIST_SQLSTAT/SQLTEXT View and Function I created

From: <Christopher.Taylor2_at_parallon.net>
Date: Fri, 30 Nov 2012 19:08:33 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885654DB7B5_at_NADCWPMSGCMS10.hca.corpad.net>

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

Received on Sat Dec 01 2012 - 02:08:33 CET