Hi All
The above wait event appears consistantly in my statspack report.
My log file sizes are 5Mb (samllish in size) i have two groups with two members each.They are not on raid 5.Any ideas how do we get rid of this wait event.
regards
Hrishy
Printable View
Hi All
The above wait event appears consistantly in my statspack report.
My log file sizes are 5Mb (samllish in size) i have two groups with two members each.They are not on raid 5.Any ideas how do we get rid of this wait event.
regards
Hrishy
5MB... so small, create more groups or increase in size, i would do both
Hi Pando
Thanks for the sugestion..I was thiking of somethin more reasonable like 20 -25 Mb..I will do this and see wheather i can elimnate this wait event.
regards
Hrishy
I would certainly increase the number of groups.
As for the size, remember that you normally want some log switching: it allows the archlog to be generated (e.g. for standby) and forces a checkpoint, which would speed up an instance recovery. You may also want to limit the size to ease transmission of archlogs over the network. So the question here is: how frequently do you get a log switch with a 5MB file size? (and of course: how long is the wait? is it significant?)
Hi Dapi
The archive logs switch at a half an hour interval.I saw this through the alert logs.The wait event si significant as revealed by the statspack report.
I dont have a standby for this instance.
Ideally i would have prefered a logswitch every hour.
I will try both i guess adding one more group and increasing the size of the members to 20Mb
any other suggestions thoughts are most welcome
regards
Hrishy
Significant? (Remember, something has to be in the "top five waits". If your db & application are brilliantly tuned, with all data & parsed SQL in buffers, log switches & control file writes are about the only things left to wait on! :)) So my question stands - how long do you wait for a switch?Quote:
Originally posted by hrishy
The wait event si significant as revealed by the statspack report.
Hi Dapi
Yes i was checking the DB infact a routine performance review and this wait event showed up in the top 5 wait events along with controlm file parallel write.
The reason for control file parallel write being in the top 5 wait event was all the control files are placed on the same disk.
The next significant wait event was the one for log file switch thats when i started diggin in deeper.And started looking at the log file switch rates and the sizes of the logfiles.
what do u think ?
regards
Hrishy
Hrishy :
I think still u havent answered DAPI's
Quote:
So my question stands - how long do you wait for a switch?
Hi Abhay Dapi
I dont have exact number but in the top 5 wait events .
The wait event corresponding to log file switch and control file parallel write contribute 52% and 10% respectively.
regards
Hrishy
If you're switching every 30 minutes, I would question why you are waiting that much. I would carefully look at smaller timeslices in your statspack reports (maybe 5 minutes or so) to see what is really going on.
Hi Marist
why would u look for smaller time slices in the statspack report.The statspack report is taken in a interval of 15 minutes..
regards
Hrishy
I am guessing that waiting on a logswitch is happening during that 15 minute window with not a lot of other activity going on. Therefore, the time spend waiting for a log switch may be high in comparison to other waits going on.
A smaller window will let you see what is happening between log switches. If you got still get high logswitch waits when no logswitch occurs, there is something else going on.
BUT YOU DO! It's all there in the statspack, e.g.:Quote:
Originally posted by hrishy
I dont have exact number but in the top 5 wait events .
PHP Code:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 9,747 7,168 45.34
control file parallel write 1,250 2,012 12.73
file open 441 1,379 8.72
enqueue 24 946 5.98
log file sync 557 715 4.52
-------------------------------------------------------------
Wait Events for DB: PROD Instance: prod Snaps: 992 -993
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file sequential read 9,747 0 7,168 7 3.8
control file parallel write 1,250 0 2,012 16 0.5
file open 441 0 1,379 31 0.2
enqueue 24 0 946 394 0.0
log file sync 557 1 715 13 0.2
log file parallel write 644 0 538 8 0.2
control file sequential read 6,004 0 514 1 2.3
Jeff, I think this could be one of those cases where a ratio (58% of waits are due to X) is misleading. The real question is: how long are my users waiting because of X? Is it milliseconds, seconds, minutes, hours?Quote:
Originally posted by marist89
Therefore, the time spend waiting for a log switch may be high in comparison to other waits going on.
True, could be.Quote:
Originally posted by DaPi
Jeff, I think this could be one of those cases where a ratio (58% of waits are due to X) is misleading. The real question is: how long are my users waiting because of X? Is it milliseconds, seconds, minutes, hours?
Hi Dapi
:-) Well i dont have acesses to raw data like that.Just graphs and pie charts :-)
regards
Hrishy
Aghhhhh! You've been got at by the marketeers! The "raw" data come from the standard "spreport".Quote:
Originally posted by hrishy
Well i dont have acesses to raw data like that.Just graphs and pie charts
P.S. how do you get the fancy stuff? I might want to impress my boss . . . .
Hi Dapi
There is a custom program written that will graph all the details to me with fancy HTML using applets etc..no acessses to raw data :-)
i guess we can easily write sucha prog using php and GD library :-)
regards
Hrishy
Hi Hrishy, Yet another GUI that stops you seeing the truth! If your "customisers" can't show you the TIME waited, it's time to send in Mr.Hanky (or Donald von Rumsfeld).
Hi DapiQuote:
Originally posted by DaPi
Hi Hrishy, Yet another GUI that stops you seeing the truth! If your "customisers" can't show you the TIME waited, it's time to send in Mr.Hanky (or Donald von Rumsfeld).
hehe :-)
regards
Hrishy