Re: MAXQUERYLEN clarification

On Apr 19, 12:18 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "David.E.M...._at_gmail.com" <David.E.Mur..._at_gmail.com> a écrit dans le message de news:
> 1176998310.151385.171..._at_n76g2000hsh.googlegroups.com...
> |I am trying to understand MAXQUERYLEN. If anyone could help I would
> | appreciate it. This is what I know:
> |
> | MAXQUERYLEN - The length of a query is measured from the cursor open
> | time to the last fetch/execute time of the cursor. Also, I have heard
> | of it as - time for a process to complete, time for a transaction to
> | complete, time for a report to complete, etc.
> |
> | I ask this because of trouble with my undo space growing and I would
> | like to split up a function to reduce this from happening, but I want
> | to make sure I am on the right track without going further.
> |
> | The script I would like to split is compiled into the db as a
> | procedure. The script starts by dropping a bunch of tables. Then
> | there is the CREATE OR REPLACE PROCEDURE line followed shortly after
> | by a BEGIN and the last line is an END. Within the procedure there
> | are numerous begin/end blocks. There are also numerous DELETE FROM
> | lines. There are COMMIT statements throughout.
> |
> | Is MAXQUERYLEN the time it takes for the entire procedure to run?
> | Is MAXQUERYLEN the time it takes between COMMIT statements?
> | Is MAXQUERYLEN the time it takes for the innermost begin/end blocks to
> | complete?
> | Is MAXQUERYLEN the time it takes for the outermost begin/end blocks to
> | complete?
> | Is MAXQUERYLEN the time it takes for a single line of sql to complete?
> |
> | This was probably covered in plsql 101 but I never took that...
> |
> | Thanks!
> |
>
> What about making some tests?
> Just a small procedure with several blocks calling dbms_lock.sleep and
> commiting. Simple, easy, fast.
>
> Regards
> Michel Cadot

I created a little procedure that logged a message to a table, then did a USER_LOCK.SLEEP(2000), and then logged another message. After I run the procedure I see that the messages are exactly 20 seconds apart in my log table but in v$undostat the maxquerylen is 4. I also put a little for loop that counted up to 10 million, the log message were exactly 45 seconds apart but the maxquerylen is still only 4. maxquerylen for all entries in the v$undostat table are all 3,4, or 5.

Any comments on something else I could try?

I read through this post
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/4ee53413cce2ce1c/40fae5a685406152?lnk=gst&q=maxquerylen)+from+v%24undostat&rnum=1#40fae5a685406152 and I see that maxquerylen is not the maximum length for any given query (which is what one would assume).

I also realise that MAXQUERYLEN is The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor.

But can someone explain to me what that means? Received on Thu Apr 19 2007 - 16:03:50 CDT