1 2 Previous Next 19 Replies Latest reply on Mar 5, 2013 2:53 AM by Aman.... Go to original post
      • 15. Re: Question about Undo Tablespace?
        Ecimen
        Rob_J wrote:
        OK, that's fine, so what makes you say that you are generating too much UNDO? What is the problem you are trying to resolve? Or you just wanted to know if you could reduce UNDO writing?
        Our log tables generating too much undo because we need logging all this data (insert and update mostly) ... but we dont care about these log tables , we want to reduce undo writing for these log tables so we could use flashback to query older timestamp due to more space in undo tablespace...
        • 16. Re: Question about Undo Tablespace?
          mtefft
          we want to reduce undo writing for these log tables so we could use flashback to query older timestamp due to more space in undo tablespace...
          Then the answer is to set parameter undo_retention to cover the amount of time you want. You should do this regardless of whether activity on these particular tables are creating UNDO.

          Yes, this might require more space in your UNDO tablespace.

          By the way, if these are truly 'logging' tables, I presume the activity on them is entirely INSERTs. But an INSERT generates very, very little UNDO. (To undo an INSERT, you can just delete it, so the UNDO does not need the full 'payload' of the INSERT that it covers.) Are you sure that the activity on these tables is actually generating a lot of UNDO?
          • 17. Re: Question about Undo Tablespace?
            sb92075
            UNDO for INSERT is VERY small; only DELETE rowid.

            I am willing to bet that if you disabled all application logging simply for debugging test,
            that the application performance does not measurably change.
            • 18. Re: Question about Undo Tablespace?
              jgarry
              You can use manual rollback segments and set transactions to use them to control whatever problem you are creating for yourself, but that would be silly these days, as compared to just throwing disk at it in the default auto mode. What actual problem are you trying to solve, are you seeing some undo waits or errors?
              • 19. Re: Question about Undo Tablespace?
                Aman....
                Ecimen wrote:
                Rob_J wrote:
                OK, that's fine, so what makes you say that you are generating too much UNDO? What is the problem you are trying to resolve? Or you just wanted to know if you could reduce UNDO writing?
                Our log tables generating too much undo because we need logging all this data (insert and update mostly) ... but we dont care about these log tables , we want to reduce undo writing for these log tables so we could use flashback to query older timestamp due to more space in undo tablespace...
                I think I mentioned that in my other two replies above that it's not possible to disable the Undo generation, or at least I don't know that there is any way to do it! What you can do , if you are worried about the Undo tablespace getting bigger , is that you make it a fixed size Undo tablespace by monitoring the Undo data that's generated . You can use Undo Advisor to do that . With the proposed Undo Retention period of yours, it would advice for the size of the Undo tablespace and you may add about additional 20-30% size to it and you should be okay. But as I said before too, there is no way to stop generating the Undo .

                Aman....
                1 2 Previous Next