| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> bstat/estat you requested
This is a multi-part message in MIME format.
------=_NextPart_000_0007_01C01268.2546CE70 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Steve, et al;
Here is the bstat/estat I ran for about 5 minutes. There were no users on and an index rebuild was running. Any ideas would be greatly appreciated. My boss thinks I did something to the database...
Thanks, as always,
Ruth
------=_NextPart_000_0007_01C01268.2546CE70 Content-Type: text/plain;
name="report.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="report.txt"
SVRMGR>=20
SVRMGR> set charwidth 12
Charwidth 12 SVRMGR> set numwidth 10 Numwidth 10
SVRMGR> select namespace library,
2> gets,=20
3> =
round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)=20
4> gethitratio,
5> pins,=20
6> =
round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)=20
7> pinhitratio,
8> reloads, invalidations
9> from stats$lib;
LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS =
INVALIDATI
BODY 0 1 0 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 9348 1 27972 1 0 =
0
TABLE/PROCED 18 1 22 1 0 =
0
TRIGGER 0 1 0 1 0 =
0
8 rows selected.
SVRMGR>=20
SVRMGR> set charwidth 27;
Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12
SVRMGR> Rem always be based on at least one logon.
SVRMGR> select n1.name "Statistic",=20
2> n1.change "Total",=20
3> round(n1.change/trans.change,2) "Per Transaction",
4> round(n1.change/logs.change,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"
11> where trans.name=3D'user commits'
12> and logs.name=3D'logons cumulative'
13> and n1.change !=3D 0
14> order by n1.name;
Statistic Total Per Transact Per Logon Per =
Second =20
--------------------------- ------------ ------------ ------------ = ------------ CPU used by this session 117 117 117 =
.36
CPU used when call started 117 117 117 =
.36
SQL*Net roundtrips to/from 508 508 508 = 1.56 background timeouts 324 324 324 =
.99
buffer is not pinned count 538473 538473 538473 = 1651.76 buffer is pinned count 2312 2312 2312 = 7.09 bytes received via SQL*Net 24340 24340 24340 = 74.66 bytes sent via SQL*Net to c 38477 38477 38477 = 118.03 calls to get snapshot scn: 9911 9911 9911 = 30.4 calls to kcmgas 290 290 290 =
.89
calls to kcmgcs 4 4 4 =
.01
change write time 33 33 33 = .1 cleanouts only - consistent 302 302 302 =
.93
cluster key scan block gets 540785 540785 540785 = 1658.85 cluster key scans 6944 6944 6944 = 21.3 commit cleanout failures: b 302 302 302 =
.93
commit cleanouts 628 628 628 = 1.93 commit cleanouts successful 326 326 326 = 1 consistent gets 552671 552671 552671 = 1695.31 db block changes 10342 10342 10342 = 31.72 db block gets 5816 5816 5816 = 17.84 deferred (CURRENT) block cl 152 152 152 =
.47
enqueue releases 5810 5810 5810 = 17.82 enqueue requests 5804 5804 5804 = 17.8 execute count 9347 9347 9347 = 28.67 free buffer requested 2435 2435 2435 = 7.47 immediate (CR) block cleano 302 302 302 =
.93
immediate (CURRENT) block c 11 11 11 =
.03
logons cumulative 1 1 1 = 0 messages received 2610 2610 2610 = 8.01 messages sent 2610 2610 2610 = 8.01 no work - consistent read g 538212 538212 538212 = 1650.96 opened cursors cumulative 9272 9272 9272 = 28.44 parse count (hard) 5 5 5 =
.02
parse count (total) 9347 9347 9347 = 28.67 parse time cpu 199 199 199 =
.61
parse time elapsed 223 223 223 =
.68
recursive calls 85630 85630 85630 = 262.67 redo blocks written 2669 2669 2669 = 8.19 redo entries 5285 5285 5285 = 16.21 redo size 1285872 1285872 1285872 = 3944.39 redo small copies 619 619 619 = 1.9 redo synch time 2 2 2 =
.01
redo synch writes 1 1 1 = 0 redo wastage 35248 35248 35248 = 108.12 redo write time 465 465 465 = 1.43 redo writes 292 292 292 = .9 session logical reads 558487 558487 558487 = 1713.15 session pga memory 350092 350092 350092 = 1073.9 session pga memory max 350092 350092 350092 = 1073.9 session uga memory -18608 -18608 -18608 = -57.08 session uga memory max 20512 20512 20512 = 62.92 sorts (memory) 12 12 12 =
.04
sorts (rows) 26304 26304 26304 = 80.69 user calls 403 403 403 = 1.24 user commits 1 1 1 = 0
SVRMGR>=20 SVRMGR>=20 SVRMGR> set numwidth 27 Numwidth 27
SVRMGR> Rem than the value of: SVRMGR> Rem 1. (db_files * db_file_simultaneous_writes)/2 SVRMGR> Rem or SVRMGR> Rem 2. 1/4 of db_block_buffers SVRMGR> Rem which ever is smaller and also there is a platform specific =limit
SVRMGR> select queue.change/writes.change "Average Write Queue Length"
2> from stats$stats queue, stats$stats writes
3> where queue.name =3D 'summed dirty queue length'
4> and writes.name =3D 'write requests';
Average Write Queue Length=20
SVRMGR>=20 SVRMGR>=20 SVRMGR> set charwidth 32; Charwidth 32 SVRMGR> set numwidth 13; Numwidth 13
SVRMGR> Rem these is a context switch which costs CPU time. By looking =
at
SVRMGR> Rem the Total Time you can often determine what is the =
bottleneck=20
SVRMGR> Rem that processes are waiting for. This shows the total time =
spent
SVRMGR> Rem waiting for a specific event and the average time per wait =
on=20
SVRMGR> Rem that event.
SVRMGR> select n1.event "Event Name",=20
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 =
=20
-------------------------------- ------------- ------------- = ------------- SQL*Net message from client 524 132971 = 253.76 sort segment request 324 32645 = 100.76 log file sync 1 2 = 2 SQL*Net message to client 524 1 = 0 control file sequential read 46 1 = .02 db file sequential read 18 1 = .06 file open 18 0 = 0 refresh controlfile command 7 0 =0
SVRMGR>=20
SVRMGR>=20
SVRMGR> Rem System wide wait events for background processes (PMON, =
SMON, etc)
SVRMGR> select n1.event "Event Name",=20
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 =
=20
-------------------------------- ------------- ------------- = ------------- rdbms ipc message 2830 104044 = 36.76 slave wait 647 65180 = 100.74 pmon timer 109 32783 = 300.76 rdbms ipc reply 2324 23132 = 9.95 log file parallel write 294 468 = 1.59 control file parallel write 108 168 = 1.56 control file sequential read 14 0 = 0 latch free 1 0 =0
SVRMGR>=20 SVRMGR>=20 SVRMGR> set charwidth 18; Charwidth 18 SVRMGR> set numwidth 11; Numwidth 11
SVRMGR> Rem the 'latch free' event in the wait events above.
SVRMGR> Rem Sleeps should be low. The hit_ratio should be high.
SVRMGR> select name latch_name, gets, misses,
2> round((gets-misses)/decode(gets,0,1,gets),3)=20
3> hit_ratio,
4> sleeps,
5> round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
6> from stats$latches=20
7> where gets !=3D 0
8> order by name;
LATCH_NAME GETS MISSES HIT_RATIO SLEEPS =
SLEEPS/MISS
------------------ ----------- ----------- ----------- ----------- =
-----------
Active checkpoint 107 0 1 0 =
0
Checkpoint queue l 5322 0 1 0 =
0
cache buffer handl 124 0 1 0 =
0
cache buffers chai 58955196 0 1 0 =
0
cache buffers lru 6660 0 1 0 =
0
dml lock allocatio 1167 0 1 0 =
0
enqueue hash chain 11586 0 1 0 =
0
enqueues 16803 0 1 0 =
0
library cache 111960 1 1 0 =
0
list of block allo 579 0 1 0 =
0
messages 15208 294 .981 1 =
.003
modify parameter v 6 0 1 0 =
0
ncodef allocation 5 0 1 0 =
0
redo allocation 5840 1 1 0 =
0
redo writing 798 0 1 0 =
0
row cache objects 37001 0 1 0 =
0
session allocation 6 0 1 0 =
0
session idle bit 796 0 1 0 =
0
session switching 5 0 1 0 =
0
shared pool 9588 0 1 0 =
0
sort extent pool 5 0 1 0 =
0
transaction alloca 869 0 1 0 =
0
transaction branch 5 0 1 0 =
0
undo global data 1170 0 1 0 =
0
24 rows selected.
SVRMGR>=20
SVRMGR> set numwidth 16
Numwidth 16
SVRMGR> Rem wait for the 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((immed_gets/(immed_gets+immed_miss)), 3)
5> nowait_hit_ratio=20
6> from stats$latches=20
7> where immed_gets + immed_miss !=3D 0
8> order by name;
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------ ---------------- ---------------- ----------------
cache buffers chai 311 0 1
cache buffers lru 2429 0 1
redo copy 5233 0 1
3 rows selected.
SVRMGR> Rem contention.
SVRMGR> select * from stats$waitstat=20
2> where count !=3D 0=20
3> order by count desc;
CLASS COUNT TIME =20
------------------ ---------------- ----------------
0 rows selected.
SVRMGR>=20 SVRMGR>=20 SVRMGR> set numwidth 19; Numwidth 19
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS =
UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS =
WRAPS =20
------------------- ------------------- ------------------- =
------------------- ------------------- ------------------- =
------------------- -------------------
0 1 0 =
0 118784 0 0 =
0
1 69 0 =
47096 52957184 0 0 =
0
2 68 0 =
47324 47714304 0 0 =
0
3 69 0 =
47324 52957184 0 0 =
0
4 69 0 =
47324 52957184 0 0 =
0
5 69 0 =
47340 52957184 0 0 =
0
6 69 0 =
47700 52486144 0 0 =
0
7 69 0 =
47292 52957184 0 0 =
0
8 70 0 =
47488 52957184 0 0 =
0
9 69 0 =
47356 52957184 0 0 =
0
10 70 0 =
48142 47714304 0 0 =
0
Charwidth 39
SVRMGR> Rem The init.ora parameters currently in effect:
SVRMGR> select name, value from v$parameter where isdefault =3D 'FALSE'=20
2> order by name;
NAME VALUE =
=20
--------------------------------------- =
---------------------------------------
background_dump_dest /usr/oracle/otrace =
=20
compatible 8.0.5.1 =
=20
control_files /prdvircs/misc/prdvircs.ctl, =
/prdvircs/
core_dump_dest /usr/oracle/otrace =
=20
db_block_buffers 50000 =
=20
db_block_checksum TRUE =
=20
db_block_size 4096 =
=20
db_domain universe =
=20
db_file_multiblock_read_count 8 =
=20
db_files 20 =
=20
db_name prdvircs =
=20
dbwr_io_slaves 2 =
=20
dml_locks 100 =
=20
license_max_sessions 150 =
=20
log_archive_dest /prdvircs/system/arch =
=20
log_archive_format log%s.arc =
=20
log_archive_start TRUE =
=20
log_buffer 9500 =
=20
log_checkpoint_interval 100000 =
=20
log_checkpoint_timeout 0 =
=20
max_dump_file_size 10240 =
=20
nls_date_format YYYYMMDD =
=20
open_cursors 200 =
=20
processes 100 =
=20
remote_login_passwordfile EXCLUSIVE =
=20
rollback_segments rbs01, rbs02, rbs03, rbs04, =
rbs05, rbs0
sequence_cache_entries 10 =
=20
sequence_cache_hash_buckets 10 =
=20
shared_pool_size 250000000 =
=20
sort_direct_writes TRUE =
=20
sort_write_buffer_size 65536 =
=20
sort_write_buffers 4 =
=20
timed_statistics TRUE =
=20
user_dump_dest /usr/oracle/otrace =
=20
Charwidth 15 SVRMGR> set numwidth 8; Numwidth 8
2> where get_reqs !=3D 0 or scan_reqs !=3D 0 or mod_reqs !=3D 0;
NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT =
CUR_USAG
28
dc_objects 16 0 0 0 0 169 =
167
dc_object_ids 2 0 0 0 0 103 =
101
4 rows selected.
SVRMGR>=20
SVRMGR>=20
SVRMGR> set charwidth 80;
Charwidth 80
SVRMGR> set numwidth 10;
Numwidth 10
SVRMGR> Rem Sum IO operations over tablespaces.
SVRMGR> select
2> table_space||' =
'=20
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=20
-------------------------------------------------------------------------=------ ---------- ---------- ---------- ---------- ---------- ---------- =
DATA =
0 0 0 0 0 0 =
4929
IDX =
0 0 0 0 0 0 =
4090
RBS =
0 0 0 0 0 0 =
839
SYSTEM =
0 0 0 0 0 0 =
262
TMP =
0 0 0 0 0 0 =
472
TOOLS =
0 0 0 0 0 0 =
5
USR =
0 0 0 0 0 0 =
21
SVRMGR>=20 SVRMGR>=20 SVRMGR> set charwidth 48; Charwidth 48 SVRMGR> set numwidth 10; Numwidth 10
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,=20
4> megabytes_size megabytes
5> from stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME =
READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME =
MEGABYTES=20
------------------------------ =
------------------------------------------------ ---------- ---------- =
---------- ---------- ---------- ---------- ----------
DATA /prdvircs/data/pvdata01.dbf =
0 0 0 0 0 0 =
1783
DATA /prdvircs/data/pvdata02.dbf =
0 0 0 0 0 0 =
1573
DATA /prdvircs/data/pvdata03.dbf =
0 0 0 0 0 0 =
1573
IDX /prdvircs/idx/pvidx01.dbf =
0 0 0 0 0 0 =
839
IDX /prdvircs/idx/pvidx02.dbf =
0 0 0 0 0 0 =
1049
IDX /prdvircs/idx/pvidx03.dbf =
0 0 0 0 0 0 =
734
IDX /prdvircs/idx/pvidx04.dbf =
0 0 0 0 0 0 =
734
IDX /prdvircs/system/idx/pvidx05.dbf =
0 0 0 0 0 0 =
734
RBS /prdvircs/misc/rbs/pvrbs01 =
0 0 0 0 0 0 =
839
SYSTEM /prdvircs/system/pvssys01.dbf =
0 0 0 0 0 0 =
105
SYSTEM /prdvircs/system/pvsys02.dbf =
0 0 0 0 0 0 =
157
TMP /prdvircs/misc/tmp/pvtmp01.dbf =
0 0 0 0 0 0 =
262
TMP /prdvircs/misc/tmp/pvtmp02.dbf =
0 0 0 0 0 0 =
210
TOOLS /prdvircs/misc/tools/pvtools01.dbf =
0 0 0 0 0 0 =
5
USR /prdvircs/misc/usr/pvusr01.dbf =
0 0 0 0 0 0 =
21
SVRMGR>=20
SVRMGR>=20
SVRMGR> set charwidth 25
Charwidth 25
SVRMGR> Rem The times that bstat and estat were run.
SVRMGR> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time,
2> to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time
3> from stats$dates;
START_TIME END_TIME =20
------------------ ------------------
 |
 |