Re: What does maxquerylen in v$undostat really mean?

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1102803357.871449_at_yasure...
> 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)

he is using 9i not 10g, why look up the wrong product version? Received on Sat Dec 11 2004 - 17:12:48 CST