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

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
HJR: Thanks. Yes, I had read Oracle's 9.2 documentation before posting and as you said some of the columns are not documeneted. Also, there was so much difference in elapsed_time of v$sqlarea and maxquerylen, I wanted to clarify. I still don't think any of my queries really took 3-4 hours to complete as maxquerylen seems to indicate. Regards,

Prem
of the fileds Received on Sat Dec 11 2004 - 16:14:54 CST