Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于DataGuard SDU

2135 积分	502 好友	184 主题

发消息

关于DataGuard SDU

1^#

发表于 2012-3-12 21:31:28 | 查看: 6792| 回复: 2

DataGuard Primary AWR:

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
DBNAME 52778448 DBNAME 1 10.2.0.4.0 NO US1P-ISTORDB
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2567 08-Mar-12 05:00:51 628 2.0
End Snap: 2569 08-Mar-12 09:00:01 625 1.9
Elapsed: 239.17 (mins)
DB Time: 125.44 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 4,880M 4,880M Std Block Size: 8K
Shared Pool Size: 1,200M 1,200M Log Buffer: 21,120K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 71,604.87 767.91
Logical reads: 1,811.76 19.43
Block changes: 502.93 5.39
Physical reads: 0.03 0.00
Physical writes: 8.17 0.09
User calls: 389.16 4.17
Parses: 161.49 1.73
Hard parses: 0.01 0.00
Sorts: 3.31 0.04
Logons: 0.07 0.00
Executes: 161.72 1.73
Transactions: 93.25
% Blocks changed per Read: 27.76 Recursive Call %: 27.71
Rollback per transaction %: 0.00 Rows per Sort: 47.51
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.82 Redo NoWait %: 99.98
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.12 Soft Parse %: 100.00
Execute to Parse %: 0.14 Latch Hit %: 99.71
Parse CPU to Parse Elapsd %: 86.44 % Non-Parse CPU: 99.45
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 44.35 44.99
% SQL with executions>1: 98.21 97.75
% Memory for SQL w/exec>1: 93.28 91.78
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 1,342,844 6,025 4 80.1 Commit
CPU time 5,347 71.0
LGWR wait on LNS 861,496 1,416 2 18.8 Network
LGWR-LNS wait on channel 1,230,568 1,405 1 18.7 Other
LNS wait on SENDREQ 861,429 1,335 2 17.7 Network
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Commit 1,342,844 .0 6,025 4 1.0
Network 6,081,149 .0 2,760 0 4.5
Other 1,238,515 .4 1,406 1 0.9
System I/O 1,028,462 .0 1,195 1 0.8
Application 190 90.5 506 2664 0.0
Concurrency 75,849 .1 234 3 0.1
Configuration 384 .0 56 145 0.0
User I/O 952 .0 2 3 0.0
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file sync 1,342,844 .0 6,025 4 1.0
LGWR wait on LNS 861,496 .0 1,416 2 0.6
LGWR-LNS wait on channel 1,230,568 .4 1,405 1 0.9
LNS wait on SENDREQ 861,429 .0 1,335 2 0.6
log file parallel write 861,326 .0 943 1 0.6
enq: TX - row lock contentio 173 99.4 506 2926 0.0
buffer busy waits 47,207 .0 222 5 0.0
db file parallel write 54,904 .0 192 4 0.0
log file switch completion 382 .0 56 146 0.0
control file parallel write 21,961 .0 41 2 0.0
log file sequential read 1,631 .0 12 8 0.0
control file sequential read 85,700 .0 6 0 0.1
latch: cache buffers chains 10,613 .0 5 1 0.0
latch: In memory undo latch 16,425 .0 5 0 0.0
SQL*Net message to client 3,496,728 .0 5 0 2.6
LNS wait on LGWR 861,496 .0 4 0 0.6
db file sequential read 527 .0 2 4 0.0
cursor: pin S wait on X 106 98.1 1 10 0.0
log file single write 136 .0 1 7 0.0
latch free 221 .0 1 2 0.0
latch: library cache 677 .0 1 1 0.0
latch: shared pool 446 .0 0 1 0.0
row cache lock 59 .0 0 5 0.0
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
LGWR wait on LNS 861,496 .0 1,416 2 0.6
events in waitclass Other 1,234,364 .4 1,405 1 0.9
LNS wait on SENDREQ 861,428 .0 1,335 2 0.6
log file parallel write 861,326 .0 943 1 0.6
db file parallel write 54,904 .0 192 4 0.0
control file parallel write 21,386 .0 40 2 0.0
log file sequential read 1,631 .0 12 8 0.0
LNS wait on LGWR 861,496 .0 4 0 0.6
control file sequential read 68,404 .0 3 0 0.1
log file single write 136 .0 1 7 0.0
direct path read 204 .0 0 1 0.0
direct path write 203 .0 0 1 0.0
os thread startup 2 .0 0 38 0.0
Log archive I/O 2,786 .0 0 0 0.0
latch: shared pool 7 .0 0 0 0.0
buffer busy waits 14 .0 0 0 0.0
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 80,550 5.6 0.1
CPU used when call started 77,649 5.4 0.1
CR blocks created 90,665 6.3 0.1
Cached Commit SCN referenced 668 0.1 0.0
Commit SCN cached 2 0.0 0.0
DB time 3,639,570 253.6 2.7
DBWR checkpoint buffers written 117,014 8.2 0.1
DBWR checkpoints 35 0.0 0.0
DBWR thread checkpoint buffers w 0 0.0 0.0
DBWR transaction table writes 3,745 0.3 0.0
DBWR undo block writes 49,707 3.5 0.0
IMU CR rollbacks 32,686 2.3 0.0
IMU Flushes 210,775 14.7 0.2
IMU Redo allocation size 431,911,696 30,098.1 322.8
IMU commits 765,269 53.3 0.6
IMU contention 204,178 14.2 0.2
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 371,947 25.9 0.3
IMU recursive-transaction flush 2 0.0 0.0
IMU undo allocation size 2,684,790,280 187,091.7 2,006.4
IMU- failed to get a private str 371,947 25.9 0.3
SMON posted for undo segment shr 8 0.0 0.0
SQL*Net roundtrips to/from clien 3,538,448 246.6 2.6
active txn count during cleanout 89,287 6.2 0.1
application wait time 50,624 3.5 0.0
background checkpoints completed 34 0.0 0.0
background checkpoints started 35 0.0 0.0
background timeouts 53,792 3.8 0.0
branch node splits 11 0.0 0.0
buffer is not pinned count 515,911 36.0 0.4
buffer is pinned count 129,398 9.0 0.1
bytes received via SQL*Net from 755,607,474 52,655.1 564.7
bytes sent via SQL*Net to client 371,055,517 25,857.3 277.3
calls to get snapshot scn: kcmgs 5,862,879 408.6 4.4
calls to kcmgas 1,460,668 101.8 1.1
calls to kcmgcs 881,324 61.4 0.7
change write time 3,499 0.2 0.0
cleanout - number of ktugct call 175,320 12.2 0.1
cleanouts and rollbacks - consis 77,773 5.4 0.1
cleanouts only - consistent read 73,061 5.1 0.1
cluster key scan block gets 20,348 1.4 0.0
cluster key scans 652 0.1 0.0
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 3 0.0 0.0
commit batch/immediate requested 3 0.0 0.0
commit cleanout failures: buffer 3 0.0 0.0
commit cleanout failures: callba 29 0.0 0.0
commit cleanout failures: cannot 200,675 14.0 0.2
commit cleanouts 2,169,637 151.2 1.6
commit cleanouts successfully co 1,968,930 137.2 1.5
commit immediate performed 3 0.0 0.0
commit immediate requested 3 0.0 0.0
commit txn count during cleanout 564,136 39.3 0.4
concurrency wait time 23,715 1.7 0.0
consistent changes 135,942 9.5 0.1
consistent gets 17,065,392 1,189.2 12.8
consistent gets - examination 2,490,161 173.5 1.9
consistent gets direct 0 0.0 0.0
consistent gets from cache 17,065,393 1,189.2 12.8
Instance Activity Stats DB/Inst: DBNAME/DBNAME Snaps: 2567-2569
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
cursor authentications 84 0.0 0.0
data blocks consistent reads - u 97,058 6.8 0.1
db block changes 7,217,062 502.9 5.4
db block gets 8,933,668 622.6 6.7
db block gets direct 25 0.0 0.0
db block gets from cache 8,933,643 622.6 6.7
deferred (CURRENT) block cleanou 314,574 21.9 0.2
dirty buffers inspected 0 0.0 0.0
enqueue conversions 6,007 0.4 0.0
enqueue releases 3,413,819 237.9 2.6
enqueue requests 3,413,829 237.9 2.6
enqueue timeouts 7 0.0 0.0
enqueue waits 28 0.0 0.0
exchange deadlocks 10 0.0 0.0
execute count 2,320,755 161.7 1.7
failed probes on index block rec 0 0.0 0.0
free buffer inspected 3,307 0.2 0.0
free buffer requested 98,787 6.9 0.1
heap block compress 1,643 0.1 0.0
hot buffers moved to head of LRU 188 0.0 0.0
immediate (CR) block cleanout ap 150,834 10.5 0.1
immediate (CURRENT) block cleano 11,320 0.8 0.0
index crx upgrade (found) 61 0.0 0.0
index crx upgrade (positioned) 6,518 0.5 0.0
index fast full scans (full) 1,422 0.1 0.0
index fetch by key 1,391,889 97.0 1.0
index scans kdiixs1 47,236 3.3 0.0
leaf node 90-10 splits 24 0.0 0.0
leaf node splits 3,355 0.2 0.0
lob reads 4 0.0 0.0
lob writes 62 0.0 0.0
lob writes unaligned 62 0.0 0.0
logons cumulative 1,076 0.1 0.0
messages received 1,784,403 124.4 1.3
messages sent 1,784,404 124.4 1.3
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 12,018,647 837.5 9.0
opened cursors cumulative 2,317,944 161.5 1.7
parse count (failures) 1 0.0 0.0
parse count (hard) 111 0.0 0.0
parse count (total) 2,317,434 161.5 1.7
parse time cpu 2,964 0.2 0.0
parse time elapsed 3,429 0.2 0.0
physical read IO requests 344 0.0 0.0
physical read bytes 3,170,304 220.9 2.4
physical read total IO requests 88,336 6.2 0.1
physical read total bytes 3,166,883,840 220,686.8 2,366.7
physical read total multi block 1,975 0.1 0.0
physical reads 387 0.0 0.0
physical reads cache 183 0.0 0.0
physical reads cache prefetch 43 0.0 0.0
physical reads direct 204 0.0 0.0
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 43 0.0 0.0
physical write IO requests 58,047 4.1 0.0
physical write bytes 960,446,464 66,929.5 717.8
physical write total IO requests 1,878,018 130.9 1.4
physical write total bytes 7,678,449,664 535,078.8 5,738.3
physical write total multi block 1,717,505 119.7 1.3
Instance Activity Stats DB/Inst: DBNAME/DBNAME Snaps: 2567-2569
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
physical writes 117,242 8.2 0.1
physical writes direct 229 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 0 0.0 0.0
physical writes from cache 117,013 8.2 0.1
physical writes non checkpoint 4,394 0.3 0.0
pinned buffers inspected 0 0.0 0.0
prefetch clients - default 184 0.0 0.0
prefetch warmup blocks aged out 0 0.0 0.0
prefetch warmup blocks flushed o 0 0.0 0.0
prefetched blocks aged out befor 0 0.0 0.0
process last non-idle time 14,551 1.0 0.0
recursive calls 2,140,429 149.2 1.6
recursive cpu usage 13,915 1.0 0.0
redo blocks written 2,751,572 191.8 2.1
redo buffer allocation retries 293 0.0 0.0
redo entries 2,416,684 168.4 1.8
redo log space requests 457 0.0 0.0
redo log space wait time 5,787 0.4 0.0
redo ordering marks 20,350 1.4 0.0
redo size 1,027,539,188 71,604.9 767.9
redo subscn max counts 32,882 2.3 0.0
redo synch time 614,510 42.8 0.5
redo synch writes 1,339,088 93.3 1.0
redo wastage 332,871,028 23,196.4 248.8
redo write time 102,162 7.1 0.1
redo writer latching time 17 0.0 0.0
redo writes 861,315 60.0 0.6
rollback changes - undo records 7 0.0 0.0
rollbacks only - consistent read 9,970 0.7 0.0
rows fetched via callback 229,000 16.0 0.2
session connect time 0 0.0 0.0
session cursor cache hits 2,090,582 145.7 1.6
session logical reads 25,999,055 1,811.8 19.4
session pga memory max 16,143,352 1,125.0 12.1
session uga memory 25,800,512 1,797.9 19.3
session uga memory max 307,663,120 21,439.8 229.9
shared hash latch upgrades - no 19,479 1.4 0.0
shared hash latch upgrades - wai 352 0.0 0.0
sorts (memory) 47,531 3.3 0.0
sorts (rows) 2,258,166 157.4 1.7
sql area evicted 0 0.0 0.0
sql area purged 1 0.0 0.0
summed dirty queue length 0 0.0 0.0
switch current to new buffer 4,262 0.3 0.0
table fetch by rowid 291,327 20.3 0.2
table fetch continued row 5,742 0.4 0.0
table scan blocks gotten 11,530,343 803.5 8.6
table scan rows gotten 467,251,967 32,560.8 349.2
table scans (long tables) 0 0.0 0.0
table scans (short tables) 736,461 51.3 0.6
total number of times SMON poste 9 0.0 0.0
transaction rollbacks 3 0.0 0.0
transaction tables consistent re 0 0.0 0.0
transaction tables consistent re 0 0.0 0.0
undo change vector size 347,446,984 24,212.1 259.7
user I/O wait time 244 0.0 0.0
user calls 5,584,493 389.2 4.2
user commits 1,338,100 93.3 1.0
user rollbacks 0 0.0 0.0
Instance Activity Stats DB/Inst: DBNAME/DBNAME Snaps: 2567-2569
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
workarea executions - optimal 11,326 0.8 0.0
write clones created in backgrou 0 0.0 0.0
write clones created in foregrou 85 0.0 0.0
log_archive_config dg_config=(DBNAME2)
log_archive_dest_1 location=/test/oraarch/DBNAME/
log_archive_dest_2 location=/test/oraarch/DBNAME/
log_archive_dest_3 SERVICE=DB2 LGWR SYNC AFFIRM REOP
log_archive_dest_state_3 ENABLE
log_archive_format DBNAME%t_%s_%r.arc
log_archive_max_processes 3

