Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance Issue (Urgent ????)

Re: Performance Issue (Urgent ????)

From: Gaja Krishna Vaidyanatha <>
Date: Wed, 28 Jun 2000 09:09:22 -0700 (PDT)
Message-Id: <>


Some points to what you wrote:

  1. You can set and use a specific rollback segment within a PL/SQL block by calling dbms_transaction.use_rollback_segment('RBSNAME'); You mention that you tried real hard to get this feature working....wondering if you tried the above! That usually does the trick. Also remember that the first commit/rollback in your PL/SQL block will unset this, so you need to repeat it if needed.
  2. So setting a specific rollback segment using the method described in #1, should hopefully allow you to revert back to your original RBS INITIAL size. Changing something globally to achieve something very specific, should be avoided...I guess you know that by now.
  3. I did not see anything drastic that stood out at me in the statistics that you shared. But then again, you did not share the entire report.txt.
  4. You can, if and when you need, access and input your report.txt and get a pretty decent set of recommendations on the "state of your instance and database". This site is owned by a guy named Anjo Kolk who is one of the key individuals in Oracle's RDBMS group, and a lot of us hold him in very high esteem. The man knows what he is talking about.

Please realize that utlbstat/utlestat runs that are longer than 15 minutes will tend to mask the actual problem and may not be very useful. Run it when the load is high and you will get a good snapshot of what is going on.

4) Myth : 65% database buffer cache hit ratio is bad performance. Says who....compared to what - 95% (guessing)?

Even if your database buffer cache hit ratio goes from 95% to 65%, that does not mean you have bad performance. I think we have covered the topic of cache hit ratios and their relevance to system performance, in pretty good detail, in a thread last week. So long as your system throughput is fine (the jobs are getting done, transactions are cranking through your system), you should not lose sleep over it.

5) Further, how soon did you measure this ratio? If you measured it immediately after creating the larger rollback segments, then yes you will see a decrease. These are brand new objects. Also, if you have OPTIMAL set, SMON will attempt to shrink the rollback segments, if and when they grow above 60M. That means deallocation of extents, which means those blocks in the database buffer cache will go away. And if your jobs constantly increase the size of the RBSs above 60M, there you go, brand new blocks that need to be allocated and brought into the database buffer cache.

Hope this helps,


P.S.: On a very friendly note and please do not take this the wrong way. You might want to refrain from putting "Urgent" flags on messages. We all have day jobs and we all try to help each other the best we can, as fast as we can, in the time we can. I can say that the level of camaraderie on this list is very high and we have some outstanding people who make this list what it is.

Making something "Urgent" (which I am sure is urgent in your life) does not necessarily get you faster responses. In fact some folks may just refuse to respond, when they see the "Urgent" flag. Just something to remember in the future. OK, I am off the soapbox...;-)

Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170 Received on Wed Jun 28 2000 - 11:09:22 CDT

Original text of this message