RE: [sqr-users] actual execution time for select to process -why *I* want accurate measurements

[Wendy Biggs <wendy.biggs@vt.edu>]

Ahh.... there you have it ... we are running Oracle 10g and the DBA's 
decided to change how they were collecting stats .... ever since then 
we've been having problems with observations missing from data view 
(they are there in the underlying table but not in the view) .... so 
far we, (the DBA's and my team) have not found a pattern to when the 
obs are dropping out of sight.  The current solution is to delete the 
stats on several tables that are involved in the view and flush the 
shared pool every time the problem crops up and we report it (which 
is daily at this point).  I don't mean to make them sound incompetent 
... we just haven't found the bug yet and I'm having to watch the 
situation 'by hand' to ensure that our end users are not producing 
reports with missing data that they are not aware of.

and in the middle of the pudding, one of the DBA's asked if we could 
please try running the query several different ways with different 
hints to see if they helped.  So ... I've got this little program 
chroned out there to run on an hourly basis with several variations 
on the base query to watch for when the problem crops up and whether 
it is affected by including any of the suggested hints.

and I want to watch the average time the various permutations of the 
query are taking because I'm not inclined to add hint A just because 
it means the obs are not being dropped if hint A consistently 
averages 2 minutes CPU time...  I'm more inclined to want the bug 
found and fixed.

besides, I'm a basically curious person, I like watching for patterns 
& the CPU times are giving me an idea of whether or not our 'end 
users' are running against the tables in between my runs.  ...

more than you really wanted to know, right?

--wb


At 04:17 PM 4/6/2007, you wrote:
>That's as close as you're going to get without having actual 
>database access through a tool such as sql*plus, toad, oem or 
>whatever, and maybe running an explain on your code.  Even then, the 
>actual execution time will vary upon a lot of factors, including 
>whether the query is in cache, or if there has been a lot of dml run 
>on the tables affected by the query.  Additionally, how long ago 
>statistics was run will be important, as well as any new or removed indexes.
>
>I guess I am missing *why* *you* need such accurate measurements of 
>sql execution timing; that's why you keep DBAs around.
>
>peace,
>clark 'the dragon' willis



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users