SVRMGR>
SVRMGR> set charwidth 12
Charwidth 12
SVRMGR> set numwidth 10
Numwidth 10
SVRMGR> Rem Select Library cache statistics. The pin hit rate shoule be high.
SVRMGR> 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 2378 1 2378 1 0 0
CLUSTER 0 1 0 1 0 0
INDEX 0 1 0 1 0 0
OBJECT 0 1 0 1 0 0
PIPE 0 1 0 1 0 0
SQL AREA 58691 .899 218305 .943 507 5
TABLE/PROCED 16284 .993 67372 .997 45 0
TRIGGER 2215 .994 2215 .987 2 0
8 rows selected.
SVRMGR>
SVRMGR> set charwidth 27;
Charwidth 27
SVRMGR> set numwidth 12;
Numwidth 12
SVRMGR> Rem The total is the total value of the statistic between the time
SVRMGR> Rem bstat was run and the time estat was run. Note that the estat
SVRMGR> Rem script logs on as "internal" so the per_logon statistics will
SVRMGR> Rem always be based on at least one logon.
SVRMGR> select n1.name "Statistic",
2> n1.change "Total",
3> round(n1.change/trans.change,2) "Per Transaction",
4> round(n1.change/logs.change,2) "Per Logon"
5> from stats$stats n1, stats$stats trans, stats$stats logs
6> where trans.name='user commits'
7> and logs.name='logons cumulative'
8> and n1.change != 0
9> order by n1.name;
Statistic Total Per Transact Per Logon
--------------------------- ------------ ------------ ------------
CPU used by this session 87758 63.5 39.82
CPU used when call started 87726 63.48 39.8
CR blocks created 967 .7 .44
DBWR buffers scanned 270062 195.41 122.53
DBWR checkpoints 20 .01 .01
DBWR free buffers found 245465 177.62 111.37
DBWR lru scans 132 .1 .06
DBWR make free requests 120 .09 .05
DBWR summed scan depth 270062 195.41 122.53
DBWR timeouts 5478 3.96 2.49
OS Bytes from Logical reads 69912385 50587.83 31720.68
OS Disk Reads 1175197683 850360.12 533211.29
OS Involuntary context swit 216020 156.31 98.01
OS Logical Reads 109175 79 49.53
OS Maximum resident set siz 43604085 31551.44 19784.07
OS Page faults 2085878 1509.32 946.41
OS Resident Set Size decrea 578844 418.85 262.63
OS System calls 108 .08 .05
OS System time used 2952210 2136.19 1339.48
OS User time used 19319494 13979.37 8765.65
OS Zero-Fill Pages 142783523 103316.59 64783.81
SQL*Net roundtrips to/from 233580 169.02 105.98
background checkpoints comp 1 0 0
background checkpoints star 1 0 0
background timeouts 18022 13.04 8.18
bytes received via SQL*Net 12945504 9367.22 5873.64
bytes sent via SQL*Net to c 30113585 21789.86 13663.15
calls to get snapshot scn: 92945 67.25 42.17
calls to kcmgas 1638 1.19 .74
calls to kcmgcs 881 .64 .4
calls to kcmgrs 108297 78.36 49.14
change write time 102 .07 .05
cleanouts and rollbacks - c 470 .34 .21
cleanouts only - consistent 297 .21 .13
cluster key scan block gets 526997 381.33 239.11
cluster key scans 347165 251.2 157.52
commit cleanout failures: b 4 0 0
commit cleanout failures: c 140 .1 .06
commit cleanout number succ 10233 7.4 4.64
consistent changes 1354 .98 .61
consistent gets 5897656 4267.48 2675.89
cursor authentications 33737 24.41 15.31
data blocks consistent read 1354 .98 .61
db block changes 50611 36.62 22.96
db block gets 90469 65.46 41.05
deferred (CURRENT) block cl 2762 2 1.25
enqueue conversions 428 .31 .19
enqueue releases 8870 6.42 4.02
enqueue requests 8859 6.41 4.02
enqueue timeouts 1 0 0
enqueue waits 2 0 0
execute count 101464 73.42 46.04
free buffer inspected 47 .03 .02
free buffer requested 194779 140.94 88.38
immediate (CR) block cleano 767 .55 .35
immediate (CURRENT) block c 3626 2.62 1.65
logons cumulative 2204 1.59 1
logons current -5 0 0
messages received 2040 1.48 .93
messages sent 2040 1.48 .93
no work - consistent read g 5381966 3894.33 2441.91
opened cursors cumulative 41210 29.82 18.7
opened cursors current -97 -.07 -.04
parse count 62815 45.45 28.5
parse time cpu 5537 4.01 2.51
parse time elapsed 6729 4.87 3.05
physical reads 193608 140.09 87.84
physical writes 13831 10.01 6.28
process last non-idle time 96616058455 69910317.26 43836687.14
recursive calls 219592 158.89 99.63
recursive cpu usage 30443 22.03 13.81
redo blocks written 16539 11.97 7.5
redo buffer allocation retr 1 0 0
redo entries 27793 20.11 12.61
redo log space requests 1 0 0
redo log space wait time 70 .05 .03
redo size 7768878 5621.47 3524.9
redo small copies 7088 5.13 3.22
redo synch time 2337 1.69 1.06
redo synch writes 1445 1.05 .66
redo wastage 417727 302.26 189.53
redo write time 3870 2.8 1.76
redo writer latching time 3 0 0
redo writes 2329 1.69 1.06
rollback changes - undo rec 267 .19 .12
rollbacks only - consistent 500 .36 .23
session connect time 96616058455 69910317.26 43836687.14
session logical reads 5985828 4331.28 2715.89
session pga memory 1951988776 1412437.61 885657.34
session pga memory max 1977371420 1430804.21 897173.97
session uga memory 6045016 4374.11 2742.75
session uga memory max 138930448 100528.54 63035.59
sorts (disk) 4 0 0
sorts (memory) 22606 16.36 10.26
sorts (rows) 1779957 1287.96 807.6
table fetch by rowid 3474096 2513.82 1576.27
table fetch continued row 48 .03 .02
table scan blocks gotten 726148 525.43 329.47
table scan rows gotten 42286764 30598.24 19186.37
table scans (long tables) 165 .12 .07
table scans (short tables) 20948 15.16 9.5
total number commit cleanou 10398 7.52 4.72
transaction rollbacks 18 .01 .01
user calls 210901 152.61 95.69
user commits 1382 1 .63
user rollbacks 358 .26 .16
write requests 4100 2.97 1.86
107 rows selected.
SVRMGR>
SVRMGR> set charwidth 27;
Charwidth 27
SVRMGR> set numwidth 12;
Numwidth 12
SVRMGR> Rem System wide wait events.
SVRMGR> select n1.event "Event Name",
2> n1.event_count "Count",
3> n1.time_waited "Total Time",
4> (n1.time_waited/n1.event_count) "Average Time"
5> from stats$event n1
6> where n1.event_count > 0
7> order by n1.time_waited desc;
Event Name Count Total Time Average Time
--------------------------- ------------ ------------ ------------
SQL*Net message from client 238333 28856237 121.07528962
rdbms ipc message 20175 16170398 801.50671623
Null event 65088 11624889 178.60264565
pmon timer 5547 1663465 299.88552371
smon timer 56 1646292 29398.071429
db file sequential read 87906 150006 1.7064364207
db file scattered read 8027 9553 1.1901083842
db file parallel write 4100 3928 .95804878049
log file parallel write 2328 3868 1.6615120275
log file sync 1843 3062 1.6614215952
enqueue 3 599 199.66666667
control file sequential rea 5347 355 .06639236955
control file parallel write 100 336 3.36
SQL*Net break/reset to clie 97 107 1.1030927835
SQL*Net message to client 238328 104 .0004363734
db file single write 64 100 1.5625
log file switch completion 1 70 70
rdbms ipc reply 20 36 1.8
write complete waits 21 33 1.5714285714
latch free 77 19 .24675324675
buffer busy waits 3 11 3.6666666667
SQL*Net more data to client 967 9 .00930713547
log file single write 2 6 3
log file sequential read 1 1 1
24 rows selected.
SVRMGR>
SVRMGR> set numwidth 27
Numwidth 27
SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger than
SVRMGR> Rem the value of the db_block_write_batch init.ora parameter, then consider
SVRMGR> Rem increasing the value of db_block_write_batch and check for disks that
SVRMGR> Rem are doing many more IOs than other disks.
SVRMGR> select queue.change/writes.change "Average Write Queue Length"
2> from stats$stats queue, stats$stats writes
3> where queue.name = 'summed dirty queue length'
4> and writes.name = 'write requests';
Average Write Queue Length
---------------------------
0
1 row selected.
SVRMGR>
SVRMGR> set charwidth 48;
Charwidth 48
SVRMGR> set numwidth 12;
Numwidth 12
SVRMGR> Rem I/O should be spread evenly accross drives. A big difference between
SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on.
SVRMGR> select * from
2> stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME PHYS_READS PHYS_BLKS_RD PHYS_RD_TIME PHYS_WRITES PHYS_BLKS_WR PHYS_WRT_TIM
------------------------------ ------------------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------
RBS001 /v001a/oracle/dojv/rbs_01.dbf 2 2 5 495 495 1103
RBS001 /v001a/oracle/dojv/rbs_011.dbf 0 0 0 0 0 0
RBS002 /v002a/oracle/dojv/rbs_02.dbf 5 5 14 459 459 1105
RBS002 /v002a/oracle/dojv/rbs_021.dbf 0 0 0 0 0 0
RBS003 /v003a/oracle/dojv/rbs_03.dbf 0 0 0 166 166 378
RBS003 /v003a/oracle/dojv/rbs_031.dbf 4 4 12 277 277 656
RBS004 /v004a/oracle/dojv/rbs_04.dbf 1 1 3 537 537 1242
RBS004 /v004a/oracle/dojv/rbs_041.dbf 0 0 0 0 0 0
RBS005 /v005a/oracle/dojv/rbs_05.dbf 3 3 8 21 21 51
RBS005 /v005a/oracle/dojv/rbs_051.dbf 0 0 0 0 0 0
RBS006 /v006a/oracle/dojv/rbs_06.dbf 5 5 16 491 491 1110
RBS006 /v006a/oracle/dojv/rbs_061.dbf 0 0 0 0 0 0
SPPI_DATA_01 /v005a/oracle/dojv/vcin_s01d_0.dbf 10 10 32 15 15 34
SPPI_DATA_02 /v006a/oracle/dojv/vcin_s02d_0.dbf 14 14 39 0 0 0
SPPI_DATA_03 /v006a/oracle/dojv/vcin_s03d_0.dbf 4 4 11 8 8 18
SPPI_DATA_04 /v001a/oracle/dojv/vcin_s04d_0.dbf 122 122 270 0 0 0
SPPI_DATA_05 /v005a/oracle/dojv/vcin_s05d_0.dbf 0 0 0 0 0 0
SPPI_INDX_01 /v003a/oracle/dojv/vcin_s01i_0.dbf 6 6 17 0 0 0
SPPI_INDX_01 /v003a/oracle/dojv/vcin_s01i_1.dbf 0 0 0 0 0 0
SPPI_INDX_02 /v001a/oracle/dojv/vcin_s02i_0.dbf 35 35 96 0 0 0
SPPI_INDX_03 /v002a/oracle/dojv/vcin_s03i_0.dbf 4 4 11 5 5 12
SPPI_INDX_04 /v003a/oracle/dojv/vcin_s04i_0.dbf 66 66 197 0 0 0
SPPI_INDX_05 /v003a/oracle/dojv/vcin_s05i_0.dbf 0 0 0 0 0 0
SYSTEM /v001a/oracle/dojv/sys_01.dbf 1241 2841 2175 223 223 537
TEMP /v002a/oracle/dojv/tmp_01.dbf 69 464 123 272 1039 444
TEMP /v002a/oracle/dojv/tmp_02.dbf 0 0 0 0 0 0
USERS /v003a/oracle/dojv/usr_01.dbf 1982 28587 3144 84 114 232
VCIN_ADDR /v004a/oracle/dojv/vcin_vadd_0.dbf 3524 3524 9044 259 259 597
VCIN_ADDR_HIST /v001a/oracle/dojv/vcin_vadh_0.dbf 5 5 16 179 179 411
VCIN_ADDR_INDX /v005a/oracle/dojv/vcin_vadi_0.dbf 1416 1416 3427 1019 1019 2543
VCIN_COMM /v001a/oracle/dojv/vcin_vcmd_0.dbf 11219 30397 18349 1084 1084 2450
VCIN_COMM_HIST /v004a/oracle/dojv/vcin_vcmh_0.dbf 3771 3771 9152 506 506 1160
VCIN_COMM_INDX /v002a/oracle/dojv/vcin_vcmi_0.dbf 4411 4411 11508 1740 1740 4553
VCIN_CONT /v004a/oracle/dojv/vcin_vcod_0.dbf 2 2 6 2 2 4
VCIN_CONT_INDX /v004a/oracle/dojv/vcin_vcoi_0.dbf 361 361 1047 13 13 40
VCIN_IMAGE_DATA /v001a/oracle/dojv/vcin_vimd_0.dbf 396 5360 410 3 3 6
VCIN_IMAGE_INDEX /v002a/oracle/dojv/vcin_vimi_0.dbf 217 217 687 0 0 0
VCIN_JOUR /v002a/oracle/dojv/vcin_vjrd_0.dbf 1058 16683 377 373 373 911
VCIN_JOUR_INDX /v003a/oracle/dojv/vcin_vjri_0.dbf 626 626 1432 1611 1611 4165
VCIN_LOOK /v001a/oracle/dojv/vcin_vlkd_0.dbf 561 1067 1209 0 0 0
VCIN_LOOK_INDX /v002a/oracle/dojv/vcin_vlki_0.dbf 279 279 663 0 0 0
VCIN_MO /v003a/oracle/dojv/vcin_vmod_0.dbf 899 7678 2320 171 171 421
VCIN_MO_HIST /v001a/oracle/dojv/vcin_vmoh_0.dbf 70 70 193 57 57 146
VCIN_MO_INDX /v002a/oracle/dojv/vcin_vmoi_0.dbf 128 128 256 85 85 243
VCIN_NAME /v004a/oracle/dojv/vcin_vnmd_0.dbf 37053 37053 45007 35 35 96
VCIN_NAME_HIST /v004a/oracle/dojv/vcin_vnmh_0.dbf 9 9 26 25 25 65
VCIN_NAME_INDX /v004a/oracle/dojv/vcin_vnmi_0.dbf 4867 4867 8868 133 133 340
VCIN_ODOR /v005a/oracle/dojv/vcin_vodd_0.dbf 337 1489 666 0 0 0
VCIN_ODOR_HIST /v006a/oracle/dojv/vcin_vodh_0.dbf 0 0 0 0 0 0
VCIN_ODOR_INDX /v004a/oracle/dojv/vcin_vodi_0.dbf 39 39 94 29 29 70
VCIN_PERS /v005a/oracle/dojv/vcin_vpsd_0.dbf 4054 4072 3726 493 493 1319
VCIN_PERS_HIST /v005a/oracle/dojv/vcin_vpsh_0.dbf 1936 1936 5217 240 240 649
VCIN_PERS_INDX /v006a/oracle/dojv/vcin_vpsi_0.dbf 7699 7699 15943 1473 1473 4301
VCIN_SEAR /v005a/oracle/dojv/vcin_vsrd_0.dbf 13 13 39 288 288 670
VCIN_SEAR_INDX /v004a/oracle/dojv/vcin_vsri_0.dbf 0 0 0 0 0 0
VCIN_STRP /v005a/oracle/dojv/vcin_vstd_0.dbf 8 59 23 521 521 3229
VCIN_SUPE /v002a/oracle/dojv/vcin_vspd_0.dbf 487 487 1494 9 9 24
VCIN_SUPE_HIST /v006a/oracle/dojv/vcin_vsph_0.dbf 10 10 25 8 8 29
VCIN_SUPE_INDX /v003a/oracle/dojv/vcin_vspi_0.dbf 348 348 933 21 21 48
VCIN_VEHI /v005a/oracle/dojv/vcin_vvhd_0.dbf 4687 25543 7669 134 134 351
VCIN_VEHI_HIST /v001a/oracle/dojv/vcin_vvhh_0.dbf 14 14 42 102 102 253
VCIN_VEHI_INDX /v003a/oracle/dojv/vcin_vvhi_0.dbf 1797 1797 4011 431 431 1044
VCIN_VSTR /v003a/oracle/dojv/vcin_vvsd_0.dbf 4 4 7 4 4 11
VCIN_VSTR /v003a/oracle/dojv/vcin_vvsd_1.dbf 0 0 0 0 0 0
64 rows selected.
SVRMGR>
SVRMGR> set charwidth 80
Charwidth 80
SVRMGR> Rem sum over tablespaces
SVRMGR> select
2> table_space||' '
3> table_space,
4> sum(phys_reads) phys_reads, sum(phys_blks_rd) phys_blks_rd,
5> sum(phys_rd_time) phys_rd_time, sum(phys_writes) phys_writes,
6> sum(phys_blks_wr) phys_blks_wr, sum(phys_wrt_tim) phys_wrt_tim
7> from stats$files
8> group by table_space
9> order by table_space;
TABLE_SPACE PHYS_READS PHYS_BLKS_RD PHYS_RD_TIME PHYS_WRITES PHYS_BLKS_WR PHYS_WRT_TIM
------------------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------
RBS001 2 2 5 495 495 1103
RBS002 5 5 14 459 459 1105
RBS003 4 4 12 443 443 1034
RBS004 1 1 3 537 537 1242
RBS005 3 3 8 21 21 51
RBS006 5 5 16 491 491 1110
SPPI_DATA_01 10 10 32 15 15 34
SPPI_DATA_02 14 14 39 0 0 0
SPPI_DATA_03 4 4 11 8 8 18
SPPI_DATA_04 122 122 270 0 0 0
SPPI_DATA_05 0 0 0 0 0 0
SPPI_INDX_01 6 6 17 0 0 0
SPPI_INDX_02 35 35 96 0 0 0
SPPI_INDX_03 4 4 11 5 5 12
SPPI_INDX_04 66 66 197 0 0 0
SPPI_INDX_05 0 0 0 0 0 0
SYSTEM 1241 2841 2175 223 223 537
TEMP 69 464 123 272 1039 444
USERS 1982 28587 3144 84 114 232
VCIN_ADDR 3524 3524 9044 259 259 597
VCIN_ADDR_HIST 5 5 16 179 179 411
VCIN_ADDR_INDX 1416 1416 3427 1019 1019 2543
VCIN_COMM 11219 30397 18349 1084 1084 2450
VCIN_COMM_HIST 3771 3771 9152 506 506 1160
VCIN_COMM_INDX 4411 4411 11508 1740 1740 4553
VCIN_CONT 2 2 6 2 2 4
VCIN_CONT_INDX 361 361 1047 13 13 40
VCIN_IMAGE_DATA 396 5360 410 3 3 6
VCIN_IMAGE_INDEX 217 217 687 0 0 0
VCIN_JOUR 1058 16683 377 373 373 911
VCIN_JOUR_INDX 626 626 1432 1611 1611 4165
VCIN_LOOK 561 1067 1209 0 0 0
VCIN_LOOK_INDX 279 279 663 0 0 0
VCIN_MO 899 7678 2320 171 171 421
VCIN_MO_HIST 70 70 193 57 57 146
VCIN_MO_INDX 128 128 256 85 85 243
VCIN_NAME 37053 37053 45007 35 35 96
VCIN_NAME_HIST 9 9 26 25 25 65
VCIN_NAME_INDX 4867 4867 8868 133 133 340
VCIN_ODOR 337 1489 666 0 0 0
VCIN_ODOR_HIST 0 0 0 0 0 0
VCIN_ODOR_INDX 39 39 94 29 29 70
VCIN_PERS 4054 4072 3726 493 493 1319
VCIN_PERS_HIST 1936 1936 5217 240 240 649
VCIN_PERS_INDX 7699 7699 15943 1473 1473 4301
VCIN_SEAR 13 13 39 288 288 670
VCIN_SEAR_INDX 0 0 0 0 0 0
VCIN_STRP 8 59 23 521 521 3229
VCIN_SUPE 487 487 1494 9 9 24
VCIN_SUPE_HIST 10 10 25 8 8 29
VCIN_SUPE_INDX 348 348 933 21 21 48
VCIN_VEHI 4687 25543 7669 134 134 351
VCIN_VEHI_HIST 14 14 42 102 102 253
VCIN_VEHI_INDX 1797 1797 4011 431 431 1044
VCIN_VSTR 4 4 7 4 4 11
55 rows selected.
SVRMGR>
SVRMGR> set charwidth 18;
Charwidth 18
SVRMGR> set numwidth 11;
Numwidth 11
SVRMGR> Rem Sleeps should be low. The hit_ratio should be high.
SVRMGR> select name latch_name, gets, misses,
2> round(decode(gets-misses,0,1,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
------------------ ----------- ----------- ----------- ----------- -----------
archive control 1 0 1 0 0
cache buffer handl 182 0 1 0 0
cache buffers chai 11862947 44 1 1 .023
cache buffers lru 16293 2 1 0 0
dml lock allocatio 12466 2 1 0 0
enqueue hash chain 18148 1 1 0 0
enqueues 25220 2 1 0 0
ktm global data 58 0 1 0 0
latch wait list 126 0 1 0 0
library cache 1310448 1557 .999 43 .028
library cache load 388 0 1 0 0
list of block allo 3752 0 1 0 0
loader state objec 10 0 1 0 0
messages 44948 23 .999 0 0
modify parameter v 2204 30 .986 6 .2
multiblock read ob 17274 0 1 0 0
process allocation 580 0 1 0 0
redo allocation 45339 6 1 0 0
row cache objects 738614 127 1 3 .024
sequence cache 9123 0 1 0 0
session allocation 17229 79 .995 3 .038
session idle bit 427200 2 1 0 0
session switching 22820 0 1 0 0
shared pool 169321 174 .999 21 .121
sort extent pool 56 0 1 0 0
system commit numb 167512 4 1 0 0
transaction alloca 5297 0 1 0 0
undo global data 8053 0 1 0 0
user lock 1086 0 1 0 0
29 rows selected.
SVRMGR>
SVRMGR> set numwidth 18
Numwidth 18
SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not wait for the
SVRMGR> Rem latch to become free, it immediately times out.
SVRMGR> select name latch_name,
2> immed_gets nowait_gets,
3> immed_miss nowait_misses,
4> round(decode(immed_gets-immed_miss,0,1,immed_gets-immed_miss)/
5> decode(immed_gets,0,1,immed_gets),
6> 3)
7> nowait_hit_ratio
8> from stats$latches
9> where immed_gets != 0
10> order by name;
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------ ------------------ ------------------ ------------------
cache buffers chai 5776612 7 1
cache buffers lru 519087 149 1
library cache 14824 2 1
process allocation 580 0 1
redo copy 30005 0 1
row cache objects 315 0 1
6 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set numwidth 19;
Numwidth 19
SVRMGR> Rem Waits_for_trans_tbl high implies you should add rollback segments.
SVRMGR> select * from stats$roll;
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS WRAPS
------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
0 58 0 0 237568 0 0 0
2 801 0 498128 31940608 0 0 0
3 815 1 434583 14901248 -1 0 1
4 697 0 456943 31940608 -1 0 0
5 871 0 475586 31940608 1 0 0
6 77 0 3163 17031168 0 0 0
7 776 0 452656 14901248 1 0 0
7 rows selected.
SVRMGR>
SVRMGR> set charwidth 39
Charwidth 39
SVRMGR> Rem The init.ora parameters currently in effect:
SVRMGR> select name, value from v$parameter where isdefault = 'FALSE' order by name;
NAME VALUE
--------------------------------------- ---------------------------------------
always_anti_join NESTED_LOOPS
audit_trail NONE
background_dump_dest /oracle/admin/dojv/bdump
cache_size_threshold 1280
compatible 7.3.3
control_files /v002a/oracle/dojv/ctrl_1.ctl, /v003a/o
core_dump_dest /oracle/admin/dojv/cdump
cpu_count 4
db_block_buffers 12800
db_block_lru_latches 2
db_block_size 8192
db_file_multiblock_read_count 16
db_files 1000
db_name dojv
db_writers 7
distributed_transactions 61
dml_locks 500
enqueue_resources 520
gc_freelist_groups 50
gc_releasable_locks 12800
job_queue_processes 5
log_archive_dest /v007a/arclog/dojv/archv
log_archive_format %S.log
log_archive_start TRUE
log_buffer 11534336
log_checkpoint_interval 12000000
log_simultaneous_copies 4
max_dump_file_size 1024
max_enabled_roles 48
mts_max_dispatchers 0
mts_max_servers 0
mts_servers 0
mts_service dojv
open_cursors 400
optimizer_mode CHOOSE
processes 200
remote_login_passwordfile NONE
rollback_segments r01, r02, r03, r04, r05, r06
sequence_cache_entries 30
sequence_cache_hash_buckets 23
sessions 225
shared_pool_size 104857600
sort_area_retained_size 1000000
sort_area_size 1000000
sort_direct_writes AUTO
temporary_table_locks 225
timed_statistics TRUE
transactions 247
transactions_per_rollback_segment 71
user_dump_dest /oracle/admin/dojv/udump
utl_file_dir *
51 rows selected.
SVRMGR>
SVRMGR> set charwidth 15;
Charwidth 15
SVRMGR> set numwidth 8;
Numwidth 8
SVRMGR> Rem get_miss and scan_miss should be very low compared to the requests.
SVRMGR> Rem cur_usage is the number of entries in the cache that are being used.
SVRMGR> 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 296 0 0 0 0 60 55
dc_free_extents 4786 22 15 0 49 502 470
dc_segments 39280 11 0 0 24 1041 1039
dc_rollback_seg 906 0 0 0 0 18 9
dc_used_extents 32 15 0 0 32 435 325
dc_tablespace_q 7 0 0 0 6 24 22
dc_users 15905 1 0 0 0 80 78
dc_user_grants 11062 3 0 0 0 108 74
dc_objects 10864 80 0 0 3 1158 1149
dc_tables 44333 36 0 0 5 633 624
dc_columns 391971 334 22478 38 8 5068 5065
dc_table_grants 30718 159 0 0 0 9549 6128
dc_indexes 36851 0 11146 2 0 656 523
dc_constraint_d 75196 36 8781 4 12 1476 1475
dc_constraint_d 2746 0 212 2 0 121 70
dc_synonyms 391 0 0 0 0 43 42
dc_usernames 10359 1 0 0 0 87 74
dc_object_ids 79487 7 0 0 0 625 621
dc_constraints 18 6 0 0 18 22 14
dc_sequences 262 3 0 0 171 123 122
dc_sequence_gra 17 0 0 0 0 129 13
dc_tablespaces 22 0 0 0 22 24 23
dc_histogram_de 10893 10 0 0 0 819 797
23 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 20
Charwidth 20
SVRMGR> Rem The times that bstat and estat were run.
SVRMGR> select * from stats$dates;
STATS_GATHER_TIMES
--------------------
19-mar-99 10:43:55
19-mar-99 15:21:23
2 rows selected.
SVRMGR>
SVRMGR> spool off;