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

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 12 Dec 2004 07:29:35 +1100
Message-ID: <41bb58b7$0$5103$afc38c87@news.optusnet.com.au>

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 Received on Sat Dec 11 2004 - 14:29:35 CST