这是我的一个statspack的REPORT,怎高手帮忙分析一下问题

rollingpig

最初由 anchen211 发布
[B]

另外能解释一下为什么添加日志组没有用处呢? [/B]

增加日志组是为了防止log switch时等待archiving
你没有使用archive log,所以没用。

anchen211

最初由 bulkaunt 发布
[B]1 再增大点 db_block_buffers 和shared_pool_size

2  Soft Parse %:   13.64  软分析太低了，注意绑定变量

3  可以的话，生成statspack快照时间长点，看看效果如何。你上面最长时间才不到一个小时。 [/B]

软分析太低?那怎么定位相应的SQL?
db_block_buffers 和shared_pool_size还要增呀?数据库上还有其它的应用呀.
另外,STATSPACK的取样强调的是是多次取样,每次时间为15-30分钟,间隔半个小时左右,并在数据库WARMED UP以后,我那个长时间的取样是我有其他事情才导致的,我还觉得有点长了呢.

husthxd

参考跟log file有关的几个等待事件:

You might want to reduce "log file parallel write" wait times in order to reduce user waits which depend on LGWR.
• Ensure tablespaces are NOT left in HOT BACKUP mode longer than needed.
Tablespaces in HOT BACKUP mode cause more redo to be generated for each change which can vastly increase the rate of redo generarion.
• Redo log members should ideally be on high speed disks
eg: RAID5 is not a good candidate for redo log members.
• Redo log members should be on disks with little/no IO activity from other sources.
(including low activity from other sources against the same disk controller)
• RAW devices can be faster file system files.
• NOLOGGING / UNRECOVERABLE operations may be possible for certain operations to reduce the overall rate of redo generation


There are 3 main things you can do to help reduce waits on "log file sync":
•        Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
•        If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect.
•        See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.

husthxd

根据
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file parallel write 17,802 4,197 69.27
log file sync 400 696 11.49
db file scattered read 77,132 333 5.50
db file sequential read 8,577 276 4.56
latch free 140 224 3.70

其实等待事件并不明显,
等待次数,等待时间都不大.如果执行时间可以接受的话,其实是不需要做调整的

anchen211

最初由 husthxd 发布
[B]参考跟log file有关的几个等待事件:

You might want to reduce "log file parallel write" wait times in order to reduce user waits which depend on LGWR.
• Ensure tablespaces are NOT left in HOT BACKUP mode longer than needed.
Tablespaces in HOT BACKUP mode cause more redo to be generated for each change which can vastly increase the rate of redo generarion.
• Redo log members should ideally be on high speed disks
eg: RAID5 is not a good candidate for redo log members.
• Redo log members should be on disks with little/no IO activity from other sources.
(including low activity from other sources against the same disk controller)
• RAW devices can be faster file system files.
• NOLOGGING / UNRECOVERABLE operations may be possible for certain operations to reduce the overall rate of redo generation


There are 3 main things you can do to help reduce waits on "log file sync":
•        Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
•        If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect.
•        See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options. [/B]

husthxd

google && metalink.
just search it.