Home » RDBMS Server » Performance Tuning » poor performance during switch log file (Oracle 12.2.0.1 linux 7.6)
poor performance during switch log file [message #676074] Sun, 12 May 2019 05:37 Go to next message
fabi88
Messages: 111
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 #676075 is a reply to message #676074] Sun, 12 May 2019 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Sun, 12 May 2019 03:37
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?

SHOW us using COPY & PASTE the actual elapsed time duration for log file switches.
What results if you simply add more log file groups?

Re: poor performance during switch log file [message #676077 is a reply to message #676074] Sun, 12 May 2019 11:53 Go to previous messageGo to next message
John Watson
Messages: 8075
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 Go to previous messageGo to next message
fabi88
Messages: 111
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 Go to previous messageGo to next message
John Watson
Messages: 8075
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.
Re: poor performance during switch log file [message #676206 is a reply to message #676092] Mon, 20 May 2019 22:02 Go to previous message
trantuananh24hg
Messages: 741
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
As Jame said, you may see at the top SQL I/O & segment lock in workload. Finding it is easy.
Then, you could use ash instant as Tanel's introduction at the real time.
Previous Topic: Query Taking Alot of time ..Help in Tuning
Next Topic: improve performance on updating clob data
Goto Forum:
  


Current Time: Fri Dec 06 15:09:12 CST 2019