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

["Alexander, Steven" <Steven.Alexander@sanjoseca.gov>]

I'm not familiar with your terminology.  Does "observations missing from
data view" mean that the view does not deliver all the rows in the
underlying table?

Have you checked the script for the view?  Is it correct?  Is it joining the
main table to another table that could eliminate some rows?  Does it have
any "where" clauses that could eliminate some rows?  Is it performing any
aggregation functions that could combine some rows?  Is it using the
"distinct" keyword that could eliminate some rows?  (Don't underestimate the
havoc that bad data can cause.)

You might try rebuilding the indices.  If they're corrupt, they might not
have some of the underlying rows.

Also, when you speak of the DBAs collecting stats, do you mean generating
statistics for the optimizer by analyzing the tables?  Changing that would
change the execution plan that the optimizer produces.  In a clean database,
that affects performance but not results.  But in a database with
questionable indices, that might not have been used previously, it could
change the results.

-----Original Message-----
From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org
[mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On
Behalf Of Wendy Biggs
Sent: Friday, April 06, 2007 1:52 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] actual execution time for select to process -why
*I* want accurate measurements

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

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