Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help reading my report.txt

Re: Help reading my report.txt

From: <emdproduction_at_hotmail.com>
Date: 12 Dec 2006 01:44:16 -0800
Message-ID: <1165916656.742500.159290@79g2000cws.googlegroups.com>


Mark D Powell wrote:
> On Dec 11, 7:05 pm, emdproduct..._at_hotmail.com wrote:
> > Hi,
> >
> > I am using oraperf.com to interpret my report.txt. One thing I don't
> > understand is: the Elapsed time is only 8333 sec (2.3 hour).
> >
> > The "response time" is 2975430, CPU time is 873191, Waint time is
> > 2102239, what is this? Seconds, or Seconds/1000?
> >
> > Thanks for your help.
>
> What version of Oracle?
>
> What does the Oracle produced report say for the same statistics?
>
> How about posting a few lines to demostrate what you are asking about?
>
> HTH -- Mark D Powell --

Mark, thanks for your help. Here is my report.txt. I have done my research, just want all the expert here to have a look, and I can compare with I have found and use this process as educational purpose. And, also, could you tell me what is oraf's reponse time?



QL>
SQL> column library	  format a12 trunc;
SQL> column pinhitratio   heading 'PINHITRATI';
SQL> column gethitratio   heading 'GETHITRATI';
SQL> column invalidations heading 'INVALIDATI';
SQL> set numwidth 10;
SQL> Rem Select Library cache statistics.  The pin hit rate should be
high.
SQL> select namespace library,
  2  	    gets,
  3  	    round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
  4  	       gethitratio,
  5  	    pins,
  6  	    round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
  7  	       pinhitratio,
  8  	    reloads, invalidations

  9 from stats$lib;

LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI
------------ ---------- ---------- ---------- ---------- ----------


BODY                674          1        674          1          0
     0
CLUSTER             131       .985        149       .973          0
     0
INDEX              1000          1       1000          1          0
     0
JAVA DATA            52       .962        118       .966          0
     0
JAVA RESOURC          0          1          0          1          0
     0
JAVA SOURCE           0          1          0          1          0
     0
OBJECT                0          1          0          1          0
     0
PIPE               4610          1       4610          1          0
     0
SQL AREA         482878       .793    3136725       .933       5412
     0
TABLE/PROCED     688624       .999    1168258       .998       1063
     0
TRIGGER           64999          1      64999          1          9
     0

11 rows selected.

SQL>
SQL> column "Statistic"       format a27 trunc;
SQL> column "Per Transaction" heading "Per Transact";
SQL> column ((start_users+end_users)/2) heading "((START_USER"
SQL> set numwidth 12;
SQL> Rem The total is the total value of the statistic between the time
SQL> Rem bstat was run and the time estat was run.  Note that the estat
SQL> Rem script logs on to the instance so the per_logon statistics
will
SQL> Rem always be based on at least one logon. SQL> select 'Users connected at ',to_char(start_time, 'dd-mon-yy hh24:mi:ss'),':',start_users from stats$dates;

