| poor performance during switch log file [message #676074] |
Sun, 12 May 2019 05:37  |
 |
fabi88
Messages: 112
Registered: November 2011
|
Senior Member |
|
|
Hi experts,
There is a delay in response time of queries because of long time for switch log file (Private strand flush not complete), actually response time of queries are good before and after switch log file .
- redo logs are on fast disks
- redolog size is 1.5G (log switch per hour is normal, about 5)
wait events are as follws:
db file async I/O submit
CPU + Wait for CPU
log file parallel write
can something like latch contention cause wait for db writer? or something else?
could you please help to resolve this issue? thank you in advance?
|
|
|
|
|
|
| Re: poor performance during switch log file [message #676077 is a reply to message #676074] |
Sun, 12 May 2019 11:53  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
db file async I/O submit and log file parallel write are waits for background processes, so shouldn't be a problem. CPU + Wait for CPU is actually working, so certainly not a problem. What is the problem? Is it, for example, a particular query?
|
|
|
|
| Re: poor performance during switch log file [message #676090 is a reply to message #676077] |
Tue, 14 May 2019 04:27 |
|
fabi88
Messages: 112
Registered: November 2011
|
Senior Member |
|
|
Quote:db file async I/O submit and log file parallel write are waits for background processes, so shouldn't be a problem. CPU + Wait for CPU is actually working, so certainly not a problem. What is the problem? Is it, for example, a particular query?
Thank you so much for your replies.
No, it is not for particular query, response time for all queries which was running before log switch is 5sec, but it is increased to 25sec during log switch, response time decreased to 5sec after log switch.
Quote:SHOW us using COPY & PASTE the actual elapsed time duration for log file switches
is there any query for finding elapsed time duration for log file switches?
Quote:What results if you simply add more log file groups?
at first, the count of log group was 3 with 1 member, We increased both log group count to 6 and the size of log, But only the switch time was delayed and the time of increased response time as well.
|
|
|
|
| Re: poor performance during switch log file [message #676092 is a reply to message #676090] |
Tue, 14 May 2019 04:49 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If the problem really is caused by log file switches, the wait event you should see is log file switch completion. Are you seeing this on the session(s) that are running the queries? It is hard to see why a SELECT query would hit that wait event, it should apply to DML queries.
Can you post a Statspack or AWR report over an hour or so? Also, the SQL reports on one or two of the SQLs that hit the problem.
In the meantime, if your hypothesis is correct then the obvious first step is to replace your logfile groups with larger ones. Make them three imes as big, and the problem will occur one third as often.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
