基於AWR實現STATSPACK報告(8-例項元件二)
Dictionary Cache Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 52 0.0 0 0 1
dc_global_oids 652 1.1 0 0 16
dc_histogram_data 86,829 0.4 0 0 3,231
dc_histogram_defs 34,125 2.6 0 21 1,747
dc_object_grants 156 6.4 0 0 107
dc_object_ids 41,387 0.4 0 2 535
dc_objects 4,475 0.8 0 4 668
....
-------------------------------------------------------------
GES GES GES
Cache Requests Conflicts Releases
------------------------- ------------ ------------ ------------
dc_global_oids 7 0 20
dc_histogram_defs 862 0 5,399
dc_object_ids 177 0 811
dc_objects 32 0 699
dc_segments 21 1 426
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT lower(b.parameter) param,
e.gets - b.gets gets,
to_number(decode(e.gets,
b.gets,
NULL,
(e.getmisses - b.getmisses) * 100 /
(e.gets - b.gets))) getm,
e.scans - b.scans scans,
to_number(decode(e.scans,
b.scans,
NULL,
(e.scanmisses - b.scanmisses) * 100 /
(e.scans - b.scans))) scanm,
e.modifications - b.modifications mods,
e.usage usage
FROM dba_hist_rowcache_summary b, dba_hist_rowcache_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.parameter = e.parameter
AND e.gets - b.gets > 0
ORDER BY param;
--dlm_requests,RAC related:
select lower(b.parameter) param
, e.dlm_requests - b.dlm_requests dreq
, e.dlm_conflicts - b.dlm_conflicts dcon
, e.dlm_releases - b.dlm_releases drel
from dba_hist_rowcache_summary b
, dba_hist_rowcache_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.parameter = e.parameter
and e.dlm_requests - b.dlm_requests > 0
order by param;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Library Cache Activity DB/Inst: ORADB/oraDB1 Snaps: 66-67
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
------------------------------ ------------ ------ -------------- ------ ---------- --------
BODY 186 0.5 2,079 0.7 14 0
CLUSTER 78 0.0 176 1.1 2 0
SQL AREA 791 53.4 2,013,353 0.4 2,172 1,137
TABLE/PROCEDURE 2,996 0.6 86,094 1.5 1,203 0
TRIGGER 1 100.0 2 50.0 0 0
GES Lock GES Pin GES Pin GES Inval GES Invali-
Namespace Requests Requests Releases Requests dations
------------------------------ ------------ ------------ ------------ ----------- -----------
BODY 1 1 13 1 0
CLUSTER 176 0 1 0 0
SQL AREA 0 0 0 0 0
TABLE/PROCEDURE 9,221 14 430 9 0
TRIGGER 0 1 0 1 0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.namespace
, e.gets - b.gets gets
, to_number(decode(e.gets,b.gets,null,
100 - (e.gethits - b.gethits) * 100/(e.gets - b.gets))) getm
, e.pins - b.pins pins
, to_number(decode(e.pins,b.pins,null,
100 - (e.pinhits - b.pinhits) * 100/(e.pins - b.pins))) pinm
, e.reloads - b.reloads reloads
, e.invalidations - b.invalidations inv
from DBA_HIST_LIBRARYCACHE b
, DBA_HIST_LIBRARYCACHE 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.namespace = e.namespace
and e.gets - b.gets > 0;
--dlm_lock_requests,RAC related:
SELECT e.namespace,
e.dlm_lock_requests - b.dlm_lock_requests dlreq,
e.dlm_pin_requests - b.dlm_pin_requests dpreq,
e.dlm_pin_releases - b.dlm_pin_releases dprel,
e.dlm_invalidation_requests - b.dlm_invalidation_requests direq,
e.dlm_invalidations - b.dlm_invalidations dinv
FROM dba_hist_librarycache b, dba_hist_librarycache 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.namespace = e.namespace
AND e.gets - b.gets > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Global Enqueue Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
acks for commit broadcast(actual) 466,128 154.4 4.7
acks for commit broadcast(logical 492,587 163.2 4.9
broadcast msgs on commit(actual) 97,764 32.4 1.0
broadcast msgs on commit(logical) 98,266 32.6 1.0
broadcast msgs on commit(wasted) 4,694 1.6 0.0
dynamically allocated gcs resourc 0 0.0 0.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.NAME st,
e.VALUE - b.VALUE dif,
round(e.VALUE - b.VALUE) / &ela ps,
round(e.VALUE - b.VALUE) / &tran pt
FROM dba_hist_dlm_misc b, dba_hist_dlm_misc e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.dbid = &dbid
AND e.dbid = &dbid
AND e.statistic# = b.statistic#
ORDER BY b.NAME;
其中：
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;
tran-->ucom+urol
SELECT sum(e.VALUE) - sum(b.VALUE)
FROM dba_hist_sysstat b, dba_hist_sysstat 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
AND b.stat_name = e.stat_name
AND b.stat_name in('user commits','user rollbacks');
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Global CR Served Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
Statistic Total
------------------------------ ------------------
CR Block Requests 105,752
CURRENT Block Requests 1,057
Data Block Requests 105,752
Undo Block Requests 17
TX Block Requests 366
Current Results 105,862
Private results 11
Zero Results 938
Disk Read Results 0
Fail Results 0
....
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'Statistic Total',
'------------------------------' nl,
'------------------',
'CR Block Requests ' nl,
e.cr_requests - b.cr_requests val,
'CURRENT Block Requests ' nl,
e.current_requests - b.current_requests val,
'Data Block Requests ' nl,
e.data_requests - b.data_requests val,
'Undo Block Requests ' nl,
e.undo_requests - b.undo_requests val,
'TX Block Requests ' nl,
e.tx_requests - b.tx_requests val,
'Current Results ' nl,
e.current_results - b.current_results val,
'Private results ' nl,
e.private_results - b.private_results val,
'Zero Results ' nl,
e.zero_results - b.zero_results val,
'Disk Read Results ' nl,
e.disk_read_results - b.disk_read_results val,
'Fail Results ' nl,
e.fail_results - b.fail_results val,
'Fairness Down Converts ' nl,
e.fairness_down_converts - b.fairness_down_converts val,
'Fairness Clears ' nl,
e.fairness_clears - b.fairness_clears val,
'Free GC Elements ' nl,
e.free_gc_elements - b.free_gc_elements val,
'Flushes ' nl,
e.flushes - b.flushes val,
'Flushes Queued ' nl,
e.flushes_queued - b.flushes_queued val,
'Flush Queue Full ' nl,
e.flush_queue_full - b.flush_queue_full val,
'Flush Max Time (us) ' nl,
e.flush_max_time - b.flush_max_time val,
'Light Works ' nl,
e.light_works - b.light_works val,
'Errors ' nl,
e.errors - b.errors val
FROM dba_hist_cr_block_server b, dba_hist_cr_block_server e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.dbid = &dbid
AND e.dbid = &dbid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Global CURRENT Served Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Pins = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block Served Operations
-> Writes = CURRENT Block Fusion Write Operations
Statistic Total % <1ms % <10ms % <100ms % <1s % <10s
----------- ----------- -------- -------- -------- -------- --------
Pins 1,228,830
100.00
0.00
0.00
0.00
0.00
Flushes 88
86.36
12.50
0.00
1.14
0.00
Writes 18,000
24.14
63.16
10.36
0.83
1.52
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'Statistic ',
' Total',
' % <1ms',
' % <10ms',
'% <100ms',
' % <1s',
' % <10s',
'----------- ----------- -------- -------- -------- -------- --------',
'Pins ' stat,
pins tot,
lpad(to_char(decode(pins, 0, 0, 100 * pin1 / pins), '990.99'),
8,
' '),
lpad(to_char(decode(pins, 0, 0, 100 * pin10 / pins), '990.99'),
8,
' '),
lpad(to_char(decode(pins, 0, 0, 100 * pin100 / pins), '990.99'),
8,
' '),
lpad(to_char(decode(pins, 0, 0, 100 * pin1000 / pins), '990.99'),
8,
' '),
lpad(to_char(decode(pins, 0, 0, 100 * pin10000 / pins), '990.99'),
8,
' '),
'Flushes ' stat,
flushes tot,
lpad(to_char(decode(flushes, 0, 0, 100 * flush1 / flushes), '990.99'),
8,
' '),
lpad(to_char(decode(flushes, 0, 0, 100 * flush10 / flushes),
'990.99'),
8,
' '),
lpad(to_char(decode(flushes, 0, 0, 100 * flush100 / flushes),
'990.99'),
8,
' '),
lpad(to_char(decode(flushes, 0, 0, 100 * flush1000 / flushes),
'990.99'),
8,
' '),
lpad(to_char(decode(flushes, 0, 0, 100 * flush10000 / flushes),
'990.99'),
8,
' '),
'Writes ' stat,
writes tot,
lpad(to_char(decode(writes, 0, 0, 100 * write1 / writes), '990.99'),
8,
' '),
lpad(to_char(decode(writes, 0, 0, 100 * write10 / writes), '990.99'),
8,
' '),
lpad(to_char(decode(writes, 0, 0, 100 * write100 / writes), '990.99'),
8,
' '),
lpad(to_char(decode(writes, 0, 0, 100 * write1000 / writes),
'990.99'),
8,
' '),
lpad(to_char(decode(writes, 0, 0, 100 * write10000 / writes),
'990.99'),
8,
' ')
FROM (SELECT (e.pin1 + e.pin10 + e.pin100 + e.pin1000 + e.pin10000 -
(b.pin1 + b.pin10 + b.pin100 + b.pin1000 + b.pin10000)) pins,
e.pin1 - b.pin1 pin1,
e.pin10 - b.pin10 pin10,
e.pin100 - b.pin100 pin100,
e.pin1000 - b.pin1000 pin1000,
e.pin10000 - b.pin10000 pin10000,
(e.flush1 + e.flush10 + e.flush100 + e.flush1000 +
e.flush10000 - (b.flush1 + b.flush10 + b.flush100 +
b.flush1000 + b.flush10000)) flushes,
e.flush1 - b.flush1 flush1,
e.flush10 - b.flush10 flush10,
e.flush100 - b.flush100 flush100,
e.flush1000 - b.flush1000 flush1000,
e.flush10000 - b.flush10000 flush10000,
(e.write1 + e.write10 + e.write100 + e.write1000 +
e.write10000 - (b.write1 + b.write10 + b.write100 +
b.write1000 + b.write10000)) writes,
e.write1 - b.write1 write1,
e.write10 - b.write10 write10,
e.write100 - b.write100 write100,
e.write1000 - b.write1000 write1000,
e.write10000 - b.write10000 write10000
FROM dba_hist_current_block_server b,
dba_hist_current_block_server e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.dbid = &dbid
AND e.dbid = &dbid);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Global Cache Transfer Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays
Busy (Busy) - Block Transfer impacted by Remote Contention
Congested (Congst) - Block Transfer impacted by Remote System Load
-> ordered by CR + Current Blocks Received desc
-------------- CR ------------- ----------- Current -----------
Inst Block Blocks % % % Blocks % % %
No Class Received Immed Busy Congst Received Immed Busy Congst
---- -------- ----------- ------ ------ ------ ----------- ------ ------ ------
2 data blo 189,236 82.3 17.7 .0 1,682,755 100.0 .0 .0
2 undo blo 12,111 99.3 .7 .0 0
2 others 372 100.0 .0 .0 554 100.0 .0 .0
2 undo hea 693 98.4 1.6 .0 81 97.5 2.5 .0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
with instance_cache_transfer as (
select snap_id
, instance
, case when class in ('data block', 'undo header', 'undo block')
then class
else 'others' end as class
, sum(cr_block) cr_block
, sum(cr_busy) cr_busy
, sum(cr_congested) cr_congested
, sum(current_block) current_block
, sum(current_busy) current_busy
, sum(current_congested) current_congested
, sum(cr_block) + sum(cr_busy) + sum(cr_congested) totcr
, sum(current_block) + sum(current_busy) + sum(current_congested) totcu
from DBA_HIST_INST_CACHE_TRANSFER
where instance_number = &inst_num
and dbid = &dbid
group by snap_id
, instance
, case when class in ('data block', 'undo header', 'undo block')
then class
else 'others' end)
select e.instance inst
, e.class class
, e.totcr - nvl(b.totcr , 0) totcr
, decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkimm
, decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy -nvl(b.cr_busy, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkbus
, decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt
, e.totcu - nvl(b.totcu , 0) totcu
, decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkimm
, decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy -nvl(b.current_busy, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkbus
, decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt
from (select * from instance_cache_transfer
where snap_id = &bid) b
, (select * from instance_cache_transfer
where snap_id = &eid) e
where b.class(+) = e.class
and b.instance(+) = e.instance
and e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0) > 0
order by totcr + totcu desc;
with class_cache_transfer as (
select snap_id
, case when class in ('data block', 'undo header', 'undo block')
then class
else 'others' end as class
, sum(cr_block) cr_block
, sum(cr_busy) cr_busy
, sum(cr_congested) cr_congested
, sum(current_block) current_block
, sum(current_busy) current_busy
, sum(current_congested) current_congested
, sum(cr_block) + sum(cr_busy) + sum(cr_congested) totcr
, sum(current_block) + sum(current_busy) + sum(current_congested) totcu
from DBA_HIST_INST_CACHE_TRANSFER
where instance_number = &inst_num
and dbid = &dbid
group by snap_id
, case when class in ('data block', 'undo header', 'undo block')
then class
else 'others' end)
select e.class
, e.totcr - nvl(b.totcr , 0) totcr
, decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkimm
, decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy-nvl(b.cr_busy, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkbus
, decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt
, e.totcu - nvl(b.totcu , 0) totcu
, decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkimm
, decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy-nvl(b.current_busy, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkbus
, decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt
from (select * from class_cache_transfer
where snap_id = &bid) b
, (select * from class_cache_transfer
where snap_id = &eid) e
where b.class(+) = e.class
and (e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0)) > 0
order by totcr + totcu desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dynamic Remastering Stats DB/Inst: ORADB/oraDB1 Snaps: 66-67
Remaster Operations: 4 Remaster Time(s): 6.2
Remastered Objects: 4 Quiesce Time(s): 0.2
Affinity Objects (Beg): 15 Freeze Time(s): 0.0
Affinity Objects (End): 15 Cleanup Time(s): 0.2
Replayed Locks Sent: 85,656 Replay Time(s): 1.2
Replayed Locks Recvd: 112,604 Fixwrite Time(s): 0.2
Resources Cleaned: 0 Sync Time(s): 2.7
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由於不存在相關表,AWR中無法實現,摘錄STATSPACK實現方法:
create or replace view STATS$V_$DYNAMIC_REM_STATS as
select drms remaster_ops
, avg_drm_time*drms remaster_time
, objects_per_drm*drms remastered_objects
, quisce_t*drms quiesce_time
, frz_t*drms freeze_time
, cleanup_t*drms cleanup_time
, replay_t*drms replay_time
, fixwrite_t*drms fixwrite_time
, sync_t*drms sync_time
, res_cleaned*drms resources_cleaned
, replay_s*drms replayed_locks_sent
, replay_r*drms replayed_locks_received
, my_objects current_objects
from x$kjdrmafnstats;
grant select on STATS$V_$DYNAMIC_REM_STATS to PERFSTAT;
create synonym PERFSTAT.V$DYNAMIC_REMASTER_STATS for STATS$V_$DYNAMIC_REM_STATS;
select ' Remaster Operations:' ch25n
, e.remaster_ops - nvl(b.remaster_ops, 0) numX
, ' Remaster Time(s):' ch20
, (e.remaster_time - nvl(b.remaster_time, 0))/&cstos numXX
, ' Remastered Objects:' ch25n
, e.remastered_objects - nvl(b.remastered_objects, 0) numX
, ' Quiesce Time(s):' ch20
, (e.quiesce_time - nvl(b.quiesce_time, 0))/&cstos numXX
, ' Affinity Objects (Beg):' ch25n
, b.current_objects numX
, ' Freeze Time(s):' ch20
, (e.freeze_time - nvl(b.freeze_time, 0))/&cstos numXX
, ' Affinity Objects (End):' ch25n
, e.current_objects numX
, ' Cleanup Time(s):' ch20
, (e.cleanup_time - nvl(b.cleanup_time, 0))/&cstos numXX
, ' Replayed Locks Sent:' ch25n
, e.replayed_locks_sent - nvl(b.replayed_locks_sent, 0) numX
, ' Replay Time(s):' ch20
, (e.replay_time - nvl(b.replay_time, 0))/&cstos numXX
, ' Replayed Locks Recvd:' ch25n
, e.replayed_locks_received - nvl(b.replayed_locks_received, 0) numX
, ' Fixwrite Time(s):' ch20
, (e.fixwrite_time - nvl(b.fixwrite_time, 0))/&cstos numXX
, ' Resources Cleaned:' ch25n
, e.resources_cleaned - nvl(b.resources_cleaned, 0) numX
, ' Sync Time(s):' ch20
, (e.sync_time - nvl(b.sync_time, 0))/&cstos numXX
from stats$dynamic_remaster_stats b
, stats$dynamic_remaster_stats 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 e.remaster_ops - nvl(b.remaster_ops, 0) > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
STREAM
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
略
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Rule Sets DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- -- ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select owner||'.'||name rule_name
, restart
, decode( elapsed_time, 0, 0
, evaluations/elapsed_time * 100) eval_rate
, reloads/&ela reload_rate
, sql_free_evaluations
/ (sql_free_evaluations + sql_executions + .0000001)
* 100 pct_sql_free
, sql_executions
/ (sql_free_evaluations + sql_executions + .0000001 )
* 100 pct_sql
from (select e.owner
, e.name
, decode( e.startup_time, b.startup_time, null, '*') restart
, e.cpu_time - nvl(b.cpu_time,0) cpu_time
, e.elapsed_time - nvl(b.elapsed_time,0) elapsed_time
, e.evaluations - nvl(b.evaluations,0) evaluations
, e.sql_free_evaluations - nvl(b.sql_free_evaluations,0)
sql_free_evaluations
, e.sql_executions - nvl(b.sql_executions,0) sql_executions
, e.reloads - nvl(b.reloads,0) reloads
from DBA_HIST_RULE_SET b
, DBA_HIST_RULE_SET 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.owner (+)= e.owner
and b.name (+)= e.name
and b.startup_time (+)= e.startup_time
order by e.evaluations desc
)
where rownum <= 10;
其中：
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;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Streams Pool Advisory DB/Inst: ORADB/oraDB1 End Snap: 67
Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill
Size (M) Size Factor Count Time (s) Count Time (s)
------------ ------------ --------- --------- ----------- -----------
.0 1.0 0 0 0 0
.0 2.0 0 0 0 0
.0 3.0 0 0 0 0
.0 4.0 0 0 0 0
.0 5.0 0 0 0 0
.0 6.0 0 0 0 0
.0 7.0 0 0 0 0
.0 8.0 0 0 0 0
.0 9.0 0 0 0 0
.0 10.0 0 0 0 0
.0 11.0 0 0 0 0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select size_for_estimate/1024/1024 spsfe
, size_factor spsf
, estd_spill_count esc
, estd_spill_time est
, estd_unspill_count eusc
, estd_unspill_time eust
from DBA_HIST_STREAMS_POOL_ADVICE
where snap_id = &eid
and dbid = &dbid
and instance_number = &inst_num
order by size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Shared Pool Advisory DB/Inst: ORADB/oraDB1 End Snap: 67
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
384 .6 71 4,254 ####### 1.0 250 1.0 5,412,042
448 .7 133 6,608 ####### 1.0 246 1.0 5,412,585
512 .8 170 8,948 ####### 1.0 242 1.0 5,413,037
576 .9 170 8,948 ####### 1.0 240 1.0 5,413,311
640 1.0 170 8,948 ####### 1.0 239 1.0 5,413,467
704 1.1 170 8,948 ####### 1.0 239 1.0 5,413,549
768 1.2 170 8,948 ####### 1.0 239 1.0 5,413,589
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT shared_pool_size_for_estimate spsfe,
shared_pool_size_factor spsf,
estd_lc_size elcs,
estd_lc_memory_objects elcmo,
estd_lc_time_saved elcts,
estd_lc_time_saved_factor elctsf,
estd_lc_load_time elclt,
estd_lc_load_time_factor elcltf,
estd_lc_memory_object_hits elcmoh
FROM dba_hist_shared_pool_advice
WHERE snap_id = &eid
AND dbid = &dbid
AND instance_number = &inst_num
ORDER BY shared_pool_size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Java Pool Advisory
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT java_pool_size_for_estimate jpsfe,
java_pool_size_factor jpsf,
estd_lc_size elcs,
estd_lc_memory_objects elcmo,
estd_lc_time_saved elcts,
estd_lc_time_saved_factor elctsf,
estd_lc_load_time elclt,
estd_lc_load_time_factor elcltf,
estd_lc_memory_object_hits elcmoh
FROM dba_hist_java_pool_advice
WHERE snap_id = &eid
AND dbid = &dbid
AND instance_number = &inst_num
AND estd_lc_memory_objects > 0
ORDER BY java_pool_size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Cache Size Changes DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Not all cache size changes may be captured. Only cache changes which are
evident at snapshot time are shown
Prior New Difference
Snap Id Cache Size (MB) Size (MB) (MB)
--------- ------------ --------- --------- ----------
67 Shared Pool 704 608 -96
Buffer Cache 3,296 3,392 96
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT snap_id,
decode(parameter_name,
'__db_cache_size',
'Buffer Cache',
'__shared_pool_size',
'Shared Pool',
'__large_pool_size',
'Large Pool',
'__java_pool_size',
'Java Pool',
'__streams_pool_size',
'Streams Pool') cache,
prev_value,
VALUE,
(VALUE - prev_value) diff
FROM (SELECT snap_id,
parameter_name,
to_number(VALUE) / 1024 / 1024 VALUE,
to_number((lag(VALUE, 1, NULL)
over(ORDER BY parameter_name, snap_id))) / 1024 / 1024 prev_value,
(lag(parameter_name, 1, NULL)
over(ORDER BY parameter_name, snap_id)) prev_name
FROM dba_hist_parameter
WHERE snap_id BETWEEN &bid AND &eid
AND dbid = &dbid
AND instance_number = &inst_num
AND parameter_name IN
('__shared_pool_size', '__db_cache_size', '__large_pool_size',
'__java_pool_size', '__streams_pool_size'))
WHERE VALUE != prev_value
AND parameter_name = prev_name
ORDER BY snap_id, diff;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SGA Target Advisory DB/Inst: ORADB/oraDB1 End Snap: 67
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
1,024 .3 85,148 1.2 43,738,481
2,048 .5 70,017 1.0 16,214,704
3,072 .8 69,036 1.0 14,428,488
4,096 1.0 68,590 1.0 13,604,081
5,120 1.3 68,419 1.0 13,304,791
6,144 1.5 67,734 1.0 12,055,937
7,168 1.8 66,917 1.0 10,573,092
8,192 2.0 66,918 1.0 10,573,092
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select sga_size sgatsfe
, sga_size_factor sgasf
, estd_db_time edbts
-- , estd_db_time_factor edbtf
, estd_physical_reads epr
from DBA_HIST_SGA_TARGET_ADVICE
where snap_id = &eid
and dbid = &dbid
and instance_number = &inst_num
order by sga_size;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SGA Memory Summary DB/Inst: ORADB/oraDB1 Snaps: 66-67
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 3,456,106,496 3,556,769,792
Fixed Size 2,078,328
Redo Buffers 14,696,448
Variable Size 822,086,024 721,422,728
-------------------- --------------------
sum 4,294,967,296 4,278,192,520
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.NAME,
b.VALUE bval,
decode(b.VALUE, e.VALUE, to_number(NULL), e.VALUE) eval
FROM dba_hist_sga b, dba_hist_sga e
WHERE e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND b.snap_id = &bid
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.NAME = e.NAME
ORDER BY NAME;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SGA breakdown difference DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large PX msg pool 1.0 1.0 0.00
large free memory 47.0 47.0 0.00
shared ASH buffers 50.0 50.0 0.00
shared CCursor 13.1 10.5 -19.68
shared FileOpenBlock 8.7 8.7 0.00
shared KGH: NO ACCESS 161.9 ########
shared KGLS heap 9.0 -100.00
shared KQR L PO 9.9 -100.00
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT *
FROM (SELECT nvl(e.pool, b.pool) pool,
nvl(e.NAME, b.NAME) NAME,
b.bytes / 1024 / 1024 snap1,
e.bytes / 1024 / 1024 snap2,
100 * (nvl(e.bytes, 0) - nvl(b.bytes, 0)) / nvl(b.bytes, 1) diff
FROM (SELECT *
FROM dba_hist_sgastat
WHERE snap_id = &bid
AND dbid = &dbid
AND instance_number = &inst_num) b
FULL OUTER JOIN (SELECT *
FROM dba_hist_sgastat
WHERE snap_id = &eid
AND dbid = &dbid
AND instance_number = &inst_num) e ON b.NAME =
e.NAME
AND nvl(b.pool,
'a') =
nvl(e.pool,
'a')
ORDER BY nvl(e.bytes, b.bytes))
WHERE pool IS NULL
OR NAME = 'free memory'
OR rownum <= 10
ORDER BY pool, NAME;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL Memory Statistics DB/Inst: ORADB/oraDB1 Snaps: 66-67
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 29.47 10.35 -184.74
Cursor to Parent ratio: 1.19 1.38 14.39
Total Cursors: 2,913 2,302 -26.54
Total Parents: 2,458 1,663 -47.81
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由於DBA_HIST_SQL_SUMMARY缺少total_cursors,不能代替stats$sql_statistics,AWR中無法實現
Avg Cursor Size (KB), Cursor to Parent ratio,Total Cursors,這裡給出STATSPACK實現思路。注意，
total_cursors使用sum(v$sqlstats.version_count),sum(v$sql.version_count)實現。
--資料採集方法1:new method
create or replace view STATS$V_$SQLSTATS_SUMMARY as
select sql_id
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(executions) executions
, sum(version_count) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, sum(sharable_mem) sharable_mem
from v$sqlstats
group by sql_id;
create or replace public synonym STATS$V$SQLSTATS_SUMMARY for STATS$V_$SQLSTATS_SUMMARY;
insert into stats$sql_statistics
( snap_id
, dbid
, instance_number
, total_sql
, total_sql_mem
, single_use_sql
, single_use_sql_mem
, total_cursors
)
select l_snap_id
, p_dbid
, p_instance_number
, count(1)
, sum(sharable_mem)
, sum(decode(executions, 1, 1, 0))
, sum(decode(executions, 1, sharable_mem, 0))
, sum(version_count)
from stats$v$sqlstats_summary
where sharable_mem > 0;
--資料採集方法2:old method
create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, max(sql_id) sql_id
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(px_servers_executions) px_servers_executions
, sum(end_of_fetch_count) end_of_fetch_count
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(direct_writes) direct_writes
, sum(buffer_gets) buffer_gets
, sum(application_wait_time) application_wait_time
, sum(concurrency_wait_time) concurrency_wait_time
, sum(cluster_wait_time) cluster_wait_time
, sum(user_io_wait_time) user_io_wait_time
, sum(plsql_exec_time) plsql_exec_time
, sum(java_exec_time) java_exec_time
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, old_hash_value old_hash_value
, max(hash_value) hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
, max(sql_profile) sql_profile
, max(program_id) program_id
, max(program_line#) program_line#
, max(exact_matching_signature) exact_matching_signature
, max(force_matching_signature) force_matching_signature
, max(last_active_time) last_active_time
from v$sql
group by old_hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;
insert into stats$sql_statistics
( snap_id
, dbid
, instance_number
, total_sql
, total_sql_mem
, single_use_sql
, single_use_sql_mem
, total_cursors
)
select l_snap_id
, p_dbid
, p_instance_number
, count(1)
, sum(sharable_mem)
, sum(decode(executions, 1, 1, 0))
, sum(decode(executions, 1, sharable_mem, 0))
, sum(version_count)
from stats$v$sqlxs
where is_obsolete = 'N';
--Shared Pool Statistics
with v as(
SELECT nvl(b.total_cursors, 0) b_total_cursors,
nvl(e.total_cursors, 0) e_total_cursors,
nvl(b.total_sql, 0) b_total_sql,
nvl(e.total_sql, 0) e_total_sql,
nvl(b.total_sql_mem, 0) b_total_sql_mem,
nvl(e.total_sql_mem, 0) e_total_sql_mem
FROM stats$sql_statistics b, stats$sql_statistics e
WHERE b.snap_id = &bid
AND e.snap_id = &eid
AND b.instance_number = &inst_num
AND e.instance_number = &inst_num
AND b.dbid = &dbid
AND e.dbid = &dbid)
SELECT ' Begin End % Diff' ch78n,
' -------------- -------------- --------------' ch78n,
' Avg Cursor Size (KB): ' ch25n,
v.b_total_sql_mem / 1024 / v.b_total_cursors num8c_2,
v.e_total_sql_mem / 1024 / v.e_total_cursors num8c_2,
100 * ((v.e_total_sql_mem / 1024 / v.e_total_cursors) -
(v.b_total_sql_mem / 1024 / v.b_total_cursors)) /
(v.e_total_sql_mem / 1024 / v.e_total_cursors) num8c_2,
' Cursor to Parent ratio: ' ch25n,
v.b_total_cursors / v.b_total_sql num8c_2,
v.e_total_cursors / v.e_total_sql num8c_2,
100 * ((v.e_total_cursors / v.e_total_sql) -
(v.b_total_cursors / v.b_total_sql)) /
(v.e_total_cursors / v.e_total_sql) num8c_2,
' Total Cursors: ' ch25n,
v.b_total_cursors num10c,
v.e_total_cursors num10c,
100 * ((v.e_total_cursors) - (v.b_total_cursors)) /
(v.e_total_cursors) num8c_2,
' Total Parents: ' ch25n,
v.b_total_sql num10c,
v.e_total_sql num10c,
100 * ((v.e_total_sql) - (v.b_total_sql)) / (v.e_total_sql) num8c_2
FROM sys.dual
WHERE v.b_total_cursors > 0
AND v.e_total_cursors > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Resource Limit Stats
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT resource_name rname,
current_utilization curu,
max_utilization maxu,
initial_allocation inita,
limit_value lim
FROM dba_hist_resource_limit
WHERE snap_id = &eid
AND dbid = &dbid
AND instance_number = &inst_num
AND (nvl(current_utilization, 0) / limit_value > .8 OR
nvl(max_utilization, 0) / limit_value > .8)
ORDER BY rname;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
init.ora Parameters DB/Inst: ORADB/oraDB1 Snaps: 66-67
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /oracle/app/oracle/admin/oraDB/ad
background_dump_dest /oracle/app/oracle/admin/oraDB/bd
cluster_database TRUE
cluster_database_instances 2
compatible 10.2.0.3.0
control_files /dev/rPO_CTL1, /dev/rPO_CTL2, /de
core_dump_dest /oracle/app/oracle/admin/oraDB/cd
db_block_size 8192
db_domain
db_file_multiblock_read_count 16
db_name oraDB
instance_number 1
job_queue_processes 10
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.parameter_name,
b.VALUE bval,
decode(b.VALUE, e.VALUE, ' ', e.VALUE) eval
FROM dba_hist_parameter b, dba_hist_parameter 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.parameter_name(+) = e.parameter_name
AND translate(e.parameter_name, '_', '#') NOT LIKE '##%'
AND (nvl(b.isdefault, 'X') = 'FALSE' OR
nvl(b.ismodified, 'X') != 'FALSE' OR e.ismodified != 'FALSE' OR
nvl(e.VALUE, 0) != nvl(b.VALUE, 0))
ORDER BY e.parameter_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End of Report ( sp_66_67.lst )
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-701154/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於AWR實現STATSPACK報告(6-例項元件)元件
- 基於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的預測例項
- AWR報告分析之二：ges inquiry response 過高UI
- 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報告分析之二：ges inquiry response 過高-eygleUI
- 手工生成AWR分析報告