Thanks for the answer. With my very little basic knowledge about oracle, I too couldn't see a relationship between these two.

I don't have a link for that.

I appreciate your time during the holidays, and have a happy holiday

Kandy Train

Tom,

For v$undostat, the column MAXQUERYLEN is supposed to show us the time it took to run the longest query during the interval. (This is from the manual: Identifies the length of the longest query (in number of seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter.)

In my database (9.2.0,7) I see that value for MAXQUERYLEN gets higher than my UNDO_RETENTION setting, yet I do not get any ORA-1555 error. For example the value is 13142 in an interval, maxconcurrency is 3, SSOLDERRCNT is zero and my undo_retention is set to 7200.

Yet when I do get an ORA-1555 error, the query length reported in the error matches to the value of MAXQUERYLEN.

Please can you explain this?

Thanks

Well, the way I understand this is that MAXQUERYLEN should be always smaller than undo_retention. If MAXQUERYLEN exceeds undo_retention, I would get ORA-01555.

I do not see the above happening.

From Reference manual:

<quote>
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention.
</quote>

UNDO_RETENTION does not mean that if a query takes more time it will get an ORA-1555 but it may get it.
The opposite is also true: if a query takes less time it may get an ORA-1555 unless you enabled retention guarantee in 10g.

Michel

I have a question here, what's the impact to over estimate the undo_retention? For example, the longest querey is 20 minutes, but I set undo_retention to 7200 ( 2 hrs ); Assume the size of UNDO tablespace is fixed, if there are no free segments on the UNDO tbs, then the segment with comitted data will be overwritten even it's still in the retention period, so what's the impact to set the undo_retention to a value higher than it's really need?

Actually I am doing the work to migrate our current RBS to AUM, I am trying to correctly set up the undo_retention. The longest query in the system is around 4 hrs, the old RBS tablespace is pretty big with 20 RBSs, there was no ORA-1555 error happened for a long time in the past; Can I assume if I keep the UNDO tablespace the same size as it was in the RBS environment, then the undo_retention won't be an issue to get more chance of ora-1555 even it's set up to a small value ( since there was no ora-1555 issues in the RBS environment with the same tablespace size ). We don't care the flash back

But if I upgrade the RBS to AUM, keep the same size of the tablespace, but set the undo_retention low, shall I assume the CHANCE to get ORA-1555 is <= the old RBS mode? ( I understand that it still possible to get ORA-1555, I mena the CHANCE compare with the old RBS mode); If that's not true, can you explain why migrate to UNDO even get worse in avoiding ORA-1555 ( RBS has no undo_retention protection at all ! ).

Thanks a lot

From above with questions -
there can be a downside in extreme cases where physical IO performed by DBWR could go up. If you have 100 blocks to write undo into (we have a small undo I guess) - we might write block 1 fifty times (block 1 of our small undo?) and just have in the buffer cache 100 dirty undo blocks before DBWR is asked to checkpoint it. If you have 1,000 blocks to write undo into (we have more undo than before?) - we might only use block 1 five times - (block 1 of our undo? Why would we write 1*5 - 5 blocks of undo for 1,000 undo blocks?) and use all 1,000 blocks in the buffer cache, causing dbwr to have to write (checkpoint) 1,000 blocks....

I noticed that the client's db has two undo tablespaces- UNDOTBS1 AND UNDOTBS2, the paramater undo_tablespace uses UNDOTBS2 for the automatic undo management. I asked the Admin what's UNDOTBS1 for, and he told that is for some batch jobs.
I wonder if we can combine the two undo tbs to one and just a big one for automatic undo management for all the requirement.
Is that fine? it's 10.2.0 DB.
TIA

The guy to be asked lives in other side of the world.
I have to wait for the chance to ask him.

But can this work?
SET TRANSACTION USE ROLLBACK SEGMENTS UNDOTBS1

ops$tkyte%ORA10GR2> set transaction use rollback segment does_not_exist_in_my_database;

Transaction set.

It could be RAC....

to the reader: it's not RAC.

Hi Tom,

I have the below question :

How to Set up automatic undo management in the PROD database to support the following requirements:

2.1 Avoid ORA- 01555 Snapshot too old errors for queries running up to 90 minutes on average.
2.2 The number of concurrent OLTP users will be approximately 120 during normal business hours.
2.3 The number of concurrent batch processes that will run in the evenings and weekends will be approximately 12 to 15.

Thanks

Hey Tom,

Thanks for clearing this concept. Actually I was confused about the above query containing points 2.2 & 2.3 .
We generally use funda for "concept".

REgards

We are using RAC 2node 10.2.0.4.0 and one RAC node shows maxqrylen to be 1400-1800 accross interval. If I see v$sqlarea I found total elasped time to be 10 sec(as it is in microsecond)
So which figure I am interpretting wrongly.

below are some snaps.
BEGIN_TIME   END_TIME        UNDOTSN   UNDOBLKS   TXNCOUNT MAXQUERYLEN MAXQUERYID    MAXCONCURRENCY
------------ ------------- --------- ---------- ---------- ----------- ------------- --------------
12-jan-2010  12-jan-2010           1      18207      63102        1848 445jdv3pmzb4d             10
12-jan-2010  12-jan-2010           1       8796      31041        1861 445jdv3pmzb4d              8
12-jan-2010  12-jan-2010           1        915       3473        1318 445jdv3pmzb4d              5
12-jan-2010  12-jan-2010           1      16631      56684        1469 445jdv3pmzb4d             11
12-jan-2010  12-jan-2010           1      18294      61514         739 445jdv3pmzb4d             10
12-jan-2010  12-jan-2010           1      19663      63613        1340 445jdv3pmzb4d             12
12-jan-2010  12-jan-2010           1      19127      60079        1445 445jdv3pmzb4d             10
12-jan-2010  12-jan-2010           1      18838      61440        1683 445jdv3pmzb4d             10
12-jan-2010  12-jan-2010           1      17931      60275        1686 445jdv3pmzb4d             10
12-jan-2010  12-jan-2010           1       2430       8034        1445 445jdv3pmzb4d              9
12-jan-2010  12-jan-2010           1       5648      19160        1565 445jdv3pmzb4d             19


SYS@OLTPE> select executions,elapsed_time from v$sqlarea where sql_id='445jdv3pmzb4d';

EXECUTIONS ELAPSED_TIME
---------- ------------
     20472     10385942

Thanks Tom, I will use v$sql from now.
Tom in my system we have 1.5gb of shared pool so frequently executed qry don't get flushed(99%) and I have observed this discrepancy behavior over long period of time in almost each of my Performance tests.
I have a doubt that because of this (high maxqrylen) my tuned_undo value in v$undostat reaches upto 55000 and I start to get undo contentions event.

I understand when you say

open cursor;
go to sleep for a while
fetch a row;
go to sleep for a while
fetch a row;
(repeat...)
close cursor;


This way my understanding is that elapsed time in v$sql will be inflated and not my v$undostat maxqrylen.

Thanks in advance for your reply.

Thanks Tom, I will use v$sql from now.
Tom in my system we have 1.5gb of shared pool so frequently executed qry don't get flushed(99%) and I have observed this discrepancy behavior over long period of time in almost each of my Performance tests.
I have a doubt that because of this (high maxqrylen) my tuned_undo value in v$undostat reaches upto 55000 and I start to get undo contentions event.

I understand when you say

open cursor;
go to sleep for a while
fetch a row;
go to sleep for a while
fetch a row;
(repeat...)
close cursor;


This way my understanding is that elapsed time in v$sql will be inflated and not my v$undostat maxqrylen.

Thanks in advance for your reply.

Thanks Tom for clearing my basic mistake in all this assumption.

Hi, Tom!
Our database is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi ( for Windows)
We received a big growth of undo tablespace few weeks ago.
I perfomed some queries to the database including query to the history of undostat.

select end_time, round(maxquerylen/60,0) maxq, maxquerysqlid,
undotsn, undoblks, txncount,
round(tuned_undoretention/60,0) tuned
from dba_hist_undostat
order by 1

Query Tuned
Maximum Undo # of Retention
Date/Time Minutes SqlID TBS Blocks Trans#(Minutes)
17-09:05 2,752 7k6rstttj9f9t 1 24,676 20,109 1,833
17-09:15 2,762 7k6rstttj9f9t 1 34,129 28,696 1,850
17-09:25 2,772 7k6rstttj9f9t 1 41,338 34,272 1,846
17-09:35 2,782 7k6rstttj9f9t 1 21,000 17,215 1,843
17-09:45 2,792 7k6rstttj9f9t 1 53,951 26,695 1,820
17-09:55 2,802 7k6rstttj9f9t 1 29,585 22,208 1,862
17-10:05 2,808 7k6rstttj9f9t 1 53,941 41,687 1,838
17-10:15 2,818 7k6rstttj9f9t 1 66,433 52,674 1,871
17-10:25 2,828 7k6rstttj9f9t 1 66,291 52,878 1,589
17-10:35 2,838 7k6rstttj9f9t 1 31,253 24,688 1,573
17-10:45 2,848 7k6rstttj9f9t 1 56,226 50,774 1,561
17-10:55 2,858 7k6rstttj9f9t 1 43,874 40,423 1,579
17-11:05 2,868 7k6rstttj9f9t 1 10,578 6,534 1,584
17-11:15 2,879 7k6rstttj9f9t 1 4,948 5,162 1,590
17-11:25 2,889 7k6rstttj9f9t 1 42,314 32,505 1,591
17-11:35 2,899 7k6rstttj9f9t 1 28,947 22,948 1,598
17-11:45 2,909 7k6rstttj9f9t 1 28,158 198,988 1,593
17-11:55 2,919 7k6rstttj9f9t 1 23,800 248,859 1,608
17-12:05 2,929 7k6rstttj9f9t 1 48,582 251,195 1,600
17-12:15 2,940 7k6rstttj9f9t 1 79,496 238,294 1,585
17-12:25 2,950 7k6rstttj9f9t 1 37,848 470,802 1,580
17-12:35 2,960 7k6rstttj9f9t 1 26,518 621,110 1,579
17-12:45 2,970 7k6rstttj9f9t 1 13,692 431,268 1,580
17-12:55 2,980 7k6rstttj9f9t 1 27,472 478,534 1,575
17-13:05 2,990 7k6rstttj9f9t 1 30,533 476,277 1,577
17-13:15 3,000 7k6rstttj9f9t 1 43,902 341,339 1,572
17-13:25 3,011 7k6rstttj9f9t 1 28,526 368,415 1,576
17-13:35 3,021 7k6rstttj9f9t 1 30,222 87,853 1,573
17-13:45 3,031 7k6rstttj9f9t 1 31,563 23,602 1,573
17-13:55 3,041 7k6rstttj9f9t 1 38,777 30,344 1,566

I was confused about the same sqlid in the result, and the value of maxquerylen in dba_hist_undostat (so large maxquerylen!!!).
I get the sql_text for this query from v$sql and can suppose what the applications executes this query. It is very often-executed function.
But I do not understand how it could happen - our users would not tolerate such "performance" ( 2 days)...
You can see also, that maxquerylen of this query increases
permanently.
It is very important for us , because big tuned undo retention causes growth of undo tablespace and ora-01555 occures.
It sems to me like never-ending circle...
Can you, please explain, how maxquerylen is calculated and what do my results mean?
May this only query be the main reason of growing undo tablespace?
Thank you in advance.
Lidia.

Hi, Tom!
Thank you for your answer.
" someone has opened this cursor....and left it open."
Now I understand the reason of such results. And we find the application, that opened this cursor. This is application on Forms , which is called from another form and is not correctly closed.
So, please , next questions about cursors that are not closed.
Our application are all on forms, and there are many forms and reports , which are calling from another forms.Sometimes our users swith off the PC without closing the application.
Will such users behavior cause cursors that are not closed?
Will ever be closed cursors from the broken sessions?
And if some form is opened from morning till evening, our undo_retention must bigger then worktime (8 hours) ?
Thank you in advance.
Lidia

Thank you