|
Oracle8 Turning 〜計測開始〜
Oracle8 Tuning
〜計測開始〜
０．更新履歴
１．計測開始
パフォーマンスの計測は，utlbstat.sqlを実行してパフォーマンス統計取得用のテーブル等を準備し，計測したい処理を実行，utlestat.sqlで計測停止という手順となる．
- utlbstat.sql,utlestat.sqlがある事を確認する．
db1% cd $ORACLE_HOME/rdbms/admin
db1% pwd
/oracle/app/oracle/product/8.1.5/rdbms/admin
db1% ls -la *stat.sql
-rw-r--r-- 1 oracle other 50653 2月 16日 1999年 dbmsstat.sql
-rw-r--r-- 1 oracle other 4546 2月 16日 1999年 utlbstat.sql
-rw-r--r-- 1 oracle other 14187 2月 16日 1999年 utlestat.sql
db1%
|
- サーバマネージャを起動し，utlbstat.sqlを実行する．
SVRMGR> @utlbstat.sql
Echo ON
SVRMGR> connect internal;
Connected.
SVRMGR>
SVRMGR> Rem ********************************************************************
SVRMGR> Rem First create all the tables
SVRMGR> Rem ********************************************************************
SVRMGR>
SVRMGR> drop table stats$begin_stats;
drop table stats$begin_stats
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_stats as select * from v$sysstat where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_stats;
drop table stats$end_stats
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_stats as select * from stats$begin_stats;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_latch;
drop table stats$begin_latch
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_latch as select * from v$latch where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_latch;
drop table stats$end_latch
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_latch as select * from stats$begin_latch;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_roll;
drop table stats$begin_roll
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_roll as select * from v$rollstat where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_roll;
drop table stats$end_roll
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_roll as select * from stats$begin_roll;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_lib;
drop table stats$begin_lib
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_lib as select * from v$librarycache where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_lib;
drop table stats$end_lib
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_lib as select * from stats$begin_lib;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_dc;
drop table stats$begin_dc
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_dc as select * from v$rowcache where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_dc;
drop table stats$end_dc
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_dc as select * from stats$begin_dc;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_event;
drop table stats$begin_event
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_event as select * from v$system_event where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_event;
drop table stats$end_event
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_event as select * from stats$begin_event;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_bck_event;
drop table stats$begin_bck_event
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_bck_event
2> (event varchar2(200), total_waits number, time_waited number);
Statement processed.
SVRMGR> drop table stats$end_bck_event;
drop table stats$end_bck_event
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_bck_event as select * from stats$begin_bck_event;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$dates;
drop table stats$dates
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$dates (start_time date, end_time date);
Statement processed.
SVRMGR>
SVRMGR> drop view stats$file_view;
drop view stats$file_view
*
ORA-00942: table or view does not exist
SVRMGR> create view stats$file_view as
2> select ts.name ts,
3> i.name name,
4> x.phyrds pyr,
5> x.phywrts pyw,
6> x.readtim prt,
7> x.writetim pwt,
8> x.phyblkrd pbr,
9> x.phyblkwrt pbw,
10> round(i.bytes/1000000) megabytes_size
11> from v$filestat x, ts$ ts, v$datafile i,file$ f
12> where i.file#=f.file#
13> and ts.ts#=f.ts#
14> and x.file#=f.file#;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_file;
drop table stats$begin_file
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_file as select * from stats$file_view where 0 = 1;
Statement processed.
SVRMGR> drop table stats$end_file;
drop table stats$end_file
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_file as select * from stats$begin_file;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_waitstat;
drop table stats$begin_waitstat
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$begin_waitstat as select * from v$waitstat where 1=0;
Statement processed.
SVRMGR> drop table stats$end_waitstat;
drop table stats$end_waitstat
*
ORA-00942: table or view does not exist
SVRMGR> create table stats$end_waitstat as select * from stats$begin_waitstat;
Statement processed.
SVRMGR>
SVRMGR>
SVRMGR> Rem ********************************************************************
SVRMGR> Rem Gather start statistics
SVRMGR> Rem ********************************************************************
SVRMGR>
SVRMGR> insert into stats$dates select sysdate, null from dual;
1 row processed.
SVRMGR>
SVRMGR> insert into stats$begin_waitstat select * from v$waitstat;
14 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_bck_event
2> select event, sum(total_waits), sum(time_waited)
3> from v$session s, v$session_event e
4> where type = 'BACKGROUND' and s.sid = e.sid
5> group by event;
15 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_event select * from v$system_event;
26 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_roll select * from v$rollstat;
5 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_file select * from stats$file_view;
8 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_dc select * from v$rowcache;
23 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_stats select * from v$sysstat;
219 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_lib select * from v$librarycache;
8 rows processed.
SVRMGR>
SVRMGR> insert into stats$begin_latch select * from v$latch;
142 rows processed.
SVRMGR>
SVRMGR> commit;
Statement processed.
SVRMGR>
|
- エラーが出ているが，これは不要なテーブルを削除しているためで，初回実行時には存在しないためである．
- その後のcreate及びinsert等の処理がStatement processedとなっていれば，問題ない．
- データベースを利用する処理を実行する．
- 今回は，impコマンドを用いて全データインポートを行う処理とした．
- 処理が終了したら，サーバマネージャよりutlestat.sqlを実行する．
db1% svrmgrl
Oracle Server Manager Release 3.1.5.0.0 - Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> @utlestat.sql
Echo ON
SVRMGR> connect internal;
Connected.
SVRMGR>
SVRMGR> Rem ********************************************************************
SVRMGR> Rem Gather Ending Statistics
SVRMGR> Rem ********************************************************************
SVRMGR>
SVRMGR>
SVRMGR> insert into stats$end_latch select * from v$latch;
142 rows processed.
SVRMGR> insert into stats$end_stats select * from v$sysstat;
219 rows processed.
SVRMGR> insert into stats$end_lib select * from v$librarycache;
8 rows processed.
SVRMGR> update stats$dates set end_time = sysdate;
1 row processed.
SVRMGR> insert into stats$end_event select * from v$system_event;
36 rows processed.
SVRMGR> insert into stats$end_bck_event
2> select event, sum(total_waits), sum(time_waited)
3> from v$session s, v$session_event e
4> where type = 'BACKGROUND' and s.sid = e.sid
5> group by event;
22 rows processed.
SVRMGR> insert into stats$end_waitstat select * from v$waitstat;
14 rows processed.
SVRMGR> insert into stats$end_roll select * from v$rollstat;
5 rows processed.
SVRMGR> insert into stats$end_file select * from stats$file_view;
8 rows processed.
SVRMGR> insert into stats$end_dc select * from v$rowcache;
23 rows processed.
SVRMGR>
SVRMGR> Rem ********************************************************************
SVRMGR> Rem Create Summary Tables
SVRMGR> Rem ********************************************************************
SVRMGR>
SVRMGR> drop table stats$stats;
drop table stats$stats
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$latches;
drop table stats$latches
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$roll;
drop table stats$roll
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$files;
drop table stats$files
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$dc;
drop table stats$dc
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$lib;
drop table stats$lib
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$event;
drop table stats$event
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$bck_event;
drop table stats$bck_event
*
ORA-00942: table or view does not exist
SVRMGR> drop table stats$waitstat;
drop table stats$waitstat
*
ORA-00942: table or view does not exist
SVRMGR>
SVRMGR> create table stats$stats as
2> select e.value-b.value change , n.name
3> from v$statname n , stats$begin_stats b , stats$end_stats e
4> where n.statistic# = b.statistic# and n.statistic# = e.statistic#;
Statement processed.
SVRMGR>
SVRMGR> create table stats$latches as
2> select e.gets-b.gets gets,
3> e.misses-b.misses misses,
4> e.sleeps-b.sleeps sleeps,
5> e.immediate_gets-b.immediate_gets immed_gets,
6> e.immediate_misses-b.immediate_misses immed_miss,
7> n.name
8> from v$latchname n , stats$begin_latch b , stats$end_latch e
9> where n.latch# = b.latch# and n.latch# = e.latch#;
Statement processed.
SVRMGR>
SVRMGR> create table stats$event as
2> select e.total_waits-b.total_waits event_count,
3> e.time_waited-b.time_waited time_waited,
4> e.event
5> from stats$begin_event b , stats$end_event e
6> where b.event = e.event
7> union all
8> select e.total_waits event_count,
9> e.time_waited time_waited,
10> e.event
11> from stats$end_event e
12> where e.event not in (select b.event from stats$begin_event b);
Statement processed.
SVRMGR>
SVRMGR> Rem background waits
SVRMGR> create table stats$bck_event as
2> select e.total_waits-b.total_waits event_count,
3> e.time_waited-b.time_waited time_waited,
4> e.event
5> from stats$begin_bck_event b , stats$end_bck_event e
6> where b.event = e.event
7> union all
8> select e.total_waits event_count,
9> e.time_waited time_waited,
10> e.event
11> from stats$end_bck_event e
12> where e.event not in (select b.event from stats$begin_bck_event b);
Statement processed.
SVRMGR>
SVRMGR> Rem subtrace background events out of regular events
SVRMGR> update stats$event e
2> set (event_count, time_waited) =
3> (select e.event_count - b.event_count,
4> e.time_waited - b.time_waited
5> from stats$bck_event b
6> where e.event = b.event)
7> where e.event in (select b.event from stats$bck_event b);
22 rows processed.
SVRMGR>
SVRMGR> create table stats$waitstat as
2> select e.class,
3> e.count - b.count count,
4> e.time - b.time time
5> from stats$begin_waitstat b, stats$end_waitstat e
6> where e.class = b.class;
Statement processed.
SVRMGR>
SVRMGR> create table stats$roll as
2> select e.usn undo_segment,
3> e.gets-b.gets trans_tbl_gets,
4> e.waits-b.waits trans_tbl_waits,
5> e.writes-b.writes undo_bytes_written,
6> e.rssize segment_size_bytes,
7> e.xacts-b.xacts xacts,
8> e.shrinks-b.shrinks shrinks,
9> e.wraps-b.wraps wraps
10> from stats$begin_roll b, stats$end_roll e
11> where e.usn = b.usn;
Statement processed.
SVRMGR>
SVRMGR> create table stats$files as
2> select b.ts table_space,
3> b.name file_name,
4> e.pyr-b.pyr phys_reads,
5> e.pbr-b.pbr phys_blks_rd,
6> e.prt-b.prt phys_rd_time,
7> e.pyw-b.pyw phys_writes,
8> e.pbw-b.pbw phys_blks_wr,
9> e.pwt-b.pwt phys_wrt_tim,
10> e.megabytes_size
11> from stats$begin_file b, stats$end_file e
12> where b.name=e.name;
Statement processed.
SVRMGR>
SVRMGR> create table stats$dc as
2> select b.parameter name,
3> e.gets-b.gets get_reqs,
4> e.getmisses-b.getmisses get_miss,
5> e.scans-b.scans scan_reqs,
6> e.scanmisses-b.scanmisses scan_miss,
7> e.modifications-b.modifications mod_reqs,
8> e.count count,
9> e.usage cur_usage
10> from stats$begin_dc b, stats$end_dc e
11> where b.cache#=e.cache#
12> and nvl(b.subordinate#,-1) = nvl(e.subordinate#,-1);
Statement processed.
SVRMGR>
SVRMGR> create table stats$lib as
2> select e.namespace,
3> e.gets-b.gets gets,
4> e.gethits-b.gethits gethits,
5> e.pins-b.pins pins,
6> e.pinhits-b.pinhits pinhits,
7> e.reloads - b.reloads reloads,
8> e.invalidations - b.invalidations invalidations
9> from stats$begin_lib b, stats$end_lib e
10> where b.namespace = e.namespace;
Statement processed.
SVRMGR>
SVRMGR>
SVRMGR> Rem *******************************************************************
SVRMGR> Rem Output statistics
SVRMGR> Rem *******************************************************************
SVRMGR>
SVRMGR> spool report.txt;
SVRMGR>
SVRMGR> set charwidth 12
Charwidth 12
SVRMGR> set numwidth 10
Numwidth 10
SVRMGR> Rem Select Library cache statistics. The pin hit rate should 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 1 1 1 1 0 0
CLUSTER 46 1 70 1 0 0
INDEX 56 .018 56 .018 0 0
OBJECT 0 1 0 1 0 0
PIPE 0 1 0 1 0 0
SQL AREA 9505 .961 61914 .989 1 127
TABLE/PROCED 919 .838 1468 .716 48 0
TRIGGER 0 1 0 1 0 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> 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 stats$stats n1, stats$stats trans, stats$stats logs, stats$dates
11> where trans.name='user commits'
12> and logs.name='logons cumulative'
13> and n1.change != 0
14> order by n1.name;
Statistic Total Per Transact Per Logon Per Second
--------------------------- ------------ ------------ ------------ ------------
CPU used by this session 22578 149.52 5644.5 4.13
CPU used when call started 22578 149.52 5644.5 4.13
CR blocks created 2005 13.28 501.25 .37
DBWR buffers scanned 33394 221.15 8348.5 6.11
DBWR checkpoint buffers wri 165135 1093.61 41283.75 30.23
DBWR checkpoints 751 4.97 187.75 .14
DBWR free buffers found 22123 146.51 5530.75 4.05
DBWR lru scans 74 .49 18.5 .01
DBWR make free requests 94 .62 23.5 .02
DBWR summed scan depth 33869 224.3 8467.25 6.2
DBWR transaction table writ 761 5.04 190.25 .14
DBWR undo block writes 15593 103.26 3898.25 2.85
SQL*Net roundtrips to/from 33495 221.82 8373.75 6.13
background checkpoints comp 752 4.98 188 .14
background checkpoints star 751 4.97 187.75 .14
background timeouts 4747 31.44 1186.75 .87
buffer is not pinned count 329414 2181.55 82353.5 60.31
buffer is pinned count 31067 205.74 7766.75 5.69
bytes received via SQL*Net 187330217 1240597.46 46832554.25 34297
bytes sent via SQL*Net to c 4276571 28321.66 1069142.75 782.97
calls to get snapshot scn: 48390 320.46 12097.5 8.86
calls to kcmgas 2417 16.01 604.25 .44
calls to kcmgcs 118630 785.63 29657.5 21.72
change write time 6569 43.5 1642.25 1.2
cleanouts and rollbacks - c 12 .08 3 0
cleanouts only - consistent 104847 694.35 26211.75 19.2
cluster key scan block gets 12095 80.1 3023.75 2.21
cluster key scans 5449 36.09 1362.25 1
commit cleanout failures: b 363 2.4 90.75 .07
commit cleanouts 15830 104.83 3957.5 2.9
commit cleanouts successful 15467 102.43 3866.75 2.83
consistent changes 2408 15.95 602 .44
consistent gets 602544 3990.36 150636 110.32
cursor authentications 57 .38 14.25 .01
data blocks consistent read 2011 13.32 502.75 .37
db block changes 1110248 7352.64 277562 203.27
db block gets 702959 4655.36 175739.75 128.7
deferred (CURRENT) block cl 3240 21.46 810 .59
dirty buffers inspected 71810 475.56 17952.5 13.15
enqueue releases 35867 237.53 8966.75 6.57
enqueue requests 35864 237.51 8966 6.57
enqueue timeouts 2 .01 .5 0
execute count 43256 286.46 10814 7.92
free buffer inspected 88124 583.6 22031 16.13
free buffer requested 371075 2457.45 92768.75 67.94
hot buffers moved to head o 3387 22.43 846.75 .62
immediate (CR) block cleano 104859 694.43 26214.75 19.2
immediate (CURRENT) block c 506 3.35 126.5 .09
leaf node splits 20 .13 5 0
logons cumulative 4 .03 1 0
logons current -1 -.01 -.25 0
messages received 12782 84.65 3195.5 2.34
messages sent 12781 84.64 3195.25 2.34
no buffer to keep pinned co 56 .37 14 .01
no work - consistent read g 232549 1540.06 58137.25 42.58
opened cursors cumulative 9028 59.79 2257 1.65
opened cursors current -1 -.01 -.25 0
parse count (hard) 556 3.68 139 .1
parse count (total) 9585 63.48 2396.25 1.75
parse time cpu 142 .94 35.5 .03
parse time elapsed 154 1.02 38.5 .03
physical reads 317056 2099.71 79264 58.05
physical reads direct 82280 544.9 20570 15.06
physical writes 373775 2475.33 93443.75 68.43
physical writes direct 125562 831.54 31390.5 22.99
physical writes non checkpo 332091 2199.28 83022.75 60.8
pinned buffers inspected 16074 106.45 4018.5 2.94
prefetched blocks 200087 1325.08 50021.75 36.63
prefetched blocks aged out 6909 45.75 1727.25 1.26
process last non-idle time 838920491 5555764.84 209730122.75 153592.18
recursive calls 138142 914.85 34535.5 25.29
recursive cpu usage 6200 41.06 1550 1.14
redo blocks written 750259 4968.6 187564.75 137.36
redo buffer allocation retr 2719 18.01 679.75 .5
redo entries 741653 4911.61 185413.25 135.78
redo log space requests 3658 24.23 914.5 .67
redo log space wait time 304468 2016.34 76117 55.74
redo size 371316372 2459048.82 92829093 67981.76
redo synch time 651 4.31 162.75 .12
redo synch writes 343 2.27 85.75 .06
redo wastage 1783948 11814.23 445987 326.61
redo write time 44654 295.72 11163.5 8.18
redo writer latching time 30 .2 7.5 .01
redo writes 7067 46.8 1766.75 1.29
rollback changes - undo rec 7 .05 1.75 0
rollbacks only - consistent 1992 13.19 498 .36
rows fetched via callback 2630 17.42 657.5 .48
session connect time 838920491 5555764.84 209730122.75 153592.18
session logical reads 1305503 8645.72 326375.75 239.02
session pga memory 2919876 19336.93 729969 534.58
session pga memory max 3399920 22516.03 849980 622.47
session uga memory -23756 -157.32 -5939 -4.35
session uga memory max 138020 914.04 34505 25.27
sorts (disk) 41 .27 10.25 .01
sorts (memory) 507 3.36 126.75 .09
sorts (rows) 2128970 14099.14 532242.5 389.78
summed dirty queue length 64098 424.49 16024.5 11.74
table fetch by rowid 16475 109.11 4118.75 3.02
table fetch continued row 138 .91 34.5 .03
table scan blocks gotten 299214 1981.55 74803.5 54.78
table scan rows gotten 14796379 97989.26 3699094.75 2708.97
table scans (long tables) 43 .28 10.75 .01
table scans (short tables) 86 .57 21.5 .02
total file opens 1641 10.87 410.25 .3
transaction rollbacks 2 .01 .5 0
transaction tables consiste 1 .01 .25 0
transaction tables consiste 1 .01 .25 0
user calls 33506 221.89 8376.5 6.13
user commits 151 1 37.75 .03
write clones created in bac 96 .64 24 .02
write clones created in for 114 .75 28.5 .02
111 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set numwidth 27
Numwidth 27
SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger
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> Rem on the write batch size (normally 1024 or 2048 buffers). If the average
SVRMGR> Rem length of the dirty buffer write queue is larger than the value
SVRMGR> Rem calculated before, increase db_file_simultaneous_writes or db_files.
SVRMGR> Rem Also check for disks that 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 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 32;
Charwidth 32
SVRMGR> set numwidth 13;
Numwidth 13
SVRMGR> Rem System wide wait events for non-background processes (PMON,
SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one of
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
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
SVRMGR> Rem that event.
SVRMGR> 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
-------------------------------- ------------- ------------- -------------
virtual circuit status 178 546994 3073
dispatcher timer 89 546905 6145
log file switch (checkpoint inco 2896 227757 78.65
SQL*Net message from client 33527 179185 5.34
log file switch completion 759 76425 100.69
free buffer waits 281 26572 94.56
log buffer space 1967 4871 2.48
db file scattered read 28860 3143 .11
db file sequential read 4549 1098 .24
log file sync 343 650 1.9
latch free 120 270 2.25
SQL*Net more data from client 72279 182 0
sort segment request 1 103 103
db file single write 52 57 1.1
rdbms ipc reply 52 10 .19
SQL*Net message to client 33526 6 0
control file sequential read 433 2 0
refresh controlfile command 5 2 .4
direct path write 185 1 .01
direct path read 8 0 0
file identify 104 0 0
file open 75 0 0
22 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> Rem System wide wait events for background processes (PMON, SMON, etc)
SVRMGR> 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 10860 1938749 178.52
pmon timer 3075 546251 177.64
smon timer 20 530779 26538.95
log file parallel write 7067 44655 6.32
control file parallel write 10630 11701 1.1
log file single write 1502 755 .5
free buffer waits 7 630 90
log file switch (checkpoint inco 3 286 95.33
enqueue 42 187 4.45
control file sequential read 26354 85 0
db file parallel write 850 33 .04
LGWR wait for redo copy 245 30 .12
latch free 15 30 2
rdbms ipc reply 35 28 .8
refresh controlfile command 9 22 2.44
db file sequential read 1249 9 .01
direct path read 6016 8 0
file identify 1542 7 0
direct path write 6016 2 0
log file sequential read 751 2 0
db file scattered read 148 1 .01
file open 1576 1 0
22 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 18;
Charwidth 18
SVRMGR> set numwidth 11;
Numwidth 11
SVRMGR> Rem Latch statistics. Latch contention will show up as a large value for
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)
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
------------------ ----------- ----------- ----------- ----------- -----------
Active checkpoint 7986 2 1 0 0
Checkpoint queue l 766805 553 .999 95 .172
Token Manager 12220 0 1 0 0
begin backup scn a 1215 0 1 0 0
cache buffer handl 2 0 1 0 0
cache buffers chai 5311738 19 1 8 .421
cache buffers lru 581813 470 .999 32 .068
channel handle poo 3 0 1 0 0
channel operations 6 0 1 0 0
dml lock allocatio 7275 0 1 0 0
enqueue hash chain 71713 0 1 0 0
enqueues 133080 2 1 0 0
event group latch 3 0 1 0 0
job_queue_processe 837 0 1 0 0
ktm global data 24 0 1 0 0
library cache 195676 0 1 0 0
library cache load 634 0 1 0 0
list of block allo 4479 0 1 0 0
loader state objec 1782 0 1 0 0
longop free list 57 0 1 0 0
messages 55948 2 1 0 0
multiblock read ob 64636 0 1 0 0
ncodef allocation 837 0 1 0 0
process allocation 3 0 1 0 0
process group crea 1675 0 1 0 0
redo allocation 761136 131 1 0 0
redo writing 36501 4 1 0 0
row cache objects 66417 2 1 0 0
sequence cache 8 0 1 0 0
session allocation 1140 0 1 0 0
session idle bit 67491 0 1 0 0
session switching 836 0 1 0 0
session timer 2276 0 1 0 0
shared pool 24193 0 1 0 0
sort extent pool 110 0 1 0 0
transaction alloca 4868 0 1 0 0
transaction branch 836 0 1 0 0
undo global data 126627 1 1 0 0
user lock 8 0 1 0 0
virtual circuit qu 267 0 1 0 0
40 rows selected.
SVRMGR>
SVRMGR> set numwidth 16
Numwidth 16
SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not
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
6> from stats$latches
7> where immed_gets + immed_miss != 0
8> order by name;
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------ ---------------- ---------------- ----------------
cache buffers chai 659701 241 1
cache buffers lru 370546 84 1
channel handle poo 5 0 1
channel operations 5 0 1
dispatcher configu 89 0 1
process allocation 3 0 1
process group crea 4 0 1
redo copy 743527 246 1
8 rows selected.
SVRMGR>
SVRMGR> Rem Buffer busy wait statistics. If the value for 'buffer busy wait' in
SVRMGR> Rem the wait event statistics is high, then this table will identify
SVRMGR> Rem which class of blocks is having high contention. If there are high
SVRMGR> Rem 'undo header' waits then add more rollback segments. If there are
SVRMGR> Rem high 'segment header' waits then adding freelists might help. Check
SVRMGR> Rem v$session_wait to get the addresses of the actual blocks having
SVRMGR> Rem contention.
SVRMGR> select * from stats$waitstat
2> where count != 0
3> order by count desc;
CLASS COUNT TIME
------------------ ---------------- ----------------
0 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 SEGMEN
T_SIZE_BYTES XACTS SHRINKS WRAPS
------------------- ------------------- ------------------- ------------------- ------
------------- ------------------- ------------------- -------------------
0 21 0 0
612352 0 0 0
2 2112 0 2092470
4636672 0 0 17
3 13722 0 23483930
19587072 0 0 189
4 1907 0 2066050
5928960 0 0 16
5 2038 0 2083180
19703808 0 0 17
5 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'
2> order by name;
NAME VALUE
--------------------------------------- ---------------------------------------
background_dump_dest /oracle/app/oracle/admin/o8i/bdump
compatible 8.1.0
control_files /oracle/data/oradata/o8i/control01.ctl,
core_dump_dest /oracle/app/oracle/admin/o8i/cdump
db_block_buffers 8192
db_block_size 2048
db_domain testInstall.com
db_name o8i
distributed_transactions 10
instance_name o8i
java_pool_size 20971520
job_queue_interval 60
job_queue_processes 0
log_buffer 163840
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
mts_dispatchers (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))(D
mts_servers 1
open_links 4
os_authent_prefix
processes 30
remote_login_passwordfile EXCLUSIVE
rollback_segments r01, r02, r03, r04
service_names o8i.testInstall.com
shared_pool_size 15728640
timed_statistics TRUE
user_dump_dest /oracle/app/oracle/admin/o8i/udump
27 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 2966 8 0 0 0 11 9
dc_free_extents 3036 957 739 0 2391 56 26
dc_segments 1524 158 0 0 722 148 147
dc_rollback_seg 240 0 0 0 0 16 7
dc_used_extents 976 580 0 0 976 232 203
dc_tablespace_q 127 1 0 0 127 23 1
dc_files 106 3 0 0 52 14 3
dc_users 2077 9 0 0 0 12 10
dc_user_grants 1497 8 0 0 0 20 8
dc_objects 1081 233 0 0 408 305 300
dc_usernames 655 1 0 0 0 21 2
dc_object_ids 490 125 0 0 104 215 206
dc_constraints 188 94 0 0 188 51 49
dc_sequences 8 7 0 0 1 9 8
dc_profiles 2 1 0 0 0 14 1
dc_histogram_de 269 225 0 0 225 229 225
16 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 80;
Charwidth 80
SVRMGR> set numwidth 10;
Numwidth 10
SVRMGR> Rem Sum IO operations over tablespaces.
SVRMGR> 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
------------------------------------------------------------------------------- ------
---- ---------- ---------- ---------- ---------- ---------- ----------
DRSYS
752 752 0 752 752 0 84
INDX
752 752 0 752 752 0 10
OEM_REPOSITORY
752 752 0 752 752 0 31
RBS
770 770 0 17135 17135 140987601 50
SYSTEM 5
2485 79749 122 97866 97866 12835612 184
TEMP
760 824 0 824 824 0 10
USERS
752 752 0 752 752 0 10
USR 3
3484 232715 4235 232366 254942 1577833245 8389
8 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 48;
Charwidth 48
SVRMGR> set numwidth 10;
Numwidth 10
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 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> from stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME READS
BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES
------------------------------ ------------------------------------------------ ------
---- ---------- ---------- ---------- ---------- ---------- ----------
DRSYS /oracle/data/oradata/o8i/drsys01.dbf
752 752 0 752 752 0 84
INDX /oracle/data/oradata/o8i/indx01.dbf
752 752 0 752 752 0 10
OEM_REPOSITORY /oracle/data/oradata/o8i/oemrep01.dbf
752 752 0 752 752 0 31
RBS /oracle/data/oradata/o8i/rbs01.dbf
770 770 0 17135 17135 140987601 50
SYSTEM /oracle/data/oradata/o8i/system01.dbf 5
2485 79749 122 97866 97866 12835612 184
TEMP /oracle/data/oradata/o8i/temp01.dbf
760 824 0 824 824 0 10
USERS /oracle/data/oradata/o8i/users01.dbf
752 752 0 752 752 0 10
USR /oracle/data/usr.dbf 3
3484 232715 4235 232366 254942 1577833245 8389
8 rows selected.
SVRMGR>
SVRMGR>
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
------------------ ------------------
19-jan-01 20:32:31 19-jan-01 22:03:33
1 row selected.
SVRMGR>
SVRMGR> set charwidth 75
Charwidth 75
SVRMGR> Rem Versions
SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
5 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> spool off;
SVRMGR>
SVRMGR> Rem ********************************************************************
SVRMGR> Rem Drop Temporary Tables
SVRMGR> Rem ********************************************************************
SVRMGR>
SVRMGR> drop table stats$dates;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_stats;
Statement processed.
SVRMGR> drop table stats$end_stats;
Statement processed.
SVRMGR> drop table stats$stats;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_latch;
Statement processed.
SVRMGR> drop table stats$end_latch;
Statement processed.
SVRMGR> drop table stats$latches;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_roll;
Statement processed.
SVRMGR> drop table stats$end_roll;
Statement processed.
SVRMGR> drop table stats$roll;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_file;
Statement processed.
SVRMGR> drop table stats$end_file;
Statement processed.
SVRMGR> drop table stats$files;
Statement processed.
SVRMGR> drop view stats$file_view;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_dc;
Statement processed.
SVRMGR> drop table stats$end_dc;
Statement processed.
SVRMGR> drop table stats$dc;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_lib;
Statement processed.
SVRMGR> drop table stats$end_lib;
Statement processed.
SVRMGR> drop table stats$lib;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_event;
Statement processed.
SVRMGR> drop table stats$end_event;
Statement processed.
SVRMGR> drop table stats$event;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_bck_event;
Statement processed.
SVRMGR> drop table stats$end_bck_event;
Statement processed.
SVRMGR> drop table stats$bck_event;
Statement processed.
SVRMGR>
SVRMGR> drop table stats$begin_waitstat;
Statement processed.
SVRMGR> drop table stats$end_waitstat;
Statement processed.
SVRMGR> drop table stats$waitstat;
Statement processed.
SVRMGR>
|
- サーバマネージャを終了し，現在のディレクトリにreport.txtが作成されていることを確認する．
SVRMGR> quit
Server Manager complete.
db1% ls -la report.txt
-rw-r--r-- 1 oracle other 34611 1月 19日 22:03 report.txt
db1%
|
SVRMGR>
SVRMGR> set charwidth 12
Charwidth 12
SVRMGR> set numwidth 10
Numwidth 10
SVRMGR> Rem Select Library cache statistics. The pin hit rate should 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 1 1 1 1 0 0
CLUSTER 46 1 70 1 0 0
INDEX 56 .018 56 .018 0 0
OBJECT 0 1 0 1 0 0
PIPE 0 1 0 1 0 0
SQL AREA 9505 .961 61914 .989 1 127
TABLE/PROCED 919 .838 1468 .716 48 0
TRIGGER 0 1 0 1 0 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> 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 stats$stats n1, stats$stats trans, stats$stats logs, stats$dates
11> where trans.name='user commits'
12> and logs.name='logons cumulative'
13> and n1.change != 0
14> order by n1.name;
Statistic Total Per Transact Per Logon Per Second
--------------------------- ------------ ------------ ------------ ------------
CPU used by this session 22578 149.52 5644.5 4.13
CPU used when call started 22578 149.52 5644.5 4.13
CR blocks created 2005 13.28 501.25 .37
DBWR buffers scanned 33394 221.15 8348.5 6.11
DBWR checkpoint buffers wri 165135 1093.61 41283.75 30.23
DBWR checkpoints 751 4.97 187.75 .14
DBWR free buffers found 22123 146.51 5530.75 4.05
DBWR lru scans 74 .49 18.5 .01
DBWR make free requests 94 .62 23.5 .02
DBWR summed scan depth 33869 224.3 8467.25 6.2
DBWR transaction table writ 761 5.04 190.25 .14
DBWR undo block writes 15593 103.26 3898.25 2.85
SQL*Net roundtrips to/from 33495 221.82 8373.75 6.13
background checkpoints comp 752 4.98 188 .14
background checkpoints star 751 4.97 187.75 .14
background timeouts 4747 31.44 1186.75 .87
buffer is not pinned count 329414 2181.55 82353.5 60.31
buffer is pinned count 31067 205.74 7766.75 5.69
bytes received via SQL*Net 187330217 1240597.46 46832554.25 34297
bytes sent via SQL*Net to c 4276571 28321.66 1069142.75 782.97
calls to get snapshot scn: 48390 320.46 12097.5 8.86
calls to kcmgas 2417 16.01 604.25 .44
calls to kcmgcs 118630 785.63 29657.5 21.72
change write time 6569 43.5 1642.25 1.2
cleanouts and rollbacks - c 12 .08 3 0
cleanouts only - consistent 104847 694.35 26211.75 19.2
cluster key scan block gets 12095 80.1 3023.75 2.21
cluster key scans 5449 36.09 1362.25 1
commit cleanout failures: b 363 2.4 90.75 .07
commit cleanouts 15830 104.83 3957.5 2.9
commit cleanouts successful 15467 102.43 3866.75 2.83
consistent changes 2408 15.95 602 .44
consistent gets 602544 3990.36 150636 110.32
cursor authentications 57 .38 14.25 .01
data blocks consistent read 2011 13.32 502.75 .37
db block changes 1110248 7352.64 277562 203.27
db block gets 702959 4655.36 175739.75 128.7
deferred (CURRENT) block cl 3240 21.46 810 .59
dirty buffers inspected 71810 475.56 17952.5 13.15
enqueue releases 35867 237.53 8966.75 6.57
enqueue requests 35864 237.51 8966 6.57
enqueue timeouts 2 .01 .5 0
execute count 43256 286.46 10814 7.92
free buffer inspected 88124 583.6 22031 16.13
free buffer requested 371075 2457.45 92768.75 67.94
hot buffers moved to head o 3387 22.43 846.75 .62
immediate (CR) block cleano 104859 694.43 26214.75 19.2
immediate (CURRENT) block c 506 3.35 126.5 .09
leaf node splits 20 .13 5 0
logons cumulative 4 .03 1 0
logons current -1 -.01 -.25 0
messages received 12782 84.65 3195.5 2.34
messages sent 12781 84.64 3195.25 2.34
no buffer to keep pinned co 56 .37 14 .01
no work - consistent read g 232549 1540.06 58137.25 42.58
opened cursors cumulative 9028 59.79 2257 1.65
opened cursors current -1 -.01 -.25 0
parse count (hard) 556 3.68 139 .1
parse count (total) 9585 63.48 2396.25 1.75
parse time cpu 142 .94 35.5 .03
parse time elapsed 154 1.02 38.5 .03
physical reads 317056 2099.71 79264 58.05
physical reads direct 82280 544.9 20570 15.06
physical writes 373775 2475.33 93443.75 68.43
physical writes direct 125562 831.54 31390.5 22.99
physical writes non checkpo 332091 2199.28 83022.75 60.8
pinned buffers inspected 16074 106.45 4018.5 2.94
prefetched blocks 200087 1325.08 50021.75 36.63
prefetched blocks aged out 6909 45.75 1727.25 1.26
process last non-idle time 838920491 5555764.84 209730122.75 153592.18
recursive calls 138142 914.85 34535.5 25.29
recursive cpu usage 6200 41.06 1550 1.14
redo blocks written 750259 4968.6 187564.75 137.36
redo buffer allocation retr 2719 18.01 679.75 .5
redo entries 741653 4911.61 185413.25 135.78
redo log space requests 3658 24.23 914.5 .67
redo log space wait time 304468 2016.34 76117 55.74
redo size 371316372 2459048.82 92829093 67981.76
redo synch time 651 4.31 162.75 .12
redo synch writes 343 2.27 85.75 .06
redo wastage 1783948 11814.23 445987 326.61
redo write time 44654 295.72 11163.5 8.18
redo writer latching time 30 .2 7.5 .01
redo writes 7067 46.8 1766.75 1.29
rollback changes - undo rec 7 .05 1.75 0
rollbacks only - consistent 1992 13.19 498 .36
rows fetched via callback 2630 17.42 657.5 .48
session connect time 838920491 5555764.84 209730122.75 153592.18
session logical reads 1305503 8645.72 326375.75 239.02
session pga memory 2919876 19336.93 729969 534.58
session pga memory max 3399920 22516.03 849980 622.47
session uga memory -23756 -157.32 -5939 -4.35
session uga memory max 138020 914.04 34505 25.27
sorts (disk) 41 .27 10.25 .01
sorts (memory) 507 3.36 126.75 .09
sorts (rows) 2128970 14099.14 532242.5 389.78
summed dirty queue length 64098 424.49 16024.5 11.74
table fetch by rowid 16475 109.11 4118.75 3.02
table fetch continued row 138 .91 34.5 .03
table scan blocks gotten 299214 1981.55 74803.5 54.78
table scan rows gotten 14796379 97989.26 3699094.75 2708.97
table scans (long tables) 43 .28 10.75 .01
table scans (short tables) 86 .57 21.5 .02
total file opens 1641 10.87 410.25 .3
transaction rollbacks 2 .01 .5 0
transaction tables consiste 1 .01 .25 0
transaction tables consiste 1 .01 .25 0
user calls 33506 221.89 8376.5 6.13
user commits 151 1 37.75 .03
write clones created in bac 96 .64 24 .02
write clones created in for 114 .75 28.5 .02
111 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set numwidth 27
Numwidth 27
SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger
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> Rem on the write batch size (normally 1024 or 2048 buffers). If the average
SVRMGR> Rem length of the dirty buffer write queue is larger than the value
SVRMGR> Rem calculated before, increase db_file_simultaneous_writes or db_files.
SVRMGR> Rem Also check for disks that 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 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 32;
Charwidth 32
SVRMGR> set numwidth 13;
Numwidth 13
SVRMGR> Rem System wide wait events for non-background processes (PMON,
SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one of
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
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
SVRMGR> Rem that event.
SVRMGR> 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
-------------------------------- ------------- ------------- -------------
virtual circuit status 178 546994 3073
dispatcher timer 89 546905 6145
log file switch (checkpoint inco 2896 227757 78.65
SQL*Net message from client 33527 179185 5.34
log file switch completion 759 76425 100.69
free buffer waits 281 26572 94.56
log buffer space 1967 4871 2.48
db file scattered read 28860 3143 .11
db file sequential read 4549 1098 .24
log file sync 343 650 1.9
latch free 120 270 2.25
SQL*Net more data from client 72279 182 0
sort segment request 1 103 103
db file single write 52 57 1.1
rdbms ipc reply 52 10 .19
SQL*Net message to client 33526 6 0
control file sequential read 433 2 0
refresh controlfile command 5 2 .4
direct path write 185 1 .01
direct path read 8 0 0
file identify 104 0 0
file open 75 0 0
22 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> Rem System wide wait events for background processes (PMON, SMON, etc)
SVRMGR> 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 10860 1938749 178.52
pmon timer 3075 546251 177.64
smon timer 20 530779 26538.95
log file parallel write 7067 44655 6.32
control file parallel write 10630 11701 1.1
log file single write 1502 755 .5
free buffer waits 7 630 90
log file switch (checkpoint inco 3 286 95.33
enqueue 42 187 4.45
control file sequential read 26354 85 0
db file parallel write 850 33 .04
LGWR wait for redo copy 245 30 .12
latch free 15 30 2
rdbms ipc reply 35 28 .8
refresh controlfile command 9 22 2.44
db file sequential read 1249 9 .01
direct path read 6016 8 0
file identify 1542 7 0
direct path write 6016 2 0
log file sequential read 751 2 0
db file scattered read 148 1 .01
file open 1576 1 0
22 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 18;
Charwidth 18
SVRMGR> set numwidth 11;
Numwidth 11
SVRMGR> Rem Latch statistics. Latch contention will show up as a large value for
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)
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
------------------ ----------- ----------- ----------- ----------- -----------
Active checkpoint 7986 2 1 0 0
Checkpoint queue l 766805 553 .999 95 .172
Token Manager 12220 0 1 0 0
begin backup scn a 1215 0 1 0 0
cache buffer handl 2 0 1 0 0
cache buffers chai 5311738 19 1 8 .421
cache buffers lru 581813 470 .999 32 .068
channel handle poo 3 0 1 0 0
channel operations 6 0 1 0 0
dml lock allocatio 7275 0 1 0 0
enqueue hash chain 71713 0 1 0 0
enqueues 133080 2 1 0 0
event group latch 3 0 1 0 0
job_queue_processe 837 0 1 0 0
ktm global data 24 0 1 0 0
library cache 195676 0 1 0 0
library cache load 634 0 1 0 0
list of block allo 4479 0 1 0 0
loader state objec 1782 0 1 0 0
longop free list 57 0 1 0 0
messages 55948 2 1 0 0
multiblock read ob 64636 0 1 0 0
ncodef allocation 837 0 1 0 0
process allocation 3 0 1 0 0
process group crea 1675 0 1 0 0
redo allocation 761136 131 1 0 0
redo writing 36501 4 1 0 0
row cache objects 66417 2 1 0 0
sequence cache 8 0 1 0 0
session allocation 1140 0 1 0 0
session idle bit 67491 0 1 0 0
session switching 836 0 1 0 0
session timer 2276 0 1 0 0
shared pool 24193 0 1 0 0
sort extent pool 110 0 1 0 0
transaction alloca 4868 0 1 0 0
transaction branch 836 0 1 0 0
undo global data 126627 1 1 0 0
user lock 8 0 1 0 0
virtual circuit qu 267 0 1 0 0
40 rows selected.
SVRMGR>
SVRMGR> set numwidth 16
Numwidth 16
SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not
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
6> from stats$latches
7> where immed_gets + immed_miss != 0
8> order by name;
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------ ---------------- ---------------- ----------------
cache buffers chai 659701 241 1
cache buffers lru 370546 84 1
channel handle poo 5 0 1
channel operations 5 0 1
dispatcher configu 89 0 1
process allocation 3 0 1
process group crea 4 0 1
redo copy 743527 246 1
8 rows selected.
SVRMGR>
SVRMGR> Rem Buffer busy wait statistics. If the value for 'buffer busy wait' in
SVRMGR> Rem the wait event statistics is high, then this table will identify
SVRMGR> Rem which class of blocks is having high contention. If there are high
SVRMGR> Rem 'undo header' waits then add more rollback segments. If there are
SVRMGR> Rem high 'segment header' waits then adding freelists might help. Check
SVRMGR> Rem v$session_wait to get the addresses of the actual blocks having
SVRMGR> Rem contention.
SVRMGR> select * from stats$waitstat
2> where count != 0
3> order by count desc;
CLASS COUNT TIME
------------------ ---------------- ----------------
0 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 21 0 0 612352 0 0 0
2 2112 0 2092470 4636672 0 0 17
3 13722 0 23483930 19587072 0 0 189
4 1907 0 2066050 5928960 0 0 16
5 2038 0 2083180 19703808 0 0 17
5 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'
2> order by name;
NAME VALUE
--------------------------------------- ---------------------------------------
background_dump_dest /oracle/app/oracle/admin/o8i/bdump
compatible 8.1.0
control_files /oracle/data/oradata/o8i/control01.ctl,
core_dump_dest /oracle/app/oracle/admin/o8i/cdump
db_block_buffers 8192
db_block_size 2048
db_domain testInstall.com
db_name o8i
distributed_transactions 10
instance_name o8i
java_pool_size 20971520
job_queue_interval 60
job_queue_processes 0
log_buffer 163840
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
mts_dispatchers (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))(D
mts_servers 1
open_links 4
os_authent_prefix
processes 30
remote_login_passwordfile EXCLUSIVE
rollback_segments r01, r02, r03, r04
service_names o8i.testInstall.com
shared_pool_size 15728640
timed_statistics TRUE
user_dump_dest /oracle/app/oracle/admin/o8i/udump
27 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 2966 8 0 0 0 11 9
dc_free_extents 3036 957 739 0 2391 56 26
dc_segments 1524 158 0 0 722 148 147
dc_rollback_seg 240 0 0 0 0 16 7
dc_used_extents 976 580 0 0 976 232 203
dc_tablespace_q 127 1 0 0 127 23 1
dc_files 106 3 0 0 52 14 3
dc_users 2077 9 0 0 0 12 10
dc_user_grants 1497 8 0 0 0 20 8
dc_objects 1081 233 0 0 408 305 300
dc_usernames 655 1 0 0 0 21 2
dc_object_ids 490 125 0 0 104 215 206
dc_constraints 188 94 0 0 188 51 49
dc_sequences 8 7 0 0 1 9 8
dc_profiles 2 1 0 0 0 14 1
dc_histogram_de 269 225 0 0 225 229 225
16 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 80;
Charwidth 80
SVRMGR> set numwidth 10;
Numwidth 10
SVRMGR> Rem Sum IO operations over tablespaces.
SVRMGR> 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
------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DRSYS 752 752 0 752 752 0 84
INDX 752 752 0 752 752 0 10
OEM_REPOSITORY 752 752 0 752 752 0 31
RBS 770 770 0 17135 17135 140987601 50
SYSTEM 52485 79749 122 97866 97866 12835612 184
TEMP 760 824 0 824 824 0 10
USERS 752 752 0 752 752 0 10
USR 33484 232715 4235 232366 254942 1577833245 8389
8 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 48;
Charwidth 48
SVRMGR> set numwidth 10;
Numwidth 10
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 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> from stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES
------------------------------ ------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
DRSYS /oracle/data/oradata/o8i/drsys01.dbf 752 752 0 752 752 0 84
INDX /oracle/data/oradata/o8i/indx01.dbf 752 752 0 752 752 0 10
OEM_REPOSITORY /oracle/data/oradata/o8i/oemrep01.dbf 752 752 0 752 752 0 31
RBS /oracle/data/oradata/o8i/rbs01.dbf 770 770 0 17135 17135 140987601 50
SYSTEM /oracle/data/oradata/o8i/system01.dbf 52485 79749 122 97866 97866 12835612 184
TEMP /oracle/data/oradata/o8i/temp01.dbf 760 824 0 824 824 0 10
USERS /oracle/data/oradata/o8i/users01.dbf 752 752 0 752 752 0 10
USR /oracle/data/usr.dbf 33484 232715 4235 232366 254942 1577833245 8389
8 rows selected.
SVRMGR>
SVRMGR>
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
------------------ ------------------
19-jan-01 20:32:31 19-jan-01 22:03:33
1 row selected.
SVRMGR>
SVRMGR> set charwidth 75
Charwidth 75
SVRMGR> Rem Versions
SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
5 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> spool off;
|
|
|
