RE: Users reading from rollback segments
Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City
Re: Users reading from rollback segments
Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. Plse, correct me if I'm wrong. Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 12:18 PM Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth
Re: Users reading from rollback segments
The delayed_logging_block_cleanouts parameter does not force or suppress cleanouts. It just makes any cleanouts that do occur get logged as redo entries. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Wed, 27 Feb 2002, Diego Cutrone wrote: Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. - Original Message - Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people
Re: Users reading from rollback segments
Jeremiah : What I meant was that the delayed_logging_block_cleanouts parameter (=FALSE) will make the next reader of the block to cleanout that block. Now, if this parameter's value is TRUE (default in Oracle 7 and 8.0) the next reader will NOT clean out the block (it will read the rollback segment and generate the appropiate block image but it will not clean the block out). The delayed logging block clean out feature delays the redo for the cleanout blocks until it could be logged in combination with another redo for another change to the block. So if you have this parameter set in TRUE the block clean out will be made only when you'll make another change to these blocks. According to what Glenn was saying: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). I think that there's a way you can be sure that noone will need to read some RBS blocks for a cleanout operation. Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 4:38 PM The delayed_logging_block_cleanouts parameter does not force or suppress cleanouts. It just makes any cleanouts that do occur get logged as redo entries. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Wed, 27 Feb 2002, Diego Cutrone wrote: Hi Glenn and list: As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). If you're under Oracle 7 or 8.0, I think that you could set delayed_logging_block_cleanouts=FALSE (to make sure that the next reader will do the cleanout), and execute a FTS on the table after the commit, this would make all the block cleanouts for you. This way you can be sure that noone will need to read this RBS blocks for a cleanout operation. - Original Message - Thank you all. I agree now that there is no way to tell if someone will need the rollback segment data, EVEN if no queries are running when all transactions are committed (due to delayed block cleanout - I had forgotten about this!). As you mentioned, even if noone is reading from rollback at the time all transactions commit, a query may be executing which will access rollback later in its current run (which started prior to the commit). This was very helpful information and an eductional discussion. I'll post my rollback queries later today... -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading
RE: Users reading from rollback segments
Here is the Query you are looking for. set lines 132 set pages 30 col rr heading 'RB Segment' format a18 col os heading 'OS User' format a10 col te heading 'Terminal' format a10 col sid format 9 col spid format 99 select r.name ROLLBACK SEG, s.sid, s.serial#, s.username,osuser,START_TIME from v$session s, v$transaction t, v$rollname r where s.taddr=t.addr and t.xidusn = r.usn order by 1 / Brijesh Gupta Oracle Production DBA Air Liquide Inc. Phone : (713) 438 6259 Fax : (713) 438-6825 Cell : (713) 539-1375 Email : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ~~~ -Original Message- Sent: Tuesday, February 26, 2002 3:03 PM To: Multiple recipients of list ORACLE-L Thanks for the replies. I have all sorts of neat queries (which I can post) which show me gobs of information about my rollback segments (sizes, extents, optimal, shrinks, active transactions, block used by those transcations, ad infinitum...). HOWEVER, I still cannot find an answer to my original question; Is there a way to tell if anyone is reading from the rollback segments? Readers do not open transactions, correct? So they will not show up on the queries most of us are running against v$rollxxx and v$transaction. Where can I find out of someone is using the undo info in the rollbacks for read consistency? In other words, how do I find the readers (from rollback, not from the tables themselves)? I do not want to issue a shrink (and thus risk a ORA-01555) if people are still using the rollback for read consistency. To answer another reply's question: I am shrinking the rollbacks right before I run a large batch job, so as to give the job the maximum amount of space in the rollback tablespace. (I cannot utilize 'set transaction use ...' as this is an Oracle Apps job which actually does many transactions (re: purges)). -Original Message- From: Glenn Travis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Subject: Users reading from rollback segments Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gupta, Brijesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Users reading from rollback segments
Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
Why would you want to shrink a rollback segment? -Original Message- From: Glenn Travis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Subject: Users reading from rollback segments Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
To partially answer your question, xacts in v$rollstat will tell you if there are any active transactions in the rollback segment. Raj Glenn Travis Glenn.TravisTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @sas.comcc: Sent by: Subject: Users reading from rollback segments root@fatcity. com February 26, 2002 02:38 PM Please respond to ORACLE-L Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
Is there a way to check if the rollback segment is in use first? I use following script to see which rollbacks are in use...and then shrink to certain size if it requires to do that... select substr(a.os_user_name,1,8) OS User ,substr(e.username,1,8) DB User , substr(b.object_name,1,30) Object Name , substr(b.object_type,1,10) Type , substr(c.segment_name,1,30) RBS , e.process PROCESS , substr(d.used_urec,1,8) # of Records from v$locked_object a , dba_objects b , dba_rollback_segs c , v$transaction d , v$session e where a.object_id = b.object_id and a.xidusn = c.segment_id and a.xidusn = d.xidusn and a.xidslot = d.xidslot and d.addr = e.taddr / Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 26 Feb 2002 11:38:28 -0800 Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
SELECT segment_name, USN,EXTENTS,RSSIZE,HWMSIZE,optsize,xacts,aveactive,WRAPS,shrinks FROM V$ROLLSTAT, dba_rollback_segs where usn=segment_id xacts =1 is in use. otherwise it is 0. oracle 8.1.6 you can take an active rollback segment offline, but also kill the transaction. 8.17 supposed to fix the bug. I assume it will let the transaction finished. Joan Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
Thanks for the replies. I have all sorts of neat queries (which I can post) which show me gobs of information about my rollback segments (sizes, extents, optimal, shrinks, active transactions, block used by those transcations, ad infinitum...). HOWEVER, I still cannot find an answer to my original question; Is there a way to tell if anyone is reading from the rollback segments? Readers do not open transactions, correct? So they will not show up on the queries most of us are running against v$rollxxx and v$transaction. Where can I find out of someone is using the undo info in the rollbacks for read consistency? In other words, how do I find the readers (from rollback, not from the tables themselves)? I do not want to issue a shrink (and thus risk a ORA-01555) if people are still using the rollback for read consistency. To answer another reply's question: I am shrinking the rollbacks right before I run a large batch job, so as to give the job the maximum amount of space in the rollback tablespace. (I cannot utilize 'set transaction use ...' as this is an Oracle Apps job which actually does many transactions (re: purges)). -Original Message- From: Glenn Travis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Subject: Users reading from rollback segments Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
XACTS is the NUMBER of active transactions in the rollback segment. 0 or 0. However, I am not looking for transactions, I am looking for queries reading from the rollback space for read consistency purposes. I found an answer to my second question and it is NO. You can take the rollback segment offline and it will succeed if it is being used. It will just go into an 'OFFLINE PENDING' state until all users are out. So I don't think I can use this logic. Question: How does Oracle know it is in 'use'? What does in 'use' mean? Active transactions plus readers? I would not think Oracle would take a rollback segment offline if users are reading from it (this doesn't necessarily mean they are holding active transactions). So if Oracle knows it is 'in use', how can I get that info too? -Original Message- From: Joan Hsieh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 3:51 PM To: Multiple recipients of list ORACLE-L Subject: Re: Users reading from rollback segments SELECT segment_name, USN,EXTENTS,RSSIZE,HWMSIZE,optsize,xacts,aveactive,WRAPS,shrinks FROM V$ROLLSTAT, dba_rollback_segs where usn=segment_id xacts =1 is in use. otherwise it is 0. oracle 8.1.6 you can take an active rollback segment offline, but also kill the transaction. 8.17 supposed to fix the bug. I assume it will let the transaction finished. Joan Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Users reading from rollback segments
Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Users reading from rollback segments
Glenn, An approximation of what you require *may* be worked out this way: 1. Snoop v$session_wait for all events that start with 'db file%' and see if P1 is in a list of data files that belongs to the RBS tablespace(s). If you do see sessions that have P1s indicating RBS files, then you *may* be reading Rollback. It is not necessarily on the older entries, but see below: 2. Track the start time of that Query from the SID - using V$SESSION and V$PROCESS. If the start time is reasonably old, then you *may* have an issue, as ORA-01555 errors can be expected for those queries that started *before* the entry you are going to zap via shrink was made. I have successfully used P1 and P2 to indicate the progress of a long running query by working out the segments being accessed and matching that with an EXPLAIN PLAN. This is especially useful if multiple table joins are involved. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving
RE: Users reading from rollback segments
John, I was thinking along the same lines, but then its not the sure shot way to do it. I was also wondering if one could instead do it with x$bh and dba_extents. Just a thought. Thanks Raj John Kanagaraj john.kanagaraTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: RE: Users reading from rollback segments [EMAIL PROTECTED] om February 26, 2002 06:09 PM Please respond to ORACLE-L Glenn, An approximation of what you require *may* be worked out this way: 1. Snoop v$session_wait for all events that start with 'db file%' and see if P1 is in a list of data files that belongs to the RBS tablespace(s). If you do see sessions that have P1s indicating RBS files, then you *may* be reading Rollback. It is not necessarily on the older entries, but see below: 2. Track the start time of that Query from the SID - using V$SESSION and V$PROCESS. If the start time is reasonably old, then you *may* have an issue, as ORA-01555 errors can be expected for those queries that started *before* the entry you are going to zap via shrink was made. I have successfully used P1 and P2 to indicate the progress of a long running query by working out the segments being accessed and matching that with an EXPLAIN PLAN. This is especially useful if multiple table joins are involved. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:36 PM To: Multiple recipients of list ORACLE-L Subject: RE: Users reading from rollback segments People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA
RE: Users reading from rollback segments
Jeremiah is correct. In addition, rollback segments are read as part of the delayed block cleanout process, and it's not possible to predict that, either. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: People also obtain read consistency data from the rollback segments after transactions have committed. If a query began before someone else's transaction committed, but continues reading, then needs the reconstruct the data from before the commit, in needs rollback data that is both committed and impossible to predict. I suppose if you could determine that the age of all undo entries in the portion of RBS that you will obliterate through shrinking are older than any query currently running in the database, then you could be sure that the shrink will not cause an ORA-01555. But the flaw in your logic is believing that once committed, rollback entries will not be needed for read consistency. They very well may. Because a query doesn't know what rollback entries it may need further down the road, you can't predict if your shrink will obliterate undo entries that a long-running query might need in the future. You keep asking if we can tell who is reading the rollback segments. The answer is that it doesn't matter. What you really need to ask is if we can tell who will need to read the rollback segments sometime soon. And you can't. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Hmmm. I think I CAN predict FUTURE needs of the rollback segments. If there are transactions using the rollback space, users MAY need it. If there are no transactions, then they won't. If I were able to know who is reading from rollback, I would know if shrinking might cause ORA-01555. Tell me if I'm off on this... Users will not read from the rollback segment unless they need read-consistent data due to an open transaction against the data they are looking for (thus reading the redo or undo info from rollback). Otherwise they read from the data segments (committed data). Oracle will not shrink the rollback segment if it contains open transactions. So, if there are no users reading from rollback and I issue a 'shrink' command, and it works, then the transactions are complete and any user coming in after that will read from the data segments. If there are no users reading from rollback and I issue a 'shrink' command, and it DOES NOT work, then the transactions are NOT complete and any user coming in after that will read from the rollback segments (the data is still there). If there ARE users reading from rollback and I issue a 'shrink' command, and it works, then users run the risk of getting ORA-01555 (the data MAY be gone). Which is exactly why I asked my original question (How do I identify READERS of the rollback segments?) :) -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED]] Since you cannot predict who might need to generate consistent reads from the RBS in the FUTURE, you cannot predict if you will cause ORA-01555 or not by shrinking. Your best bet is to get rid of people bloating up RBSs by limiting their growth, and enforcing the use of smaller transactions. That way you won't have to shrink so much. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 26 Feb 2002, Glenn Travis wrote: Is there a way to tell if anyone is reading from the rollback segments? I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error). Is there a way to check if the rollback segment is in use first? Can I try to take it offline? Will it fail if there is someone reading from it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California--
