Hi Group ,

Here are the values of db writer related wait events. According to the
manuals, high value means bottleneck . But how can i decide what the
high value is?

These outputs reflects the 8 hours period.

select * from gv$sysstat where name ='DBWR buffers scanned' or
name='DBWR lru scans' order by inst_id


first run:

INST_ID NAME
CLASS
VALUE
1 DBWR lru scans
23583
1 DBWR buffers scanned
10287267
2 DBWR lru scans
6668
2 DBWR buffers scanned
4897092

after 8 hours:

INST_ID NAME
VALUE
1 DBWR lru scans
25583
1 DBWR buffers scanned
11369509
2 DBWR lru scans
6756
2 DBWR buffers scanned
4985046




select * from gv$system_event where event in ('buffer busy waits','db
file parallel write','free buffer waits','write complete waits')
order by 1

1.run:

INST_ID EVENT
TOTAL_WAITS
TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT TIME_WAITED_MICRO

1 free buffer waits
605
492 53233
88 532330815

1 write complete waits
17
14 1467
86 14670866

1 buffer busy waits
326276
197 372973
1 3729726693

2 free buffer waits
4137
3291 342129
83 3421290680

2 write complete waits
359
165 19485
54 194854301

2 buffer busy waits
812305
1244 757945
1 7579448834


after 8 hours:

INST_ID EVENT
TOTAL_WAITS
TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT TIME_WAITED_MICRO

1 free buffer waits
901
579 64007
71 640069129

1 write complete waits
23
18 1949
85 19486573

1 buffer busy waits
417070
244 446700
1 4466999220

2 free buffer waits
4376
3417 355618
81 3556182943

2 write complete waits
390
192 22195
57 221953503

2 buffer busy waits
829240
1621 911937
1 9119370934





select * from gv$sysstat where name ='redo log space requests'

1.run:

INST_ID NAME
VALUE
2 redo log space requests
2619
1 redo log space requests
1823

after 8 hours:

INST_ID NAME
VALUE
2 redo log space requests
2978
1 redo log space requests
2220

Kind Regards,
tolga

Using statspack, take a snapshot every 10 minutes for a few days. Start
comparing the statspack values from the report over the smaller
time frames too see if there are actually waits, or the database is
just always writing.

You will be able to identify the statements causing the writes and tune
them, you will be able to find the datafiles that are being written too
and move them around or move the objects around if the DB is hot
blocking on a group of disk spindles.

We have a database that spends its entire life writing to disk because
it simply is fed data 24 hours a day as fast as it can accept it, then
it has to clean up the old stuff while dealing with the new stuff.
almost all it does is write. 94% of all operations in the database is
writes.
But it isn't in need of any tuning.

Look on the manuals for statspack.

Hi Herod ,

Thanks for your suggestions .

The latest value of buffer busy waits time_wait in node2 is :
1734259. (in 1/100 seconds.) This equals to : 17342.59 seconds or 4.89
hours . The db has been running for 1 week . In 1 week 4.89 hours are
spent in buffer busy waits. Should i consider this as a high value? How
can i analyse this wait times ?Is there any document etc?


Kind Regards,
tolga

Whats that... about 3% of its total time in buffer waits? Over a week
that sounds great. But it could be bad, If all of that took place
during a single 5 hour period. There is some very upset user(s) out
there cursing the database and everyone associated with it.

You need to put it all into perspective and identify when the DB might
be slow. Small time stamps.

I would recommend a monitoring tool as well as the statspack snapshots.

http://www.fourthelephant.com/

has a tool in beta mode, it also has a plug in for Oracle SQL Developer
(raptor).

I am not affiliate with http://www.fourthelephant.com/ at all, and if
it blows up your system, causes you to get fired and your family to
disown you - tough luck.

Test test test


http://yaodba.blogspot.com/

The performance tuning guide for your version is a document you should
have open and in front on you.