'USERSCONNECTEDAT' TO_CHAR(START_TIME ' START_USERS

Users connected at 11-dec-06 13:02:26 : 86

SQL> select 'Users connected at ',to_char(end_time, 'dd-mon-yy hh24:mi:ss'),':',end_users from stats$dates;

'USERSCONNECTEDAT' TO_CHAR(END_TIME,' ' END_USERS

Users connected at 11-dec-06 14:43:30 : 98

SQL> select 'avg # of connections: ',((start_users+end_users)/2) from stats$dates;

'AVG#OFCONNECTIONS:' ((START_USER

avg # of connections: 92

SQL>
SQL> select n1.name "Statistic",

  2  	    n1.change "Total",
  3  	    round(n1.change/trans.change,2) "Per Transaction",
  4  	    round(n1.change/((start_users + end_users)/2),2)  "Per
Logon",
  5  	    round(n1.change/(to_number(to_char(end_time,   'J'))*60*60*24
-
  6  			     to_number(to_char(start_time, 'J'))*60*60*24 +
  7  			     to_number(to_char(end_time,   'SSSSS')) -
  8  			     to_number(to_char(start_time, 'SSSSS')))
  9  		  , 2) "Per Second"
 10  	from
 11  		     stats$stats n1,
 12  		     stats$stats trans,
 13  		     stats$dates
 14  	where
 15  	      trans.name='user commits'
 16  	 and  n1.change != 0
 17  	order by n1.name;

Statistic                          Total Per Transact    Per Logon
Per Second
--------------------------- ------------ ------------ ------------
------------
CPU used by this session          748635        19.25      8137.34
 123.46
CPU used when call started        748617        19.25      8137.14
 123.45
CR blocks created                   2602          .07        28.28
    .43
DBWR buffers scanned               32738          .84       355.85
    5.4
DBWR checkpoint buffers wri        14543          .37       158.08
    2.4
DBWR checkpoints                       2            0          .02
      0
DBWR free buffers found            27747          .71        301.6
   4.58
DBWR lru scans                        69            0          .75
    .01
DBWR make free requests               69            0          .75
    .01
DBWR summed scan depth             32738          .84       355.85
    5.4
DBWR transaction table writ           30            0          .33
      0
DBWR undo block writes              7641           .2        83.05
   1.26
SQL*Net roundtrips to/from        849516        21.84      9233.87
 140.09
active txn count during cle         2192          .06        23.83
    .36
background checkpoints comp            2            0          .02
      0
background checkpoints star            2            0          .02
      0
background timeouts                 7383          .19        80.25
   1.22
branch node splits                     2            0          .02
      0
buffer is not pinned count     116311351      2990.24   1264253.82
19180.63
buffer is pinned count          22973009       590.61    249706.62
3788.42
bytes received via SQL*Net     155766195      4004.58   1693110.82
25687.04
bytes sent via SQL*Net to c    156523231      4024.04   1701339.47
25811.88
calls to get snapshot scn:      12545102       322.52     136359.8
2068.78
calls to kcmgas                    93988         2.42      1021.61
   15.5
calls to kcmgcs                      759          .02         8.25
    .13
change write time                    920          .02           10
    .15
cleanout - number of ktugct         2735          .07        29.73
    .45
cleanouts and rollbacks - c         1152          .03        12.52
    .19
cleanouts only - consistent           36            0          .39
    .01
cluster key scan block gets        14332          .37       155.78
   2.36
cluster key scans                   8253          .21        89.71
   1.36
commit cleanout failures: b            2            0          .02
      0
commit cleanout failures: c            5            0          .05
      0
commit cleanout failures: c            7            0          .08
      0
commit cleanouts                  373279          9.6      4057.38
  61.56
commit cleanouts successful       373265          9.6      4057.23
  61.55
commit txn count during cle        11535           .3       125.38
    1.9
consistent changes                 21155          .54       229.95
   3.49
consistent gets                140151051      3603.13   1523380.99
23111.98
consistent gets - examinati      5000541       128.56     54353.71
 824.63
cursor authentications             27912          .72       303.39
    4.6
data blocks consistent read        21133          .54       229.71
   3.48
db block changes                 1244590           32     13528.15
 205.24
db block gets                    1440156        37.02     15653.87
 237.49
deferred (CURRENT) block cl       154362         3.97      1677.85
  25.46
dirty buffers inspected              151            0         1.64
    .02
enqueue conversions                 2222          .06        24.15
    .37
enqueue releases                  341392         8.78      3710.78
   56.3
enqueue requests                  341388         8.78      3710.74
   56.3
enqueue timeouts                       2            0          .02
      0
enqueue waits                         23            0          .25
      0
execute count                    2150669        55.29     23376.84
 354.66
free buffer inspected                647          .02         7.03
    .11
free buffer requested            1898978        48.82     20641.07
 313.16
hot buffers moved to head o        21202          .55       230.46
    3.5
immediate (CR) block cleano         1188          .03        12.91
     .2
immediate (CURRENT) block c        74146         1.91       805.93
  12.23
index fast full scans (full        10335          .27       112.34
    1.7
index fetch by key               2721937        69.98     29586.27
 448.87
index scans kdiixs1              7663486       197.02     83298.76
1263.77
leaf node 90-10 splits                73            0          .79
    .01
leaf node splits                     450          .01         4.89
    .07
logons cumulative                   1328          .03        14.43
    .22
logons current                        12            0          .13
      0
messages received                  47360         1.22       514.78
   7.81
messages sent                      47360         1.22       514.78
   7.81
no work - consistent read g    117688190      3025.64   1279219.46
19407.68
opened cursors cumulative         485032        12.47      5272.09
  79.99
opened cursors current               447          .01         4.86
    .07
parse count (failures)                81            0          .88
    .01
parse count (hard)                105169          2.7      1143.14
  17.34
parse count (total)               486424        12.51      5287.22
  80.22
parse time cpu                     76825         1.98       835.05
  12.67
parse time elapsed                 79361         2.04       862.62
  13.09
physical reads                   1874933         48.2     20379.71
 309.19
physical reads direct              29357          .75        319.1
   4.84
physical writes                    55916         1.44       607.78
   9.22
physical writes direct             36242          .93       393.93
   5.98
physical writes non checkpo        48872         1.26       531.22
   8.06
pinned buffers inspected             389          .01         4.23
    .06
prefetched blocks                1445552        37.16     15712.52
 238.38
prefetched blocks aged out             8            0          .09
      0
process last non-idle time          6118          .16         66.5
   1.01
recursive calls                  2262715        58.17     24594.73
 373.14
recursive cpu usage               503897        12.95      5477.14
   83.1
redo blocks written               338787         8.71      3682.47
  55.87
redo buffer allocation retr            3            0          .03
      0
redo entries                      658143        16.92      7153.73
 108.53
redo log space requests                3            0          .03
      0
redo log space wait time               8            0          .09
      0
redo size                      156733204      4029.44   1703621.78
25846.5
redo synch time                    18070          .46       196.41
   2.98
redo synch writes                  39028            1       424.22
   6.44
redo wastage                    11234632       288.83    122115.57
1852.68
redo write time                    15541           .4       168.92
   2.56
redo writer latching time              4            0          .04
      0
redo writes                        46019         1.18       500.21
   7.59
rollback changes - undo rec          160            0         1.74
    .03
rollbacks only - consistent         2259          .06        24.55
    .37
rows fetched via callback         940780        24.19     10225.87
 155.14
session logical reads          141591204      3640.16   1539034.83
23349.47
session pga memory              20416432       524.88    221917.74
3366.83
session pga memory max         286764412       7372.4   3117004.48
47289.65
session uga memory              21462488       551.78    233287.91
3539.33
session uga memory max        1294996480     33292.97   14076048.7
213554.83
shared hash latch upgrades       7137150       183.49     77577.72
1176.97
shared hash latch upgrades          3481          .09        37.84
    .57
sorts (memory)                    195568         5.03      2125.74
  32.25
sorts (rows)                   258684051      6650.49   2811783.16
42658.98
switch current to new buffe        43044         1.11       467.87
    7.1
table fetch by rowid            10279986       264.29    111738.98
1695.25
table fetch continued row         869019        22.34      9445.86
 143.31
table scan blocks gotten       108210988      2781.99   1176206.39
17844.82
table scan rows gotten       13968676005    359119.62 151833434.84
2303541.56
table scans (long tables)           1972          .05        21.43
    .33
table scans (short tables)       3959447       101.79     43037.47
 652.94
transaction rollbacks                 64            0           .7
    .01
user calls                       1218898        31.34     13248.89
 201.01
user commits                       38897            1       422.79
   6.41
user rollbacks                       771          .02         8.38
    .13
workarea executions - onepa            5            0          .05
      0
workarea executions - optim       222655         5.72      2420.16
  36.72
workarea memory allocated             16            0          .17
      0
write clones created in for            5            0          .05
      0

124 rows selected.

SQL>
SQL> column "Event Name" format a32 trunc;
SQL> set numwidth 13;
SQL> Rem System wide wait events for non-background processes (PMON,
SQL> Rem SMON, etc).  Times are in hundreths of seconds.  Each one of
SQL> Rem these is a context switch which costs CPU time.  By looking at
SQL> Rem the Total Time you can often determine what is the bottleneck
SQL> Rem that processes are waiting for.  This shows the total time
spent
SQL> Rem waiting for a specific event and the average time per wait on
SQL> Rem that event.
SQL> select  n1.event "Event Name",
  2  	     n1.event_count "Count",
  3  	     n1.time_waited "Total Time",
  4  	     round(n1.time_waited/n1.event_count, 2) "Avg Time"
  5  	from stats$event n1
  6  	where n1.event_count > 0
  7  	order by n1.time_waited desc;

Event Name                               Count    Total Time      Avg
Time
-------------------------------- ------------- -------------
-------------
SQL*Net message from client             854546      24313588
28.45
rdbms ipc message                         1407        626548
445.31
dispatcher timer                           101        604102
5981.21
virtual circuit status                     202        602897
2984.64
wakeup time manager                        200        584029
2920.15
db file scattered read                  207340         73940
.36
db file sequential read                 192637         67773
.35
latch free                                8319         22907
2.75
log file sync                            39010         18020
.46
enqueue                                     54         10973
203.2
buffer busy waits                         2167          1531
.71
direct path write                         2176           688
.32
control file sequential read               890           449
.5
direct path read                          1765           272
.15
SQL*Net message to client               854558           261
 0
log file sequential read                    51           153
 3
async disk IO                              413           146
.35
SQL*Net break/reset to client              434            23
.05
SQL*Net more data to client               1121            11
.01
control file single write                    7            11
1.57
control file parallel write                 39            10
.26
log file switch completion                   3            10
3.33
switch logfile command                       1             7
 7
rdbms ipc reply                              1             4
 4
SQL*Net more data from client              218             3
.01
db file single write                         1             0
 0

26 rows selected.

SQL>
SQL>
SQL> Rem System wide wait events for background processes (PMON, SMON,
etc)
SQL> select n1.event "Event Name",
  2  	     n1.event_count "Count",
  3  	     n1.time_waited "Total Time",
  4  	     round(n1.time_waited/n1.event_count, 2) "Avg Time"
  5  	from stats$bck_event n1
  6  	where n1.event_count > 0
  7  	order by n1.time_waited desc;

Event Name                               Count    Total Time      Avg
Time
-------------------------------- ------------- -------------
-------------
rdbms ipc message                        54713       3388711
61.94
pmon timer                                2110        606005
287.21
smon timer                                  24        537057
22377.38
control file sequential read               691          2715
3.93
control file parallel write               2044          2113
1.03
db file parallel write                    1302          1952
1.5
log file parallel write                  46020           593
.01
enqueue                                      2           519
259.5
log file sequential read                   105           308
2.93
db file sequential read                     42            79
1.88
db file scattered read                      70            25
.36
direct path read                            50            10
.2
LGWR wait for redo copy                    143             4
.03
direct path write                           50             3
.06
latch free                                   9             1
.11
async disk IO                               49             0
 0
log file single write                        4             0
 0

17 rows selected.

SQL>
SQL>
SQL> column latch_name format a18 trunc;
SQL> set numwidth 11;
SQL> Rem Latch statistics. Latch contention will show up as a large
value for
SQL> Rem the 'latch free' event in the wait events above.
SQL> Rem Sleeps should be low.	The hit_ratio should be high.
SQL> select name latch_name, gets, misses,
  2  	 round((gets-misses)/decode(gets,0,1,gets),3)
  3  	   hit_ratio,
  4  	 sleeps,
  5  	 round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
  6  	from stats$latches
  7  	 where gets != 0
  8  	 order by name;

LATCH_NAME                GETS      MISSES   HIT_RATIO      SLEEPS
SLEEPS/MISS
------------------ ----------- ----------- ----------- -----------
-----------
Consistent RBA           46021           0           1           0
     0
FAL request queue            4           0           1           0
     0
FIB s.o chain latc          88           0           1           0
     0
FOB s.o list latch        1914           0           1           0
     0
JOX SGA heap latch        1899           0           1           0
     0
SQL memory manager      303454           0           1           0
     0
X$KSFQP                     14           0           1           0
     0
active checkpoint         3394           0           1           0
     0
alert log latch             11           0           1           0
     0
archive control            223           0           1           0
     0
archive process la         131           0           1           0
     0
cache buffer handl      705164           1           1           0
     0
cache buffers chai   283127380     2948902         .99        6665
  .002
cache buffers lru        67973          38        .999           3
  .079
channel handle poo        2654           0           1           0
     0
channel operations        8060           0           1           0
     0
checkpoint queue l      383668          89           1           0
     0
child cursor hash       742049         130           1           1
  .008
dml lock allocatio      352214          42           1           0
     0
dummy allocation          2644           0           1           0
     0
enqueue hash chain      685036          50           1           2
   .04
enqueues                402835         208        .999           3
  .014
event group latch         1328           0           1           0
     0
hash table column          391           0           1           0
     0
hash table modific          19           0           1           0
     0
job_queue_processe         101           0           1           0
     0
kmcptab latch                1           0           1           0
     0
ktm global data             24           0           1           0
     0
kwqit: protect wak         200           0           1           0
     0
lgwr LWN SCN             46289           1           1           0
     0
library cache         13685648       98955        .993         424
  .004
library cache load        4402           0           1           0
     0
library cache pin      7610571       31128        .996          27
  .001
library cache pin      2956410         222           1           1
  .005
list of block allo        2099           0           1           0
     0
loader state objec          24           0           1           0
     0
longop free list p         288           0           1           0
     0
message pool opera          10           0           1           0
     0
messages                163801          40           1           1
  .025
mostly latch-free        46338          29        .999           0
     0
multiblock read ob      766150         104           1           6
  .058
ncodef allocation          101           0           1           0
     0
object stats modif          18           0           1           0
     0
post/wait queue          59049           1           1           0
     0
process allocation        1328           0           1           0
     0
process group crea        2644           0           1           0
     0
redo allocation         750438         118           1           6
  .051
redo writing            145485           3           1           0
     0
row cache enqueue     41968586       29573        .999           5
     0
row cache objects     41978562       41754        .999          10
     0
sequence cache            4112           0           1           0
     0
session allocation      360837         338        .999           2
  .006
session idle bit       2606925         584           1           2
  .003
session switching          101           0           1           0
     0
session timer             2109           0           1           0
     0
shared java pool            48           0           1           0
     0
shared pool            8843899       23408        .997        1169
   .05
simulator hash lat    12285392          16           1           0
     0
simulator lru latc       22969           2           1           1
    .5
sort extent pool           465           0           1           0
     0
spilled msgs queue         200           0           1           0
     0
transaction alloca        7853           0           1           0
     0
transaction branch         101           0           1           0
     0
undo global data        165787           7           1           0
     0
user lock                 5600           0           1           0
     0

65 rows selected.

SQL>
SQL> set numwidth 16
SQL> Rem Statistics on no_wait gets of latches.  A no_wait get does not
SQL> Rem wait for the latch to become free, it immediately times out.
SQL> select name latch_name,
  2  	 immed_gets nowait_gets,
  3  	 immed_miss nowait_misses,
  4  	 round((immed_gets/(immed_gets+immed_miss)), 3)
  5  	   nowait_hit_ratio
  6  	from stats$latches
  7  	 where immed_gets + immed_miss != 0
  8  	 order by name;

LATCH_NAME              NOWAIT_GETS    NOWAIT_MISSES NOWAIT_HIT_RATIO

------------------ ---------------- ---------------- ----------------

SQL memory manager             2016                0                1

cache buffers chai          3612089              193                1

cache buffers lru           5188315             1655                1

checkpoint queue l            20488                0                1

hash table column           8451898              601                1

kmcpvec latch                     1                0                1

library cache                337800             2057             .994

longop free list p              288                0                1

post/wait queue               39009                5                1

process allocation             1328                0                1

redo copy                    658203              158                1

row cache objects               490                0                1

simulator lru latc           212228             1623             .992


13 rows selected.

SQL>
SQL> Rem Buffer busy wait statistics. If the value for 'buffer busy wait' in
SQL> Rem the wait event statistics is high, then this table will identify
SQL> Rem which class of blocks is having high contention. If there are high
SQL> Rem 'undo header' waits then add more rollback segments. If there are
SQL> Rem high 'segment header' waits then adding freelists might help. Check
SQL> Rem v$session_wait to get the addresses of the actual blocks having
SQL> Rem contention.
SQL> select * from stats$waitstat
  2 where count != 0
  3 order by count desc;

CLASS                         COUNT             TIME

------------------ ---------------- ----------------

data block                     2159             1543

undo block                        5                0

undo header                       2                0


SQL>
SQL>
SQL> set lines 159;

SQL> set numwidth 19;
SQL> Rem Waits_for_trans_tbl high implies you should add rollback segments.
SQL> select * from stats$roll;
       UNDO_SEGMENT      TRANS_TBL_GETS     TRANS_TBL_WAITS
UNDO_BYTES_WRITTEN  SEGMENT_SIZE_BYTES               XACTS
SHRINKS               WRAPS
------------------- ------------------- -------------------
------------------- ------------------- -------------------
------------------- -------------------
                  0                  27                   0
      0              385024                   0                   0
              0
                  1               11287                   0
5419560            18997248                   0                   0
              6
                  2               10461                   0
5045128            16900096                   0                   0
              5
                  3               11476                   0
8362400            20045824                   1                   0
              9
                  4               10510                   0
7221078            13754368                  -1                   0
              9
                  5               10292                   0
5467284            15851520                   0                   0
              5
                  6               11158                   0
5700914            11657216                   0                   0
              8
                  7               10279                   0
4859478            13754368                   0                   0
              7
                  8               10977                   0
5510304            12771328                   0                   0
              6
                  9               10079                   0
5017166            11657216                   0                   0
              8
                 10               10199                   0
5309168            11657216                   0                   0
              6

11 rows selected.

SQL> set lines 79;
SQL>
SQL> column name  format a39 trunc;
SQL> column value format a39 trunc;
SQL> Rem The init.ora parameters currently in effect:
SQL> select name, value from v$parameter where isdefault = 'FALSE'
  2 order by name;
NAME                                    VALUE

---------------------------------------
---------------------------------------
aq_tm_processes                         1

background_dump_dest                    D:\oracle\admin\fdd\bdump

compatible                              9.2.0.0.0

control_file_record_keep_time           29

control_files
D:\oracle\oradata\fdd\CONTROL01.CTL, D:

core_dump_dest                          D:\oracle\admin\fdd\cdump

db_block_size                           8192

db_cache_size                           360710144

db_domain

db_file_multiblock_read_count 16

db_name                                 fdd

dispatchers                             (PROTOCOL=TCP) (SERVICE=fddXDB)

fast_start_mttr_target                  0

hash_join_enabled                       TRUE

instance_name                           fdd

java_pool_size                          58720256

job_queue_processes                     10

large_pool_size                         16777216

log_archive_dest
d:\oracle\oradata\archivelog\fdd

log_archive_format                      %t_%s_fdd_prod.LOG

log_archive_start                       TRUE

open_cursors                            350

pga_aggregate_target                    1246756864

processes                               300

query_rewrite_enabled                   FALSE

remote_login_passwordfile               EXCLUSIVE

sga_max_size                            839984456

shared_pool_size                        369098752

sort_area_size                          25165824

star_transformation_enabled             FALSE

timed_statistics                        TRUE

undo_management                         AUTO

undo_retention                          10800

undo_tablespace                         UNDOTBS1

user_dump_dest                          D:\oracle\admin\fdd\udump


35 rows selected.

SQL>
SQL> column name format a15 trunc;
SQL> column scan_reqs heading 'SCAN_REQ';
SQL> column scan_miss heading 'SCAN_MIS';
SQL> column cur_usage heading 'CUR_USAG';
SQL> set numwidth 8;
SQL> Rem get_miss and scan_miss should be very low compared to the
requests.
SQL> Rem cur_usage is the number of entries in the cache that are being used.
SQL> select * from stats$dc
  2 where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0;

NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG
--------------- -------- -------- -------- -------- -------- --------



dc_tablespaces 2856350 1 0 0 0 24

    24
dc_segments 2140797 91 0 0 4 714

   714
dc_rollback_seg 1005 0 0 0 0 22

    22

dc_tablespace_q        4        2        0        0        4        3
     3
dc_users         2978656        7        0        0        4      133
   133
dc_user_grants      6288        7        0        0        0      109
   109
dc_objects        283631      192        0        0        0      670
   670
dc_usernames      260441        9        0        0        2      122
   122
dc_object_ids   11376730       24        0        0        0      696
   696
dc_sequences          67        1        0        0       67        2
     2
dc_profiles         1325        0        0        0        0        1
     1
dc_histogram_de  1088160      218        0        0        0     1119
  1119

12 rows selected.

SQL>
SQL>
SQL> set lines 157;
SQL> column table_space format a80 trunc;
SQL> set numwidth 10;
SQL> Rem Sum IO operations over tablespaces.
SQL> select
  2    table_space||'						      '
  3  	  table_space,
  4    sum(phys_reads) reads,  sum(phys_blks_rd) blks_read,
  5    sum(phys_rd_time) read_time,  sum(phys_writes) writes,
  6 sum(phys_blks_wr) blks_wrt, sum(phys_wrt_tim) write_time,   7 sum(megabytes_size) megabytes
  8 from stats$files
  9 group by table_space
 10 order by table_space;

TABLE_SPACE
              READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES


24 rows selected.

SQL>
SQL>
SQL> set lines 196;
SQL> column table_space format a48 trunc;
SQL> column file_name	format a48 trunc;
SQL> set numwidth 10;
SQL> Rem I/O should be spread evenly accross drives. A big difference
between
SQL> Rem phys_reads and phys_blks_rd implies table scans are going on. SQL> select table_space, file_name,
  2 phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time,
  3 phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time,
  4  	    megabytes_size megabytes,
  5  	    round(decode(phys_blks_rd,0,0,phys_rd_time/phys_blks_rd),2)
avg_rt,
  6  	    round(decode(phys_reads,0,0,phys_blks_rd/phys_reads),2)
"blocks/rd"
  7 from stats$files order by table_space, file_name;
TABLE_SPACE                                      FILE_NAME
                               READS  BLKS_READ  READ_TIME     WRITES
BLKS_WRT WRITE_TIME  MEGABYTES     AVG_RT  blocks/rd
------------------------------------------------
------------------------------------------------ ---------- ----------
---------- ---------- ---------- ---------- ---------- ----------

ADT
D:\ORACLE\ORADATA\FDD\AUDIT01.ORA                     22155     148653
    15043       4767       4767     127526       3681         .1
6.71
BLOB1
D:\ORACLE\ORADATA\FDD\BLOB1.DBF                           2          2
        3          2          2          2        105        1.5
  1
CWMLITE
D:\ORACLE\ORADATA\FDD\CWMLITE01.DBF                       2          2
        5          2          2          0         21        2.5
  1
DOC
D:\ORACLE\ORADATA\FDD\DOC01.DBF                       23005     178369
    15167        449        449       1993        524        .09
7.75
DOCIDX
D:\ORACLE\ORADATA\FDD\DOCIDX01.DBF                     5979       8086
     8165       2909       2909      16714        524       1.01
1.35
DOCTREE
D:\ORACLE\ORADATA\FDD\DOCTREE01.DBF                     340        340
      923         60         60        399        315       2.71
  1
DOCTREEIDX
D:\ORACLE\ORADATA\FDD\DOCTREEIDX01.DBF                  427        427
     1358        151        151        956        874       3.18
  1
DRSYS
D:\ORACLE\ORADATA\FDD\DRSYS01.DBF                         2          2
        5          2          2          2        105        2.5
  1
EXAMPLE
D:\ORACLE\ORADATA\FDD\EXAMPLE01.DBF                       2          2
        5          2          2          2        262        2.5
  1
EXTERNAL
D:\ORACLE\ORADATA\FDD\EXTERNAL01.DBF                      2          2
        6          2          2          2        105          3
  1
FCB
D:\ORACLE\ORADATA\FDD\FCB01.DBF                       57886     108493
    24191        201        201        556       2097        .22
1.87
FCBIDX
D:\ORACLE\ORADATA\FDD\FCBIDX01.DBF                     1307       1355
     3096       1507       1507      23748       1311       2.28
1.04
FDT
D:\ORACLE\ORADATA\FDD\FDT01.DBF                         383        599
     1062         21         21        119       1049       1.77
1.56
FDTIDX
D:\ORACLE\ORADATA\FDD\FDTIDX01.DBF                       72        147
      267          8          8          2       3146       1.82
2.04
INDX
D:\ORACLE\ORADATA\FDD\INDX01.DBF                          2          2
        7          2          2          2        105        3.5
  1
ODM
D:\ORACLE\ORADATA\FDD\ODM01.DBF                           2          2
        3          2          2          2         21        1.5
  1
PAGE
D:\ORACLE\ORADATA\FDD\PAGE01.DBF                        117        119
      133         25         25        236       4823       1.12
1.02
PAGE
D:\ORACLE\ORADATA\FDD\PAGE02.DBF                        675        675
     1780        251        251       3745       3146       2.64
  1
PAGEIDX
D:\ORACLE\ORADATA\FDD\PAGEIDX01.DBF                    1576       1576
     6012        954        954      15720       2592       3.81
  1
RBS
D:\ORACLE\ORADATA\FDD\RBS01.DBF                           2          2
        3          2          2          2        315        1.5
  1
SYSTEM
D:\ORACLE\ORADATA\FDD\SYSTEM01.DBF                      486        999
      477        253        253        577        839        .48
2.06
TOOLS
D:\ORACLE\ORADATA\FDD\TOOLS01.DBF                         2          2
       11          2          2          2         52        5.5
  1
UNDOTBS1
D:\ORACLE\ORADATA\FDD\UNDOTBS01.DBF                       3          3
        3       7679       7679     176828       3880          1
  1
USERS
D:\ORACLE\ORADATA\FDD\USERS01.DBF                    285661    1395783
    64334        469        469       3075        700        .05
4.89
XDB
D:\ORACLE\ORADATA\FDD\XDB01.DBF                           2          2
Received on Tue Dec 12 2006 - 03:44:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US