Howard J. Rogers wrote:
> Sybrand Bakker wrote:
>
>> On 10 Dec 2004 21:37:11 -0800, premmehrotra_at_hotmail.com wrote:
>>
>>
>>> I am using Oracle9i on HP UNIX 11i. When I do
>>>
>>> select max(maxquerylen) from v$undostat
>>>
>>> I get a number 13748 which is in seconds which is between 3-4 hours.
>>>
>>> When I look in v$sqlarea for the maximum elapsed_time taken by any
>>> query it is only:
>>> 798220317
>>>
>>> which I assume is in micro seconds, i.e., 798 seconds.
>>>
>>> I am puzzled what exactly does maxquerylen mean, does it mean time in
>>> seconds taken by a query (that does not seem to be the case) or
>>> is it maximum time taken by a transaction.
>>>
>>>
>>> Thanks,
>>>
>>> Prem
>>
>>
>>
>> Did you look up the v$<view> reference note on Metalink, or do you
>> think you'll get a quicker response when you ask around on cdos?
>>
>>
>> --
>> Sybrand Bakker, Senior Oracle DBA
>
>
>
> I don't know about Metalink, Sybrand. But even if he'd bothered to look
> up V$SQLAREA at http://tahiti.oracle.com, he'd have gotten no joy...
> because the definition of that view neatly -and stupidly- misses out the
> definition for column ELAPSED_TIME (along with one or two others). At
> least, in the 9i documentation it does. What the point of partial
> documentation is, I have no idea.
>
> However, it does have a definition for UNDO_STAT, and the clue is there.
>
> So, for Prem:
>
> V$UNDOSTAT's MAXQUERYLEN does indeed show the maximum number of seconds
> a report has taken to complete. And judging by other V$SQL... views
> (such as V$SQL_PLAN_STATISTICS), it would indeed appear that
> ELAPSED_TIME is measured in milli-seconds.
>
> But, as the documentation also makes clear: use the MAXQUERYLEN value
> only as a basis for setting the UNDO_RETENTION parameter. Don't, in
> other words, expect it to match columns in other views which sound as if
> they might supply the same sort of information. It's usually a mug's
> game to try and make statistics of any kind in one V$ view match similar
> or indeed identically-named statistics in another V$ view. Unless you
> know Oracle source code by heart, it is unlikely you will be able to
> work out what should match what else, and why it doesn't when it doesn't.
>
> In this specific case, ELAPSED_TIME is highly likely to be the amount of
> CPU time a SQL statement consumed. But that's definitely not the same
> thing as the amount of clock time a query took to complete back on your
> client PC.
>
> Regards
> HJR
10g v$sqlarea definitions:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/dynviews_2112.htm#REFRN30259
ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor
for parsing/executing/fetching
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Dec 11 2004 - 16:17:46 CST
