Rajesh,
Some points to what you wrote:
- 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.
- 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.
- 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.
- You can, if and when you need, access www.oraperf.com 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,
Gaja.
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...;-)
- Rajesh Dayal <Rajesh_at_ohitelecom.com> wrote:
> Hi DBAs
> Could someone point me what could be the performance impact
> of
> increasing the rollback segment size? Recently I had increased
> the RBS
> size from 100K initial extent to 1M initial extent and optimal
> size 60M
> (Oracle 7.3.4/DEC Unix with a maxextents limit of 121). So
> that some of
> the batch jobs (that are fired on normal time) don't fail
> because of
> space problem.
> I could have used "SET TRANSACTION USE RBS ", but the job is
> fired through a PL/SQL block. I tried without luck to make the
> PL/SQL
> block, use one specific RBS (bigger one). So I had to increase
> all the
> RBS storage settings. Now the performance has dipped down a
> lot :~((
> I had a glance of report.txt (utl*stat) in the begining I
> found
> that the DB Buffer Cache hit ratio has come down to 65%.
> So whether the size of RBS can really have impact on DB
> Buffer
> Cache Hit Ratio ?? If yes then how? Else other possible
> reasons ??
> I am attatching some other points of botheration from
> report.txt.. Could someone pin-point the bottleneck area and
> refer me
> to some URL/Sites that explain report.txt ??
>
> Thanks a Lot...
> Rajesh
>
>
> > Statistic Total Per Transact Per
> Logon Per Second
> --------------------------- ------------ ------------
> ------------ ------------
> CPU used by this session 219457 1590.27
> 1769.81 46.88
> CPU used when call started 219366 1589.61
> 1769.08 46.86
> DBWR buffers scanned 70520 511.01
> 568.71 15.07
> DBWR free buffers found 52827 382.8
> 426.02 11.29
> OS Integral unshared data s 4607850 33390.22
> 37160.08 984.37
> OS Integral unshared stack 35740 258.99
> 288.23 7.64
> OS Involuntary context swit 459078 3326.65
> 3702.24 98.07
> OS Maximum resident set siz 134280 973.04
> 1082.9 28.69
> OS Page faults 159 1.15
> 1.28 .03
> OS Page reclaims 18776 136.06
> 151.42 4.01
> OS Socket messages received 49997 362.3
> 403.2 10.68
> OS Socket messages sent 53929 390.79
> 434.91 11.52
> OS Swaps 1 .01
> .01 0
> OS System time used 33981 246.24
> 274.04 7.26
> OS User time used 180339 1306.8
> 1454.35 38.53
> OS Voluntary context switch 624658 4526.51
> 5037.56 133.45
> SQL*Net roundtrips to/from 962982 6978.13
> 7765.98 205.72
> background timeouts 4674 33.87
> 37.69 1
> bytes received via SQL*Net 13288026 96290.04
> 107161.5 2838.72
> bytes sent via SQL*Net to c 957086481 6935409.28
> 7718439.36 204461.97
> calls to get snapshot scn: 433716 3142.87
> 3497.71 92.65
> calls to kcmgas 454 3.29
> 3.66 .1
> calls to kcmgcs 98 .71
> .79 .02
> calls to kcmgrs 439062 3181.61
> 3540.82 93.8
> cluster key scan block gets 64109 464.56
> 517.01 13.7
> cluster key scans 26206 189.9
> 211.34 5.6
> consistent gets 12658055 91725.04
> 102081.09 2704.13
> execute count 844687 6120.92
> 6811.99 180.45
> free buffer inspected 1283 9.3
> 10.35 .27
> free buffer requested 1243234 9008.94
> 10026.08 265.59
> no work - consistent read g 9087489 65851.37
> 73286.2 1941.36
> opened cursors cumulative 9057 65.63
> 73.04 1.93
> physical reads 1239418 8981.29
> 9995.31 264.78
> physical writes 4271 30.95
> 34.44 .91
> process last non-idle time 49379362392 357821466.61
> 398220664.45 10548891.77
> recursive calls 958690 6947.03
> 7731.37 204.8
> recursive cpu usage 60282 436.83
> 486.15 12.88
> redo blocks written 3145 22.79
> 25.36 .67
> redo entries 10265 74.38
> 82.78 2.19
> redo size 2699274 19559.96
> 21768.34 576.64
> redo small copies 10264 74.38
> 82.77 2.19
> redo wastage 466913 3383.43
> 3765.43 99.75
> session connect time 49379362392 357821466.61
> 398220664.45 10548891.77
> session logical reads 12698386 92017.29
> 102406.34 2712.75
> session pga memory 30170104 218623.94
> 243307.29 6445.23
> session pga memory max 30677968 222304.12
> 247402.97 6553.72
> session uga memory 372352 2698.2
> 3002.84 79.55
> session uga memory max 21247016 153963.88
> 171346.9 4538.99
> table scan blocks gotten 3892861 28209.14
> 31394.04 831.63
> table scan rows gotten 27432513 198786.33
> 221229.94 5860.4
> user calls 962426 6974.1
> 7761.5 205.6
>
Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com
Received on Wed Jun 28 2000 - 11:09:22 CDT
