Performance Issue (Urgent ????)

From: Rajesh Dayal <Rajesh_at_ohitelecom.com>
Date: Wed, 28 Jun 2000 17:45:43 +0400
Message-Id: <10542.110655@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

=_NextPart_000_01BFE107.2DBBD66E Content-Type: text/plain

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

=_NextPart_000_01BFE107.2DBBD66E Content-Type: text/plain; name="part_report.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="part_report.txt"

Statistic                   Total        Per Transact Per Logon    Per =

Second =20

--------------------------- ------------ ------------ ------------ =
------------
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      =

Received on Wed Jun 28 2000 - 08:45:43 CDT