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!

"David.E.M....@gmail.com" <David.E.Murphy@gmail.com> a écrit dans le message de news:
1176998310.151385.171770@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

On Apr 19, 12:18 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "David.E.M....@gmail.com" <David.E.Mur...@gmail.com> a écrit dans le message de news:
> 1176998310.151385.171...@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....fae5a685406152
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?