What does maxquerylen in v$undostat really mean?
premme...@hotmail.com
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
Sybrand Bakker
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
Howard J. Rogers
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
premme...@hotmail.com
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
DA Morgan
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
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Dave
"DA Morgan" <damo...@x.washington.edu> wrote in message
news:1102803357.871449@yasure...
he is using 9i not 10g, why look up the wrong product version?
Howard J. Rogers
Because Daniel has been ticked off by me recently, and is now desperate
to show me being wrong on something, anything.
Of course, to do so on this occasion, he has to ignore the "at least in
the 9i documentation" sentence I included in my original post. But at
least it *looks* like he can be more accurate than me on a quick reading.
In his mind, that would somehow vindicate his other posts with which I
have taken issue.
Regards
HJR
Howard J. Rogers
[snip]
> 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,
>
Well, "thinking" is not ideal for these sorts of discussions. Tests and
test cases are what you need. I'll do some if I get the time.
Regards
HJR
HansF
>
> he is using 9i not 10g, why look up the wrong product version?
>
Standard investigation technique?
One public place to look for information known to be missing, or
incorrect is in the next version of the docco.
After all, Howard had already stated it's missing in the 9i docco ...
and we already know that Oracle will not generally release updates (ie.
patches) to the software on OTN, instead waiting on the next public
release. Can't see why we'd expect anything different from the docco
which, like software, is subject to the possibility of bugs.
/Hans
Howard J. Rogers
> Dave wrote:
>
>>
>> he is using 9i not 10g, why look up the wrong product version?
>
>
> Standard investigation technique?
Generally, a dangerous one. Just because it's X in 10g, doesn't mean it
was X in 9i. I was even a bit dubious about claiming microsecond
accuracy for the column just because a column of the same name appears
in similar sorts of views in the same version at that degree of
accuracy. If the documentation doesn't tell you precisely what the
answer is, then all else is inference. And that's sometimes a risky
business to be in.
> One public place to look for information known to be missing, or
> incorrect is in the next version of the docco.
I really don't know whether I'd go that far. Certainly, it would provide
a probablistic answer -and maybe, the probability might be quite high.
But it is never simply 'the answer' -which requires Oracle Corp. gets
its act together and fixes its documentation when the error is pointed out.
> After all, Howard had already stated it's missing in the 9i docco ...
> and we already know that Oracle will not generally release updates (ie.
> patches) to the software on OTN, instead waiting on the next public
> release. Can't see why we'd expect anything different from the docco
> which, like software, is subject to the possibility of bugs.
It seems to me that the software issue is different from the
documentation one, as evidenced by the fact that OTN itself talks about
'trial licenses' and 'evaluation purposes' and so on for the free
software downloads, but makes no such qualification regarding the
contents of tahiti.oracle.com.
IE, if you're suggesting that tahiti.oracle.com should come with a big
disclaimer along the lines of "This documentation is not what you'd use
or get if you paid us money, so don't expect it to be entirely
accurate", I could run with that... but it doesn't, so I do.
Regards
HJR
>
> /Hans
HansF
> HansF wrote:
>
>> Dave wrote:
>>
>>>
>>> he is using 9i not 10g, why look up the wrong product version?
>>
>>
>>
>> Standard investigation technique?
>
>
> Generally, a dangerous one. Just because it's X in 10g, doesn't mean it
> was X in 9i. I was even a bit dubious about claiming microsecond
> accuracy for the column just because a column of the same name appears
> in similar sorts of views in the same version at that degree of
> accuracy. If the documentation doesn't tell you precisely what the
> answer is, then all else is inference. And that's sometimes a risky
> business to be in.
Based on my experience, I find Oracle tends to retain definitions for a
term - if they want to change the definition, they tend to change the
name.
If I find something in a specific version, and I can't see a reference
in the documentation, I'll use other tactics to build a temporary image
as a basis for investigation - and then verify using other testing
techniques (including asking here and looking in metalink). I see
nothing dangerous in that.
>
>> One public place to look for information known to be missing, or
>> incorrect is in the next version of the docco.
>
>
> I really don't know whether I'd go that far. Certainly, it would provide
> a probablistic answer -and maybe, the probability might be quite high.
> But it is never simply 'the answer' -which requires Oracle Corp. gets
> its act together and fixes its documentation when the error is pointed out.
>
I did not state that this would yield *the* answer. I stated this was
an investigative technique.
>> After all, Howard had already stated it's missing in the 9i docco ...
>> and we already know that Oracle will not generally release updates
>> (ie. patches) to the software on OTN, instead waiting on the next
>> public release. Can't see why we'd expect anything different from the
>> docco which, like software, is subject to the possibility of bugs.
>
>
> It seems to me that the software issue is different from the
> documentation one, as evidenced by the fact that OTN itself talks about
> 'trial licenses' and 'evaluation purposes' and so on for the free
> software downloads, but makes no such qualification regarding the
> contents of tahiti.oracle.com.
>
Quoting from
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/title.htm
"The information contained in this document is subject to change without
notice. If you find any problems in the documentation, please report
them to us in writing. Oracle Corporation does not warrant that this
document is error-free."
Is that not sufficient qualification?
LWIY/Hans
Howard J. Rogers
> Howard J. Rogers wrote:
>
[snip]
>
>
> Based on my experience, I find Oracle tends to retain definitions for a
> term - if they want to change the definition, they tend to change the name.
"Experience". "Tend". These are generalities. Documentation should
provide specific answers to specific questions. Your comment misses the
point that, without specific, accurate documentation, we cannot know
*for certain* whether your experience is playing us false this time; or
whether the perceived past "tendency" not to change definitions is in
force this time round. We cannot know. Therefore we are guessing and
relying on our subjective experiences and perception of tendencies...
and that is precisely what documentation is there to avoid.
> If I find something in a specific version, and I can't see a reference
> in the documentation, I'll use other tactics to build a temporary image
> as a basis for investigation - and then verify using other testing
> techniques (including asking here and looking in metalink). I see
> nothing dangerous in that.
>
>>
>>> One public place to look for information known to be missing, or
>>> incorrect is in the next version of the docco.
>>
>>
>>
>> I really don't know whether I'd go that far. Certainly, it would
>> provide a probablistic answer -and maybe, the probability might be
>> quite high. But it is never simply 'the answer' -which requires Oracle
>> Corp. gets its act together and fixes its documentation when the error
>> is pointed out.
>>
>
> I did not state that this would yield *the* answer. I stated this was
> an investigative technique.
I didn't claim that you stated it would yield the answer. I drew the
obvious and logical inference from the words you *did* use. Namely: one
investigates something to *find an answer*. You don't investigate to
find the wrong answer, at least, do you? Therefore, investigation is for
a purpose, and to a point. The mere fact you mentioned "investigation
techniques" therefore implies that reading the wrong version of the
documentation can help you find an answer.
I disagree with that. Whatever your personal experience, it is I think
bad practice to *assume* that because something is mentioned in version
10's documentation, it must also have been that way in version 9. You
might remember, for example, the glitch that affected tkprof when they
forgot to ship a 9i-specific version with 9i Release 1: if you ran 8i's
version against a 9i trace file, your elapsed times (ironically enough)
were out by a factor of 10.
So yeah. Your investigation technique can probabilistically help find an
answer/the answer. But there is a probability, too, going across
versions especially, that the answer will be wrong, and hence not a
useful answer at all.
And what there is for you to find exceptional about that, I cannot fathom.
>>> After all, Howard had already stated it's missing in the 9i docco ...
>>> and we already know that Oracle will not generally release updates
>>> (ie. patches) to the software on OTN, instead waiting on the next
>>> public release. Can't see why we'd expect anything different from
>>> the docco which, like software, is subject to the possibility of bugs.
>>
>>
>>
>> It seems to me that the software issue is different from the
>> documentation one, as evidenced by the fact that OTN itself talks
>> about 'trial licenses' and 'evaluation purposes' and so on for the
>> free software downloads, but makes no such qualification regarding the
>> contents of tahiti.oracle.com.
>>
>
> Quoting from
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/title.htm
>
>
> "The information contained in this document is subject to change without
> notice. If you find any problems in the documentation, please report
> them to us in writing. Oracle Corporation does not warrant that this
> document is error-free."
>
> Is that not sufficient qualification?
>
> LWIY/Hans
I don't think we need start WWIII over this. No, it's not a sufficient
qualification. It's an invitation from the Corporation to get a
documentation error corrected (which is most welcome, of course). But
that is rather different from your original "they don't update the free
software downloads as patches are released, so why should we expect them
to do anything different with the documentation". The quotation you cite
itself indicates that we very much *should* expect something different.
Regards
HJR
Niall Litchfield
news:41bbf5a6$0$1082$afc3...@news.optusnet.com.au...
<snip>
My experience, by which I mean I have filed a bug, got it accepted and
corrected, for documentation errors/omissions is that Oracle will only
correct the *next* release of the documentation. At the time I was told, and
believe for the reasons below, that this is Oracle Corp's policy. i.e. they
do correct errors - but only at the next available opportunity; read
release.
My reasons for believing this to be the policy are
1, it was there in writing in my tar and
2, it makes sense. You can't very well ask a customer what patchset of the
9.2 docs they read something in. I guess you could *require* all customers
to use online docs - but that would be really unfriendly. An argument
between support and a customer about what the manual says (as distinct from
what it means) would be really unfortunate.
oh and by the way there is an exception, namely when a patchset changes
documented behaviour, that change gets recorded in the readme for the
patchset. For example the docs (http://tinyurl.com/6ky2y) for OPEN_CURSORS
state "This parameter also constrains the size of the PL/SQL cursor cache
which PL/SQL uses to avoid having to reparse as statements are reexecuted by
a user" which is wrong from 9205 onwards and so noted in the patchset notes
"Starting with patch set 9.2.0.5.0, you can work around this issue by
changing the initialization parameter that determines the upper bound for
PL/SQL cursor caching from the OPEN_CURSORS parameter to the
SESSION_CACHED_CURSORS parameter. "
I agree that the quote that Hans ends with suggests that the docs at tahiti
*may* become more up to date than those on the physical media that customers
get, I don't believe that in fact that is the case.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
HansF
> HansF wrote:
>
>> Howard J. Rogers wrote:
>>
>
> [snip]
>
>>
>>
>> Based on my experience, I find Oracle tends to retain definitions for
>> a term - if they want to change the definition, they tend to change
>> the name.
>
>
> "Experience". "Tend". These are generalities. Documentation should
> provide specific answers to specific questions. Your comment misses the
> point that, without specific, accurate documentation, we cannot know
> *for certain* whether your experience is playing us false this time; or
> whether the perceived past "tendency" not to change definitions is in
> force this time round. We cannot know. Therefore we are guessing and
> relying on our subjective experiences and perception of tendencies...
> and that is precisely what documentation is there to avoid.
By that statement, I surmise that you discount experience. Perhaps you
just discount experience with the exception of your own?
Use of the term 'tend' simply stems from my general dislike of
absolutism - a concept foreign to some.
But I defer to your superior wisdom and hereby refrain from further
discussion on this, or any other topic, in this newsgroup.
TTFN/Hans
Howard J. Rogers
> Howard J. Rogers wrote:
>
>> HansF wrote:
>>
>>> Howard J. Rogers wrote:
>>>
>>
>> [snip]
>>
>>>
>>>
>>> Based on my experience, I find Oracle tends to retain definitions
>>> for a term - if they want to change the definition, they tend to
>>> change the name.
>>
>>
>>
>> "Experience". "Tend". These are generalities. Documentation should
>> provide specific answers to specific questions. Your comment misses
>> the point that, without specific, accurate documentation, we cannot
>> know *for certain* whether your experience is playing us false this
>> time; or whether the perceived past "tendency" not to change
>> definitions is in force this time round. We cannot know. Therefore we
>> are guessing and relying on our subjective experiences and perception
>> of tendencies... and that is precisely what documentation is there to
>> avoid.
>
>
> By that statement, I surmise that you discount experience.
Hans: that's a silly statement. There is a context to my comments.
Please try not to rip them from it.
The context is: can one *rely* on the documentation? Your post states,
essentially, "in my experience, the documentation works like this...".
*In that context*, yes: I am discounting that specific bit of your
experience. Because when it comes to the documentation, I shouldn't have
to weigh up such things. The documentation should be right, or a
bug-report should be raised, and it should be fixed. In *this* version.
And why? Because documentation is specifically intended to be an
explicit statement of objective fact. It shouldn't *need* to be
filtered, or have its gaps plugged, with subjective experience as a
guide to the plugging.
Do I dismiss or discount your experience *generally*? Of course not, and
my words are quite clear about it: "we cannot know whether your
experience is playing us false THIS TIME" means that your experience is
a fine guide on many occasions. Just, perhaps, not THIS ONE.
>Perhaps you
> just discount experience with the exception of your own?
Look, you can get personally abusive if you want. But I'm sticking to
the facts. The OP read the documentation. The documentation misses out
the key piece of information he needs. He's advised to read the wrong
version of the documentation to fill in the gaps. I point out that's
risky, and doesn't actually provide assurance and certainty (as certain
as it needs to be, anyway).
That's all. You're making waaaaaay too much out of it, otherwise.
> Use of the term 'tend' simply stems from my general dislike of
> absolutism - a concept foreign to some.
There's nothing absolutist in pointing out that going cross-version to
fill a gap in the documentation is risky.
> But I defer to your superior wisdom and hereby refrain from further
> discussion on this, or any other topic, in this newsgroup.
I don't ask you to defer to anything. Just don't take things personally
when it's actually matters of fact which are being discussed.
I repeat: the original advice to go cross-version in the documentation
is risky. Not 100% wrong. Not 100% right. Not absolute at all. Just
*risky*.
I also repeat that there is in this specific case no need to go
cross-version, since other V$SQL... views *in the 9i documentation* show
a column of the same name measured in milliseconds. But that, too, is a
risky approach... though it's one I felt compelled to employ myself for
this specific request.
HJR
> TTFN/Hans
DA Morgan
> he is using 9i not 10g, why look up the wrong product version?
Two reasons:
1. Because that is where the definition is.
2. Because many people have 10g and might want the link.