基於AWR實現STATSPACK報告(6-例項元件)
Instance Activity Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 324,813 107.6 3.3
CPU used when call started 324,277 107.5 3.3
CR blocks created 28,894 9.6 0.3
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DB time 2,011,619 666.5 20.2
DBWR checkpoint buffers written 55,278 18.3 0.6
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.stat_name st,
e.VALUE - b.VALUE dif,
round((e.VALUE - b.VALUE) / &ela, 2) ps,
round((e.VALUE - b.VALUE) / &tran, 2) pt
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.stat_name = e.stat_name
AND e.stat_name NOT IN
('logons current', 'opened cursors current',
'workarea memory allocated', 'session cursor cache count')
AND e.VALUE >= b.VALUE
AND e.VALUE > 0
ORDER BY st;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Instance Activity Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 249 224
opened cursors current 2,830 2,594
session cursor cache count 27,675 28,892
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.stat_name st, b.VALUE begin_value, e.VALUE end_value
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.stat_name = e.stat_name
AND e.stat_name IN
('logons current', 'opened cursors current',
'workarea memory allocated', 'session cursor cache count')
AND (b.VALUE > 0 OR e.VALUE > 0);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Instance Activity Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 1 1.19
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'log switches (derived)' st,
e.sequence# - b.sequence# dif,
(e.sequence# - b.sequence#) / (&ela / 3600) ph
FROM dba_hist_thread e, dba_hist_thread b
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.thread# = e.thread#
AND b.thread_instance_number = e.thread_instance_number
AND e.thread_instance_number = &inst_num;
其中：
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
CAST(b.end_interval_time AS DATE)) * 1440 * 60
FROM dba_hist_snapshot b, dba_hist_snapshot e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
OS Statistics DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
NUM_LCPUS 0
NUM_VCPUS 0
BUSY_TIME 516,300
IDLE_TIME 9,162,519
IOWAIT_TIME 47,632
SYS_TIME 123,425
USER_TIME 392,875
LOAD 0
OS_CPU_WAIT_TIME 482,700
PHYSICAL_MEMORY_BYTES 68,719,476,736
NUM_CPUS 32
NUM_CPU_CORES 16
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT osn.stat_name osn,
decode(osn.stat_id,
0,
e.VALUE -- NUM_CPUS
,
15,
e.VALUE -- LOAD
,
16,
e.VALUE -- NUM_CPU_CORES
,
17,
e.VALUE -- NUM_CPU_SOCKETS
,
1008,
e.VALUE -- PHYSICAL_MEMORY_BYTES
,
e.VALUE - b.VALUE) dif,
(to_number(decode(sign(instrb(osn.stat_name, 'TIME')), 1, 1, 0)) +
to_number(decode(sign(instrb(osn.stat_name, 'LOAD')), 1, 2, 0)) +
to_number(decode(sign(instrb(osn.stat_name, 'CPU_WAIT')), 1, 3, 0)) +
to_number(decode(sign(instrb(osn.stat_name, 'VM_')), 1, 4, 0)) +
to_number(decode(sign(instrb(osn.stat_name, 'PHYSICAL_MEMORY')),
1,
5,
0)) +
to_number(decode(sign(instrb(osn.stat_name, 'NUM_CPU')), 1, 6, 0))) styp
FROM dba_hist_osstat b, dba_hist_osstat e, dba_hist_osstat_name osn
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.stat_id = e.stat_id
AND osn.stat_id = e.stat_id
AND osn.dbid = e.dbid
AND osn.stat_name NOT LIKE 'AVG_%'
AND e.VALUE >= b.VALUE
AND e.VALUE > 0
ORDER BY styp, osn;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Session Wait Events
...
Session Time Model Statistics
...
Session Statistics
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AWR中與SESSION相關的檢視只有：
DBA_HIST_ACTIVE_SESS_HISTORY--與SESSION相關
DBA_HIST_SESS_TIME_STATS--與SESSION_TYPE相關
DBA_HIST_SESSMETRIC_HISTORY--與SESSION相關
沒有類似stats$sesstat、stats$sess_time_model、stats$session_event的資料。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Tablespace IO Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
NET_TRANS
2,103,126 697 0.9 2.2 3,387 1 1 10.0
USERS
751 0 1.2 1.1 169 0 0 0.0
SYSTEM
900 0 1.7 1.0 12 0 1 0.0
NET_SIGN
98 0 6.6 1.0 753 0 2 0.0
UNDOTBS1
3 0 0.0 1.0 582 0 2 0.0
SYSAUX
6 0 1.7 1.0 0 0 0 0.0
TEMP
1 0 0.0 1.0 1 0 0 0.0
UNDOTBS2
1 0 0.0 1.0 0 0 39,603 1.2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.tsname,
SUM(e.phyrds - nvl(b.phyrds, 0)) reads,
SUM(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
0,
0,
(SUM(e.readtim - nvl(b.readtim, 0)) /
SUM(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
SUM(e.phyblkrd - nvl(b.phyblkrd, 0)) /
SUM(e.phyrds - nvl(b.phyrds, 0))) bpr,
SUM(e.phywrts - nvl(b.phywrts, 0)) writes,
SUM(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
SUM(e.wait_count - nvl(b.wait_count, 0)) waits,
decode(SUM(e.wait_count - nvl(b.wait_count, 0)),
0,
0,
(SUM(e.TIME - nvl(b.TIME, 0)) /
SUM(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt,
SUM(e.phyrds - nvl(b.phyrds, 0)) +
SUM(e.phywrts - nvl(b.phywrts, 0)) ios
FROM dba_hist_filestatxs e, dba_hist_filestatxs b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number(+) = e.instance_number
AND b.tsname(+) = e.tsname
AND b.filename(+) = e.filename
AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
GROUP BY e.tsname
UNION ALL
SELECT e.tsname tbsp,
SUM(e.phyrds - nvl(b.phyrds, 0)) reads,
SUM(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
0,
0,
(SUM(e.readtim - nvl(b.readtim, 0)) /
SUM(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
SUM(e.phyblkrd - nvl(b.phyblkrd, 0)) /
SUM(e.phyrds - nvl(b.phyrds, 0))) bpr,
SUM(e.phywrts - nvl(b.phywrts, 0)) writes,
SUM(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
SUM(e.wait_count - nvl(b.wait_count, 0)) waits,
decode(SUM(e.wait_count - nvl(b.wait_count, 0)),
0,
0,
(SUM(e.TIME - nvl(b.TIME, 0)) /
SUM(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt,
SUM(e.phyrds - nvl(b.phyrds, 0)) +
SUM(e.phywrts - nvl(b.phywrts, 0)) ios
FROM DBA_HIST_TEMPSTATXS e, DBA_HIST_TEMPSTATXS b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number(+) = e.instance_number
AND b.tsname(+) = e.tsname
AND b.filename(+) = e.filename
AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
GROUP BY e.tsname
ORDER BY ios DESC;
其中：
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
CAST(b.end_interval_time AS DATE)) * 1440 * 60
FROM dba_hist_snapshot b, dba_hist_snapshot e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
File IO Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
NET_DATA /dev/rPO_NET_DATA
308,793 102 1.3 ### 2.9 16,310 5 61,792 178.5
NET_INDEX /dev/rPO_NET_INDEX
65,000 22 2.7 ### 1.0 41,537 14 69 1.9
SYSAUX /dev/rPO_AUX1
6 0 1.7 8 1.0 0 0 0
SYSTEM /dev/rPO_SYS1
900 0 1.7 ### 1.0 12 0 1 0.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由於無stats$file_histogram、stats$temp_histogram，刪除mrt/max read time列的統計
SELECT e.tsname,
e.filename,
e.phyrds - nvl(b.phyrds, 0) reads,
(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
((e.readtim - nvl(b.readtim, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
(e.phyblkrd - nvl(b.phyblkrd, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) bpr,
e.phywrts - nvl(b.phywrts, 0) writes,
(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
e.wait_count - nvl(b.wait_count, 0) waits,
decode((e.wait_count - nvl(b.wait_count, 0)),
0,
to_number(NULL),
((e.TIME - nvl(b.TIME, 0)) /
(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt
FROM dba_hist_filestatxs e, dba_hist_filestatxs b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number(+) = e.instance_number
AND b.tsname(+) = e.tsname
AND b.filename(+) = e.filename
AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
UNION ALL
SELECT e.tsname,
e.filename,
e.phyrds - nvl(b.phyrds, 0) reads,
(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
((e.readtim - nvl(b.readtim, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
(e.phyblkrd - nvl(b.phyblkrd, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) bpr,
e.phywrts - nvl(b.phywrts, 0) writes,
(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
e.wait_count - nvl(b.wait_count, 0) waits,
decode((e.wait_count - nvl(b.wait_count, 0)),
0,
to_number(NULL),
((e.TIME - nvl(b.TIME, 0)) /
(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt
FROM dba_hist_tempstatxs e, dba_hist_tempstatxs b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number(+) = e.instance_number
AND b.tsname(+) = e.tsname
AND b.filename(+) = e.filename
AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
ORDER BY tsname, filename;
其中：
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
CAST(b.end_interval_time AS DATE)) * 1440 * 60
FROM dba_hist_snapshot b, dba_hist_snapshot e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
File Read Histogram Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
NET_DATA /dev/rPO_NET_DATA
48,879 1,186 2,290 442 145 288
NET_INDEX /dev/rPO_NET_INDEX
45,953 2,707 12,980 2,617 235 516
SYSAUX /dev/rPO_AUX1
5 0 1 0 0 0
SYSTEM /dev/rPO_SYS1
755 29 99 16 5 8
UNDOTBS1 /dev/rPO_UNDO1_A
2 0 0 0 0 0
/dev/rPO_UNDO2_A
1 0 0 0 0 0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由於無stats$file_histogram、stats$temp_histogram，刪除mrt/max read time列的統計
select fse.tsname
, fse.filename
, sum(case when (0 <= e.singleblkrdtim_milli)
and (e.singleblkrdtim_milli <= 2)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to2
, sum(case when ( e.singleblkrdtim_milli = 4)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to4
, sum(case when ( e.singleblkrdtim_milli = 8)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to8
, sum(case when ( e.singleblkrdtim_milli = 16)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to16
, sum(case when ( e.singleblkrdtim_milli = 32)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to32
, sum(case when 32 < e.singleblkrdtim_milli
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) over32
from stats$file_histogram e
, stats$file_histogram b
, stats$filestatxs fse
where b.snap_id(+) = :bid
and e.snap_id = :eid
and e.dbid = :dbid
and b.dbid(+) = e.dbid
and e.instance_number = :inst_num
and b.instance_number(+) = e.instance_number
and b.file#(+) = e.file#
and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli
and fse.snap_id = e.snap_id
and fse.dbid = e.dbid
and fse.instance_number = e.instance_number
and fse.file# = e.file#
and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0
and '&&file_histogram' = 'Y'
group by fse.tsname
, fse.filename
union all
select tse.tsname
, tse.filename
, sum(case when (0 <= e.singleblkrdtim_milli)
and (e.singleblkrdtim_milli <= 2)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to2
, sum(case when ( e.singleblkrdtim_milli = 4)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to4
, sum(case when ( e.singleblkrdtim_milli = 8)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to8
, sum(case when ( e.singleblkrdtim_milli = 16)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to16
, sum(case when ( e.singleblkrdtim_milli = 32)
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to32
, sum(case when 32 < e.singleblkrdtim_milli
then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) over32
from stats$temp_histogram e
, stats$temp_histogram b
, stats$tempstatxs tse
where b.snap_id(+) = :bid
and e.snap_id = :eid
and e.dbid = :dbid
and b.dbid(+) = e.dbid
and e.instance_number = :inst_num
and b.instance_number(+) = e.instance_number
and b.file#(+) = e.file#
and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli
and tse.snap_id = e.snap_id
and tse.dbid = e.dbid
and tse.instance_number = e.instance_number
and tse.file# = e.file#
and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0
and '&&file_histogram' = 'Y'
group by tse.tsname
, tse.filename;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Buffer Pool Statistics DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 406K 95 120,760,945 5,573,882 74,655 0 0 138,231
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select replace(e.block_size/1024||'k', &bs/1024||'k', substr(e.name,1,1)) name
, lpad(case
when e.set_msize <= 9999
then to_char(e.set_msize)||' '
when trunc((e.set_msize)/1000) <= 9999
then to_char(trunc((e.set_msize)/1000))||'K'
when trunc((e.set_msize)/1000000) <= 9999
then to_char(trunc((e.set_msize)/1000000))||'M'
when trunc((e.set_msize)/1000000000) <= 9999
then to_char(trunc((e.set_msize)/1000000000))||'G'
when trunc((e.set_msize)/1000000000000) <= 9999
then to_char(trunc((e.set_msize)/1000000000000))||'T'
else substr(to_char(trunc((e.set_msize)/1000000000000000))||'P', 1, 5) end
, 7, ' ') numbufs
, decode( e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0)
, 0, to_number(null)
, (100* (1 - ( (e.physical_reads - nvl(b.physical_reads,0))
/ ( e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0))
)
)
)
) poolhr
, e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0) buffs
, e.physical_reads - nvl(b.physical_reads,0) phread
, e.physical_writes - nvl(b.physical_writes,0) phwrite
, e.free_buffer_wait - nvl(b.free_buffer_wait,0) fbwait
, e.write_complete_wait - nvl(b.write_complete_wait,0) wcwait
, e.buffer_busy_wait - nvl(b.buffer_busy_wait,0) bbwait
from DBA_HIST_BUFFER_POOL_STAT b
, DBA_HIST_BUFFER_POOL_STAT e
where b.snap_id(+) = &bid
and e.snap_id = &eid
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.dbid(+) = e.dbid
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.instance_number(+) = e.instance_number
and b.id(+) = e.id
order by e.name;
其中：
bs-->db_block_size
SELECT VALUE
FROM dba_hist_parameter t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.parameter_name = 'db_block_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Instance Recovery Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 17 8526 36563 207744 1883700 207744
E 0 16 8081 36154 196848 1883700 196848
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select 'B' beg
, target_mttr tm
, estimated_mttr em
, recovery_estimated_ios rei
, actual_redo_blks arb
, target_redo_blks trb
, log_file_size_redo_blks lfrb
, log_chkpt_timeout_redo_blks lctrb
, log_chkpt_interval_redo_blks lcirb
, snap_id snid
from DBA_HIST_INSTANCE_RECOVERY b
where b.snap_id = &bid
and b.dbid = &dbid
and b.instance_number = &inst_num
union all
select 'E' beg
, target_mttr tm
, estimated_mttr em
, recovery_estimated_ios rei
, actual_redo_blks arb
, target_redo_blks trb
, log_file_size_redo_blks lfrb
, log_chkpt_timeout_redo_blks lctrb
, log_chkpt_interval_redo_blks lcirb
, snap_id snid
from DBA_HIST_INSTANCE_RECOVERY e
where e.snap_id = &eid
and e.dbid = &dbid
and e.instance_number = &inst_num
order by snid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Buffer Pool Advisory DB/Inst: ORADB/oraDB1 End Snap: 67
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 336 .1 40 3.2 43,324 20,519 29.9
D 672 .2 81 1.5 20,804 8,022 11.7
D 1,008 .3 121 1.3 17,559 6,221 9.1
D 1,344 .4 161 1.2 16,045 5,381 7.8
D 1,680 .5 201 1.1 15,126 4,871 7.1
D 2,016 .6 242 1.1 14,547 4,550 6.6
D 2,352 .7 282 1.0 14,278 4,400 6.4
D 2,688 .8 322 1.0 14,028 4,262 6.2
D 3,024 .9 363 1.0 13,792 4,131 6.0
D 3,360 1.0 403 1.0 13,613 4,031 5.9
D 3,376 1.0 405 1.0 13,605 4,027 5.9
D 3,696 1.1 443 1.0 13,461 3,947 5.8
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***dba_hist_db_cache_advice中沒有estd*列,刪除;bcsf列有簡化
SELECT REPLACE(block_size / 1024 || 'k',
&bs / 1024 || 'k',
substr(NAME, 1, 1)) bpool,
decode(block_size, &bs, 1, 2) order_def_bs,
size_for_estimate sfe,
size_factor bcsf,
buffers_for_estimate / 1000 bfe
--, estd_physical_read_factor eprf
--, estd_physical_reads/1000 epr
--, estd_physical_read_time eprt
--, estd_pct_of_db_time_for_reads epdbt
FROM dba_hist_db_cache_advice
WHERE snap_id = &eid
AND dbid = &dbid
AND instance_number = &inst_num
-- and estd_physical_reads > 0
ORDER BY order_def_bs, block_size, NAME, buffers_for_estimate;
其中：
bs-->db_block_size
SELECT VALUE
FROM dba_hist_parameter t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.parameter_name = 'db_block_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Buffer wait Statistics DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s)
-------------------------------------------- ----------- -------------------
Avg Time (ms)
-------------
data block 62,192 11,122
179
undo block 75,508 102
1
1st level bmb 4 0
0
undo header 1 0
0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.class
, e.wait_count - nvl(b.wait_count,0) icnt
, (e.time - nvl(b.time,0))/100 itim
,10* (e.time - nvl(b.time,0))
/ (e.wait_count - nvl(b.wait_count,0)) iavg
from DBA_HIST_WAITSTAT b
, DBA_HIST_WAITSTAT e
where b.snap_id = &bid
and e.snap_id = &eid
and b.dbid = &dbid
and e.dbid = &dbid
and b.dbid = e.dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.instance_number = e.instance_number
and b.class = e.class
and b.wait_count < e.wait_count
order by itim desc, icnt desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PGA Aggr Target Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 22,582 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 4,096 3,380 694.5 0.6 .1 100.0 .0 419,430
E 4,096 3,372 697.6 0.0 .0 .0 .0 419,430
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--PGA Cache Hit %：
SELECT 100 * (e.bytes - nvl(b.bytes, 0)) /
(e.bytes - nvl(b.bytes, 0) + e.bytesrw - nvl(b.bytesrw, 0)) calc_cache_pct,
(e.bytes - nvl(b.bytes, 0)) / 1024 / 1024 tbp,
(e.bytesrw - nvl(b.bytesrw, 0)) / 1024 / 1024 tbrw
FROM (SELECT SUM(CASE
WHEN NAME = 'bytes processed' THEN
VALUE
ELSE
0
END) bytes,
SUM(CASE
WHEN NAME = 'extra bytes read/written' THEN
VALUE
ELSE
0
END) bytesrw
FROM dba_hist_pgastat e1
WHERE e1.snap_id = &eid
AND e1.dbid = &dbid
AND e1.instance_number = &inst_num
AND e1.NAME IN ('bytes processed', 'extra bytes read/written')) e,
(SELECT SUM(CASE
WHEN NAME = 'bytes processed' THEN
VALUE
ELSE
0
END) bytes,
SUM(CASE
WHEN NAME = 'extra bytes read/written' THEN
VALUE
ELSE
0
END) bytesrw
FROM dba_hist_pgastat b1
WHERE b1.snap_id = &bid
AND b1.dbid = &dbid
AND b1.instance_number = &inst_num
AND b1.NAME IN ('bytes processed', 'extra bytes read/written')) b
WHERE e.bytes - nvl(b.bytes, 0) > 0;
--Warning:pga_aggregate_target設定太小,導致'over allocation count'增加
select 'Warning: pga_aggregate_target was set too low for current workload, as this' nl
, ' value was exceeded during this interval. Use the PGA Advisory view' nl
, ' to help identify a different value for pga_aggregate_target.' nl
from DBA_HIST_PGASTAT e
, DBA_HIST_PGASTAT b
, DBA_HIST_PARAMETER p
where e.snap_id = &eid
and e.dbid = &dbid
and e.instance_number = &inst_num
and e.name = 'over allocation count'
and b.snap_id(+) = &bid
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.name(+) = e.name
and e.value > nvl(b.value,0)
and p.snap_id = &eid
and p.dbid = &dbid
and p.instance_number = &inst_num
and p.parameter_name = 'workarea_size_policy'
and p.value = 'AUTO';
SELECT 'B' snap,
to_number(p.VALUE) / 1024 / 1024 pgaat,
mu.pat / 1024 / 1024 pat,
mu.pga_alloc / 1024 / 1024 tot_pga_allo,
(mu.pga_used_auto + mu.pga_used_man) / 1024 / 1024 tot_tun_used,
100 * (mu.pga_used_auto + mu.pga_used_man) / pga_alloc pct_tun,
decode(mu.pga_used_auto + mu.pga_used_man,
0,
0,
100 * mu.pga_used_auto / (mu.pga_used_auto + mu.pga_used_man)) pct_auto_tun,
decode(mu.pga_used_auto + mu.pga_used_man,
0,
0,
100 * mu.pga_used_man / (mu.pga_used_auto + mu.pga_used_man)) pct_man_tun,
mu.glob_mem_bnd / 1024 glo_mem_bnd
FROM (SELECT SUM(CASE
WHEN NAME = 'total PGA allocated' THEN
VALUE
ELSE
0
END) pga_alloc,
SUM(CASE
WHEN NAME = 'total PGA used for auto workareas' THEN
VALUE
ELSE
0
END) pga_used_auto,
SUM(CASE
WHEN NAME = 'total PGA used for manual workareas' THEN
VALUE
ELSE
0
END) pga_used_man,
SUM(CASE
WHEN NAME = 'global memory bound' THEN
VALUE
ELSE
0
END) glob_mem_bnd,
SUM(CASE
WHEN NAME = 'aggregate PGA auto target' THEN
VALUE
ELSE
0
END) pat
FROM dba_hist_pgastat pga
WHERE pga.snap_id = &bid
AND pga.dbid = &dbid
AND pga.instance_number = &inst_num) mu,
dba_hist_parameter p
WHERE p.snap_id = &bid
AND p.dbid = &dbid
AND p.instance_number = &inst_num
AND p.parameter_name = 'pga_aggregate_target'
AND p.VALUE != '0'
UNION ALL
SELECT 'E' snap,
to_number(p.VALUE) / 1024 / 1024 pgaat,
mu.pat / 1024 / 1024 pat,
mu.pga_alloc / 1024 / 1024 tot_pga_allo,
(mu.pga_used_auto + mu.pga_used_man) / 1024 / 1024 tot_tun_used,
100 * (mu.pga_used_auto + mu.pga_used_man) / pga_alloc pct_tun,
decode(mu.pga_used_auto + mu.pga_used_man,
0,
0,
100 * mu.pga_used_auto / (mu.pga_used_auto + mu.pga_used_man)) pct_auto_tun,
decode(mu.pga_used_auto + mu.pga_used_man,
0,
0,
100 * mu.pga_used_man / (mu.pga_used_auto + mu.pga_used_man)) pct_man_tun,
mu.glob_mem_bnd / 1024 glo_mem_bnd
FROM (SELECT SUM(CASE
WHEN NAME = 'total PGA allocated' THEN
VALUE
ELSE
0
END) pga_alloc,
SUM(CASE
WHEN NAME = 'total PGA used for auto workareas' THEN
VALUE
ELSE
0
END) pga_used_auto,
SUM(CASE
WHEN NAME = 'total PGA used for manual workareas' THEN
VALUE
ELSE
0
END) pga_used_man,
SUM(CASE
WHEN NAME = 'global memory bound' THEN
VALUE
ELSE
0
END) glob_mem_bnd,
SUM(CASE
WHEN NAME = 'aggregate PGA auto target' THEN
VALUE
ELSE
0
END) pat
FROM dba_hist_pgastat pga
WHERE pga.snap_id = &eid
AND pga.dbid = &dbid
AND pga.instance_number = &inst_num) mu,
dba_hist_parameter p
WHERE p.snap_id = &eid
AND p.dbid = &dbid
AND p.instance_number = &inst_num
AND p.parameter_name = 'pga_aggregate_target'
AND p.VALUE != '0'
ORDER BY snap;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PGA Aggr Target Histogram DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 33,739 33,739 0 0
64K 128K 15 15 0 0
128K 256K 6 6 0 0
256K 512K 6 6 0 0
512K 1024K 2,599 2,599 0 0
1M 2M 1,219 1,219 0 0
2M 4M 355 355 0 0
4M 8M 178 178 0 0
8M 16M 191 191 0 0
16M 32M 476 476 0 0
32M 64M 45 45 0 0
64M 128M 18 18 0 0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT CASE
WHEN e.low_optimal_size >= 1024 * 1024 * 1024 * 1024 THEN
lpad(round(e.low_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T',
7)
WHEN e.low_optimal_size >= 1024 * 1024 * 1024 THEN
lpad(round(e.low_optimal_size / 1024 / 1024 / 1024) || 'G', 7)
WHEN e.low_optimal_size >= 1024 * 1024 THEN
lpad(round(e.low_optimal_size / 1024 / 1024) || 'M', 7)
WHEN e.low_optimal_size >= 1024 THEN
lpad(round(e.low_optimal_size / 1024) || 'K', 7)
ELSE
lpad(e.low_optimal_size || 'B', 7)
END low_o,
CASE
WHEN e.high_optimal_size >= 1024 * 1024 * 1024 * 1024 THEN
lpad(round(e.high_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T',
7)
WHEN e.high_optimal_size >= 1024 * 1024 * 1024 THEN
lpad(round(e.high_optimal_size / 1024 / 1024 / 1024) || 'G', 7)
WHEN e.high_optimal_size >= 1024 * 1024 THEN
lpad(round(e.high_optimal_size / 1024 / 1024) || 'M', 7)
WHEN e.high_optimal_size >= 1024 THEN
lpad(round(e.high_optimal_size / 1024) || 'K', 7)
ELSE
e.high_optimal_size || 'B'
END high_o,
e.total_executions - nvl(b.total_executions, 0) tot_e,
e.optimal_executions - nvl(b.optimal_executions, 0) opt_e,
e.onepass_executions - nvl(b.onepass_executions, 0) one_e,
e.multipasses_executions - nvl(b.multipasses_executions, 0) mul_e
FROM dba_hist_sql_workarea_hstgrm e, dba_hist_sql_workarea_hstgrm b
WHERE e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.low_optimal_size(+) = e.low_optimal_size
AND b.high_optimal_size(+) = e.high_optimal_size
AND e.total_executions - nvl(b.total_executions, 0) > 0
ORDER BY e.low_optimal_size;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PGA Memory Advisory DB/Inst: ORADB/oraDB1 End Snap: 67
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
512 0.1 95,647.2 1,640.1 98.0 0
1,024 0.3 95,647.2 1,640.1 98.0 0
2,048 0.5 95,647.2 1,640.1 98.0 0
3,072 0.8 95,647.2 1,640.1 98.0 0
4,096 1.0 95,647.2 0.0 100.0 0
4,915 1.2 95,647.2 0.0 100.0 0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select pga_target_for_estimate/1024/1024 pga_t
, pga_target_factor pga_tf
, bytes_processed/1024/1024 byt_p
, estd_extra_bytes_rw/1024/1024 byt_rw
, estd_pga_cache_hit_percentage epchp
, estd_overalloc_count eoc
from DBA_HIST_PGA_TARGET_ADVICE e
where snap_id = &eid
and dbid = &dbid
and instance_number = &inst_num
order by pga_target_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Process Memory Summary Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
-- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 694.7 341.3 161.2 2.8 4.4 22 159 250
Other 522.5 2.1 4.5 22 22 250
Freeable 161.3 .0 1.0 .2 2 169
SQL 6.1 2.8 .0 .0 0 154 177
PL/SQL 4.8 3.9 .0 .0 0 0 249
E -------- 698.0 349.9 136.8 3.1 5.2 38 159 225
Other 550.1 2.4 5.3 37 37 225
Freeable 136.6 .0 .9 .3 2 147
SQL 7.0 3.2 .0 .1 0 154 155
PL/SQL 4.4 3.4 .0 .0 0 0 224
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***本部分AWR中只有DBA_HIST_PROCESS_MEM_SUMMARY,與stats$process_memory_rollup類似,缺少stats$process_rollup,在AWR中無法實現。
但該部分程式碼實現使用了很多技巧，值得借鑑：
在snap中獲取資料部分：
insert into stats$process_rollup
select *
from (select *
from (select l_snap_id
, p_dbid
, p_instance_number
, nvl(pid, -9) pid
, nvl(serial#, -9) serial#
, decode(pid, null, null, max(spid)) spid
, decode(pid, null, null, max(program)) program
, decode(pid, null, null, max(background)) background
, sum(pga_used_mem) pga_used_mem
, sum(pga_alloc_mem) pga_alloc_mem
, sum(pga_freeable_mem) pga_freeable_mem
, max(pga_alloc_mem) max_pga_alloc_mem
, max(pga_max_mem) max_pga_max_mem
, decode(pid, null, avg(pga_alloc_mem), null) avg_pga_alloc_mem
, decode(pid, null, stddev(pga_alloc_mem), null) stddev_pga_alloc_mem
, decode(pid, null, count(pid), null) num_processes
from v$process
where program != 'PSEUDO'
group by grouping sets ( (pid, serial#), () )
)
where pid = -9
or pga_alloc_mem >= (p_def_proc_mem_th *1024*1024)
order by pga_alloc_mem desc
)
where rownum <= (p_def_num_procs + 1);
-->***透過使用group by grouping sets,同時獲取了單個與彙總資訊,彙總資訊pid=-9.
insert into stats$process_memory_rollup
select *
from (select l_snap_id snap_id -- required for 3824971
, p_dbid dbid
, p_instance_number instance_number
, nvl(pm.pid, -9) pid
, nvl(pm.serial#, -9) serial#
, pm.category
, sum(pm.allocated) allocated
, sum(pm.used) used
, max(pm.allocated) max_allocated
, max(pm.max_allocated) max_max_allocated
, decode(pid, null, avg(pm.allocated), null) avg_allocated
, decode(pid, null, stddev(pm.allocated), null) stddev_allocated
, decode(pid, null ,sum(decode(allocated, 0, 0, 1)), null) non_zero_allocations
from v$process_memory pm
group by grouping sets ( (pm.pid, pm.serial#, pm.category)
, (pm.category) )
) g
where g.pid = -9 -- category summary rows
or (g.pid, g.serial#) in (select pr.pid, pr.serial# -- detail rows
from stats$process_rollup pr
where pr.snap_id = l_snap_id
and pr.dbid = p_dbid
and pr.instance_number = p_instance_number
);
-->***透過使用group by grouping sets,同時獲取了單個與分類彙總資訊,分類彙總資訊pid=-9.
select *
from (select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e
, 1 ord_col
, snap_id snid
, '---------' cat
, pga_alloc_mem/&&btomb tot_alloc_mb
, pga_used_mem/&&btomb tot_used_mb
, pga_freeable_mem/&&btomb tot_free_pga_mb
, avg_pga_alloc_mem/&&btomb avg_alloc_mb
, stddev_pga_alloc_mem/&&btomb stddev_alloc_mb
, max_pga_alloc_mem/&&btomb max_alloc_mb
, max_pga_max_mem/&&btomb max_max_alloc_mb
, num_processes nza
from stats$process_rollup
where snap_id in (:bid, :eid)
and dbid = :dbid
and instance_number = :inst_num
and pid = -9
union all
select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e
, 2 ord_col
, snap_id snid
, category cat
, allocated/&&btomb tot_alloc_mb
, used/&&btomb tot_used_mb
, to_number(null) tot_free_pga_mb
, avg_allocated /&&btomb avg_alloc_mb
, stddev_allocated/&&btomb stddev_alloc_mb
, max_allocated/&&btomb max_alloc_mb
, max_max_allocated/&&btomb max_max_alloc_mb
, non_zero_allocations nza
from stats$process_memory_rollup
where snap_id in (:bid, :eid)
and dbid = :dbid
and instance_number = :inst_num
and pid = -9
)
order by snid, ord_col, tot_alloc_mb desc;
-->***只取pid=-9的彙總資訊,分別為所有程式彙總與分類彙總
--Top Process Memory (by component):
select *
from (select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e
, snap_id snid
, 1 ord_col
, pid pid
, rpad(substr( program, instrb(program,'(') +1
, instrb(program, ')')-1-instrb(program,'(')) || ' '
, 13, '-') cat
, pga_alloc_mem/&&btomb tot_alloc_mb
, pga_used_mem/&&btomb tot_used_mb
, pga_freeable_mem/&&btomb tot_free_pga_mb
, max_pga_alloc_mem/&&btomb max_alloc_mb
, max_pga_max_mem/&&btomb max_max_alloc_mb
, pga_alloc_mem/&&btomb tot_alloc_mb2
from stats$process_rollup
where snap_id in (:bid, :eid)
and dbid = :dbid
and instance_number = :inst_num
and pid != -9
union all
select decode(pmr.snap_id, :bid, 'B', :eid, 'E') b_or_e
, pmr.snap_id snid
, 2 ord_col
, pmr.pid pid
, pmr.category cat
, pmr.allocated/&&btomb tot_alloc_mb
, pmr.used/&&btomb tot_used_mb
, to_number(null) pga_free_mb
, pmr.max_allocated/&&btomb max_alloc_mb
, pmr.max_max_allocated/&&btomb max_max_alloc_mb
, pr.pga_alloc_mem/&&btomb tot_alloc_mb2
from stats$process_memory_rollup pmr
, stats$process_rollup pr
where pmr.snap_id in (:bid, :eid)
and pmr.dbid = :dbid
and pmr.instance_number = :inst_num
and pmr.pid != -9
and pr.snap_id = pmr.snap_id
and pr.dbid = pmr.dbid
and pr.instance_number = pmr.instance_number
and pr.pid = pmr.pid
and pmr.serial# = pmr.serial#
)
order by snid, tot_alloc_mb2 desc, pid, ord_col,tot_alloc_mb desc;
-->***獲取單個程式資訊(pid!= -9),第一部分獲取程式彙總資訊,第二部分獲取程式分類彙總資訊
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Top Process Memory (by component) DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
-- ------ ------------- ------- ------- -------- ---------- ----------
B 20 LGWR -------- 22.0 10.4 .0 22.0 22.0
Other 22.0 22.0 22.0
PL/SQL .0 .0 .0 .0
30 ARC1 -------- 21.9 10.4 .0 21.9 21.9
Other 21.9 21.9 21.9
PL/SQL .0 .0 .0 .0
E 159 ------------ 38.2 17.7 .7 38.2 38.2
Other 37.4 37.4 37.4
Freeable .7 .0 .7
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 .3
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->參考Process Memory Summary Stats部分說明
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Enqueue activity DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
WL-Being Written Redo Log
3 2 1 2 3 1,634.50
US-Undo Segment
3,080 3,080 0 2,941 1 .37
TM-DML
159,593 159,591 0 659 0 .50
TX-Transaction (index contention)
161 161 0 69 0 1.84
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT /*+ ordered */
e.eq_type || '-' || to_char(nvl(l.NAME, ' ')) ||
decode(upper(e.req_reason),
'CONTENTION',
NULL,
'-',
NULL,
' (' || e.req_reason || ')') ety,
e.total_req# - nvl(b.total_req#, 0) reqs,
e.succ_req# - nvl(b.succ_req#, 0) sreq,
e.failed_req# - nvl(b.failed_req#, 0) freq,
e.total_wait# - nvl(b.total_wait#, 0) waits,
(e.cum_wait_time - nvl(b.cum_wait_time, 0)) / 1000 wttm,
decode((e.total_wait# - nvl(b.total_wait#, 0)),
0,
to_number(NULL),
((e.cum_wait_time - nvl(b.cum_wait_time, 0)) /
(e.total_wait# - nvl(b.total_wait#, 0)))) awttm
FROM dba_hist_enqueue_stat e, dba_hist_enqueue_stat b, v$lock_type l
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number(+) = e.instance_number
AND b.eq_type(+) = e.eq_type
AND b.req_reason(+) = e.req_reason
AND e.total_wait# - nvl(b.total_wait#, 0) > 0
AND l.TYPE(+) = e.eq_type
ORDER BY wttm DESC, waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Undo Segment Summary DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
1 5.9 98,588 3,137 10 5760/5760 0/0 0/0/0/0/0/0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT undotsn,
SUM(undoblks) / 1000 undob,
SUM(txncount) txcnt,
MAX(maxquerylen) maxq,
MAX(maxconcurrency) maxc,
round(MIN(tuned_undoretention) / 60, 1) || '/' ||
round(MAX(tuned_undoretention) / 60, 1) mintun,
SUM(ssolderrcnt) || '/' || SUM(nospaceerrcnt) snolno,
SUM(unxpstealcnt) || '/' || SUM(unxpblkrelcnt) || '/' ||
SUM(unxpblkreucnt) || '/' || SUM(expstealcnt) || '/' ||
SUM(expblkrelcnt) || '/' || SUM(expblkreucnt) blkst
FROM dba_hist_undostat
WHERE dbid = &dbid
AND instance_number = &inst_num
AND snap_id >= &bid
AND snap_id <= &eid
GROUP BY undotsn;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Undo Segment Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
15-Dec 10:59 1,205 20,436 3,137 8 5,760 0/0 0/0/0/0/0/0
15-Dec 10:49 1,156 19,366 2,537 9 5,760 0/0 0/0/0/0/0/0
15-Dec 10:39 1,128 19,511 1,937 6 5,760 0/0 0/0/0/0/0/0
15-Dec 10:29 1,133 20,138 1,624 10 5,760 0/0 0/0/0/0/0/0
15-Dec 10:19 1,236 19,137 1,023 7 5,760 0/0 0/0/0/0/0/0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT undotsn, endt, undob, txcnt, maxq, maxc, mintun, snolno, blkst
FROM (SELECT undotsn,
to_char(end_time, 'DD-Mon HH24:MI') endt,
undoblks undob,
txncount txcnt,
maxquerylen maxq,
maxconcurrency maxc,
tuned_undoretention / 60 mintun,
ssolderrcnt || '/' || nospaceerrcnt snolno,
unxpstealcnt || '/' || unxpblkrelcnt || '/' || unxpblkreucnt || '/' ||
expstealcnt || '/' || expblkrelcnt || '/' || expblkreucnt blkst
FROM dba_hist_undostat
WHERE dbid = &dbid
AND instance_number = &inst_num
AND snap_id >= &bid
AND snap_id <= &eid
ORDER BY begin_time DESC)
WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Latch Activity DB/Inst: ORADB/oraDB1 Snaps: 66-67
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 2,042 0.0 0 0
Consistent RBA 98,283 0.0 0.0 0 0
FAL request queue 64 0.0 0 0
FAL subheap alocation 64 0.0 0 0
FIB s.o chain latch 96 0.0 0 0
FOB s.o list latch 986 0.0 0 0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.latch_name latch_name,
e.gets - b.gets gets,
to_number(decode(e.gets,
b.gets,
NULL,
(e.misses - b.misses) * 100 / (e.gets - b.gets))) missed,
to_number(decode(e.misses,
b.misses,
NULL,
(e.sleeps - b.sleeps) / (e.misses - b.misses))) sleeps,
(e.wait_time - b.wait_time) / 1000000 wt,
e.immediate_gets - b.immediate_gets nowai,
to_number(decode(e.immediate_gets,
b.immediate_gets,
NULL,
(e.immediate_misses - b.immediate_misses) * 100 /
(e.immediate_gets - b.immediate_gets))) imiss
FROM dba_hist_latch b, dba_hist_latch e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.dbid = e.dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number = e.instance_number
AND b.latch_name = e.latch_name
AND (e.gets - b.gets + e.immediate_gets - b.immediate_gets) > 0
ORDER BY b.latch_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Latch Sleep breakdown DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
row cache objects 2,851,791 2,001,054 2,985 1,998,090
cache buffers chains 246,877,920 526,867 34,254 494,907
undo global data 2,445,483 24,812 53 24,762
simulator lru latch 7,212,042 14,717 4 14,713
KCL gc element parent latc 16,016,719 2,938 12 2,929
cache buffers lru chain 499,763 667 3 664
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select b.latch_name name
, e.gets - b.gets gets
, e.misses - b.misses misses
, e.sleeps - b.sleeps sleeps
, e.spin_gets - b.spin_gets spin_gets
from dba_hist_latch b
, dba_hist_latch e
where b.snap_id = &bid
and e.snap_id = &eid
and b.dbid = &dbid
and e.dbid = &dbid
and b.dbid = e.dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.instance_number = e.instance_number
and b.latch_name = e.latch_name
and e.sleeps - b.sleeps > 0
order by misses desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Latch Miss Sources DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
KCL gc element parent la kclplz 0 5 2
KCL gc element parent la kclbla 0 4 0
KCL gc element parent la kclzcl 0 4 0
KCL gc element parent la kclnfndnewm 0 4 7
cache buffers chains kcbzwb 0 17,450 24,067
cache buffers chains kcbchg: kslbegin: bufs not 0 16,883 14,443
cache buffers chains kcbzib: finish free bufs 0 8,633 103
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.parent_name parent
, e.where_in_code where_from
, e.nwfail_count - nvl(b.nwfail_count,0) nwmisses
, e.sleep_count - nvl(b.sleep_count,0) sleeps
, e.wtr_slp_count - nvl(b.wtr_slp_count,0) waiter_sleeps
from DBA_HIST_LATCH_MISSES_SUMMARY b
, DBA_HIST_LATCH_MISSES_SUMMARY e
where b.snap_id(+) = &bid
and e.snap_id = &eid
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.dbid(+) = e.dbid
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.instance_number(+) = e.instance_number
and b.parent_name(+) = e.parent_name
and b.where_in_code(+) = e.where_in_code
and e.sleep_count > nvl(b.sleep_count,0)
order by e.parent_name, sleeps desc;
--Parent Latch Statistics
--only latches with sleeps are shown
--ordered by name
SELECT l.latch_name PARENT, lp.gets, lp.misses, lp.sleeps, lp.spin_gets
FROM (SELECT e.instance_number,
e.dbid,
e.snap_id,
e.latch_name,
e.latch_hash,
e.gets - b.gets gets,
e.misses - b.misses misses,
e.sleeps - b.sleeps sleeps,
e.spin_gets - b.spin_gets spin_gets
FROM dba_hist_latch_parent b, dba_hist_latch_parent e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.dbid = e.dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number = e.instance_number
AND b.latch_name = e.latch_name
AND b.latch_hash = e.latch_hash
AND e.sleeps - b.sleeps > 0) lp,
dba_hist_latch l
WHERE l.snap_id = lp.snap_id
AND l.dbid = lp.dbid
AND l.instance_number = lp.instance_number
AND l.latch_name = lp.latch_name
AND l.latch_hash = lp.latch_hash
ORDER BY l.latch_name;
--Latch Children
--only latches with sleeps/gets > 1/100000 are shown
--ordered by name, gets desc
SELECT l.latch_name, lc.child, lc.gets, lc.misses, lc.sleeps, lc.spin_gets
FROM (SELECT /*+ ordered use_hash(b) */
e.instance_number,
e.dbid,
e.snap_id,
e.latch_name,
e.latch_hash,
e.child# child,
e.gets - b.gets gets,
e.misses - b.misses misses,
e.sleeps - b.sleeps sleeps,
e.spin_gets - b.spin_gets spin_gets
FROM dba_hist_latch_children e, dba_hist_latch_children b
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.dbid = &dbid
AND e.dbid = &dbid
AND b.dbid = e.dbid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.instance_number = e.instance_number
AND b.latch_name = e.latch_name
AND b.latch_hash = e.latch_hash
AND b.child# = e.child#
AND e.sleeps - b.sleeps > 0
AND (e.sleeps - b.sleeps) / (e.gets - b.gets) > .00001) lc,
dba_hist_latch l
WHERE l.snap_id = lc.snap_id
AND l.dbid = lc.dbid
AND l.instance_number = lc.instance_number
AND l.latch_name = lc.latch_name
AND l.latch_hash = lc.latch_hash
ORDER BY latch_name, gets DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Mutex Sleep DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> ordered by Wait Time desc
Wait
Mutex Type Location Sleeps Time (s)
------------------ -------------------------------- -------------- ------------
Cursor Pin kksfbc [KKSCHLFSP2] 34,825 340.3
Cursor Pin kkslce [KKSCHLPIN2] 10,112 98.8
Cursor Pin kksfbc [KKSCHLPIN1] 1,024 10.0
Cursor Parent kksfbc [KKSPRTLOC1] 38 0.0
Cursor Pin kksLockDelete [KKSCHLPIN6] 2 0.0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由於不存在v$mutex_sleep、v$mutex_sleep_history相關DBA_HIST_*表，在AWR中無法實現
select e.mutex_type mux
, e.location loc
, e.sleeps - nvl(b.sleeps, 0) sleeps
, (e.wait_time - nvl(b.wait_time, 0))/&ustos wt
from stats$mutex_sleep b
, stats$mutex_sleep e
where b.snap_id(+) = :bid
and e.snap_id = :eid
and e.dbid = :dbid
and b.dbid(+) = e.dbid
and e.instance_number = :inst_num
and b.instance_number(+) = e.instance_number
and b.mutex_type(+) = e.mutex_type
and b.location(+) = e.location
and e.sleeps - nvl(b.sleeps, 0) > 0
order by e.wait_time - nvl(b.wait_time, 0) desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-701152/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於AWR實現STATSPACK報告(8-例項元件二)元件
- 基於AWR實現STATSPACK報告(4-等待事件)事件
- 基於AWR實現STATSPACK報告(5-TOPSQL)SQL
- 基於AWR實現STATSPACK報告(7-TOPSEGMENT)
- 基於AWR實現STATSPACK報告(2-系統效率)
- 基於AWR實現STATSPACK報告(3-RAC統計)
- 基於AWR實現STATSPACK報告(1-系統負載)負載
- AWR報告基礎操作
- oracle rac 單個例項不能生成awr報告的問題Oracle
- statspack 報告分析
- shell指令碼實現自動生成awr報告指令碼
- 詳解statspack 報告
- 關於類似於awr的效能分析報告
- statspack報告分析摘錄
- Statspack分析報告說明
- Statspack分析報告詳解
- 對於AWR報告的幾個片段分析。
- Oracle Statspack報告中各項指標含義詳解！Oracle指標
- Oracle Statspack報告中各項指標含義詳解Oracle指標
- Statspack報告分析—第三部分：Instance Efficiency Percentages例項效率統計
- 使用Tensorflow實現的基於LSTM的預測例項
- Oracle生成awr報告Oracle
- AWR解析報告分析
- mysql-awr報告MySql
- Oracle 生成awr報告Oracle
- oracle效能awr報告Oracle
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- Statspack分析報告詳解 (zt)
- Statspack分析報告詳解(1)
- Statspack分析報告詳解(2)
- Statspack分析報告詳解(3)
- Statspack分析報告詳解(4)
- Statspack分析報告詳解(轉)
- Oracle Statspack報告中各項指標含義詳解！(轉)Oracle指標
- （轉）Oracle Statspack報告中各項指標含義詳解！Oracle指標
- 手工生成AWR分析報告
- Oracle AWR報告大綱Oracle
- oracle 產生awr 報告Oracle