1 2 Previous Next 20 Replies Latest reply: Jan 1, 2013 4:43 AM by 955912 Go to original post RSS
      • 15. Re: Changing undo retention_time , is this recommended by orace ?
        jgarry
        >

        >
        To Ed Stevens

        I did n't catch what do you mean ? as well as what's the relationship to post this question here ?
        I'll tell you I have a car. I'll ask you if the spark plugs can be changed and and if I change them, will that provide a good solution.
        Now, how are you going to answer that question ?

        +" if the spark plugs can be changed or removed " what ? "You know the answer"+

        Thanks  Edstevens :
        The idea is to illustrate that we don't know your situation, we can't see your screen, we need enough information to answer the question. There is a basic problem with "best practices" or "Oracle recommendations," and that is, they often don't contain the parameters and boundaries of the issue they are addressing. So when asking a question here, you have to set some boundaries: What exact version of the database, what issue are you trying to address. Databases are complicated systems, and many parameters affect each other and the various issues in complicated ways. You have to understand the concepts to even begin to understand the consequences. Have you read about undo in the concepts manual?

        See http://www.catb.org/esr/faqs/smart-questions.html We want to help you, you need to help us help you.

        Also, asktom.oracle.com has some informative conversations if you search for the parameter.
        • 16. Re: Changing undo retention_time , is this recommended by orace ?
          EdStevens
          952909 wrote:
          Hi Edstevens ;

          We need solutions and exact answers (when lacking ) so we are coming here
          to get answers from experts. If any one comes to me for clarification as much as
          possible we tried to resolve it. if not , we are coming to ask "Oracle Gurus"

          retention_time=900 seconds;
          I never seen oracle docs recommend to change retention_time. we are changing
          retention_time for our solutions. For ex : i am running query with millions of record ,
          Undo tablespace is getting filled quickly.

          *1. My option*
          Before running the query ,  should analyze weight of the query ?
          we can change undo as "AUTO EXTEND" .

          *2. Some one recommended to increase undo_retention time ?*

          I strongly dis agree  why  ? if we are running long updated query ,
          Here undo it's just like a "circular buffer" If retention_time crossed , it will over write.
          Even retention_time not crossed , it will over write.

          I hope , i can get good solution  for  different opinions  i.e 1 and 2 ?

          To Ed Stevens

          I did n't catch what do you mean ? as well as what's the relationship to post this question here ?
          I was trying to make a point by drawing an analogy. I guess my effort is wasted on the uncomprehending. After reviewing your entire posting history I hereby withdraw from any further efforts.


          I'll tell you I have a car. I'll ask you if the spark plugs can be changed and and if I change them, will that provide a good solution.
          Now, how are you going to answer that question ?

          +" if the spark plugs can be changed or removed " what ? "You know the answer"+

          Thanks  Edstevens :

          Edited by: 952909 on Dec 30, 2012 11:58 AM
          • 17. Re: Changing undo retention_time , is this recommended by orace ?
            sb92075
            Problem Exists Between Keyboard And Chair
            • 18. Re: Changing undo retention_time , is this recommended by orace ?
              VenkatB
              Hi

              Of course it's very much tunable parameter and in fact you may have to change it based on your requirements. However I would clarify couple of points here.

              a) Even if your UNDO_RETENTION is whether 10 minutes or 10000 minutes, Oracle never guarantees that your UNDO will not expire or expire prematurely. UNDO_RETENTION is only a directive and not a hard limit.
              b) You should size your UNDO tablespace sufficiently enough to "respect" your undo retention. Again read point 1)
              c) It's not really advisable to set your UNDO tablespace to autoextend (please highlight "advisable"). You can check MAXQUERYLEN in V$UNDOSTAT to see the longest running query in your database and configure your undo retention to something bigger than that.

              Coming back to the point a), Oracle will still reuse the expired undo even if the undo retention is less than that duration.

              To configure undo_retention, you have to basically size your undo tablespace good enough to respect your undo retention. Also please remember that UNDO can persist across instance shutdown. MAXQUERYLEN in V$UNDOSTAT can be treated as your starting point to plan your retention.

              Regards
              Venkat
              • 19. Re: Changing undo retention_time , is this recommended by orace ?
                Aman....
                You must understand the things before jumping on to do any kind of play around with them.
                952909 wrote:
                Hi Edstevens ;

                We need solutions and exact answers (when lacking ) so we are coming here
                to get answers from experts. If any one comes to me for clarification as much as
                possible we tried to resolve it. if not , we are coming to ask "Oracle Gurus"

                retention_time=900 seconds;
                I never seen oracle docs recommend to change retention_time. we are changing
                retention_time for our solutions. For ex : i am running query with millions of record ,
                Undo tablespace is getting filled quickly.

                *1. My option*
                Before running the query ,  should analyze weight of the query ?
                we can change undo as "AUTO EXTEND" .
                What is in the first place weight of a query, can you care to explain? If you are going to keep on generating Undo data and it's filling up the undo tablespace and you are running out of space, there is nothing that UNDO_RETENTION has to do with it. You are going to get the error can't extend teh Undo tablespace and that's all!

                About your statement that you didn't see docs recommending to change the value of the parameter, did you see this?
                http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams262.htm#REFRN10225

                The parameter is modifiable that means you can change it but that depends on what kind of situation you are into. If you are managing a db that's used for college's project and would never likely to get any kind of workload, yes 900 seconds would serve the purpose well but if you are having a db that's used a complete OLTP and is getting a lot of 1555, you should give a thought of changing this parameter along with the other changes like to the tablespace size.
                *2. Some one recommended to increase undo_retention time ?*

                I strongly dis agree  why  ? if we are running long updated query ,
                Here undo it's just like a "circular buffer" If retention_time crossed , it will over write.
                Even retention_time not crossed , it will over write.
                Again, you are making judgemental calls withuot understanding how things work! If there is an on-going transaction, Oracle would never make those buffers get overwritten which are currently in use by your transaction. There is nothing that the retention time has to do with it. Understand this that the retention parameter comes into the play when the transaction has already got ended and you are still running most likely query(s) which are still depending on the old data that's being updated and committed now. If there is an actve transaction , Oracle would keep the undo related to it till eternity.
                I hope , i can get good solution  for  different opinions  i.e 1 and 2 ?
                Read the reply given and read the documentation that explains very well how the transactions work.
                To Ed Stevens

                I did n't catch what do you mean ? as well as what's the relationship to post this question here ?
                I'll tell you I have a car. I'll ask you if the spark plugs can be changed and and if I change them, will that provide a good solution.
                Now, how are you going to answer that question ?

                +" if the spark plugs can be changed or removed " what ? "You know the answer"+

                Thanks  Edstevens :
                And why would you want to change a spark plug, just like that-without having any issues?

                Aman....
                1 2 Previous Next