复制代码

分享0

下载专业ORACLE数据库恢复工具PRM-DUL For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ: 47079569

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-3-12 21:34:08

补充：

Question:

向大侠请教一个问题。

SYNC方式写redo的步骤：
~~~~~~~~~~~~~~~~~~~~~~~
      1.主数据库上LGWR进程提交一个事务时，由LNSn进程发起网络I/O到远程备用库；
      2.备用库上，远程文件服务(RFS)通过网络从主库的LNSn进程接收redo信息，写入到standby log中；
      3.直到redo 数据写完主库redo log 和备用库standby log后，主库的事物才会提交；

楼主的AWR中
user commit 1,338,100 ，每秒commit 177回，一个commit 产生redo 404B
如果SYNC模式是上面说的情况的话，那么它应该把SDU调小吧，让事务尽快发送出去，然后备库应用。还有就是检查备库，让备库的应用快些。

Maclean's  Answer:

LGWR wait on LNS                   861,496    1,416
LNS wait on SENDREQ                861,429    1,335
redo writes                               861,315          60.0          0.6

以上三个等待事件的次数大致相仿，可以看做是 LNS做网络传输的次数 number of network send times

redo size                            1,027,539,188    71,604.9       767.9

LNS 大约每次传输 1,027,539,188/861,496= 1192bytes 确实平均每次传输的量不大，SUD 默认 8192bytes
调大 SDU 获得的收益确实可能并不大。

"The data units are then send to network layer when they are either full, flushed , or read by client"
这里 SDU 不是等待FULL才send ，所以无必要调小SDU 。

还是建议调优OS 的一些TCP 参数，或者网络硬件。

回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-3-12 21:31:46

log_archive_dest_3          SERVICE=DB2 LGWR SYNC AFFIRM REOP

考虑使用 max performance mode  ==> ASYNC

如果不能

考虑调优一些TCP 参数，使用更大的SDU -- session data unit
For example:

sales.us.acme.com=
(DESCRIPTION=
(SDU=11280)
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
   (SERVICE_NAME=sales.us.acme.com))
)

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册