Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Strange Performance problem on certain time
Xia
post Sep 12 2017, 09:55 PM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 15-August 17
From: Thailand
Member No.: 52,119



Hi Burleson,

I am a new DBA and love to read your website to research and understand oracle database.

I have strange performance problem on my oracle 11gR2 database.My application team found that on every 9:00 application will slow down and timeout some transaction. They collect response time of query that application sent to database and return result to application, very high response time between 9:00 - 9:02 on every day.

I investigate Scheduler Job and found nothing. I manage to collect AWR every 2 minute on that period and found event "enq: TX - row lock contention" and "SQL*Net break/reset to client". I already check event "enq: TX - row lock contention" that come from sql statement that "SELECT FOR UPDATE" on the same row, as we investigate that on another period they also use this statement to update same row but not high response time so we focus on event "SQL*Net break/reset to client".

I don't understand that what "SQL*Net break/reset to client" mean ? why they are appear on every 9:00 - 9:02 AM. are these event make performance of database slow down or not ? I research on Oracle Doc that show they maybe have some invalid statement from application to database but why there are appear only on 9:00AM. please suggest options for me how to investigate this Thank you very much.






9:00 - 9:02 AM


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 40121 12-Sep-17 09:00:00 617 11.9
End Snap: 40122 12-Sep-17 09:02:00 728 10.6
Elapsed: 2.00 (mins)
DB Time: 16.86 (mins)


Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 387 508 1314 50.25 Application
SQL*Net break/reset to client 160 195 1219 19.28 Application
DB CPU 127 12.55
log file sync 9,386 49 5 4.85 Commit
db file sequential read 1,523 22 15 2.22 User I/O



9:06 - 9:08 AM


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 40124 12-Sep-17 09:06:00 708 10.9
End Snap: 40125 12-Sep-17 09:08:00 698 11.0
Elapsed: 2.01 (mins)
DB Time: 4.27 (mins)


Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 105 40.98
log file sync 10,131 34 3 13.20 Commit
db file sequential read 1,621 23 14 8.91 User I/O
reliable message 823 3 3 1.03 Other
control file sequential read 2,805 2 1 0.89 System I/O




Go to the top of the page
 
+Quote Post
burleson
post Sep 13 2017, 06:28 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi Xia,

First, you did exactly the right thing in diagnosing the problem, well done!

Your issue is with the "select for update" holding row locks during a busy period.

This is an application issue, not much that a DBA can do, except encourage then to redo the "for update" in a more efficient fashion.

There are better alternatives to select for update:

http://www.dba-oracle.com/t_select_for_update.htm

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Xia
post Sep 13 2017, 09:17 AM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 15-August 17
From: Thailand
Member No.: 52,119



Hi Burleson,

Great suggestion Thank you very much rolleyes.gif . and i have another question about gv$active_session_history as i collect this view on problem period second by second and found that sid=54 was block all application session (as attachement file) but i cann't see sid=54 on this view i question that sid=54 is "inactive" session due to event = "sql*net message from client" on that time ? can i investigate what sid=54 waiting event on that period from this view or another view? Please suggest and thank you for your time happy.gif .
Go to the top of the page
 
+Quote Post
burleson
post Sep 13 2017, 11:32 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi Xia,

Look at the v$session_event view:

http://www.dba-oracle.com/oracle10g_tuning...ssion_event.htm

http://www.dba-oracle.com/t_ash_10g_v$session_event.htm

You would benefit from the troubleshooting scripts in this book:

http://www.rampant-books.com/book_1001_perf.htm


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Xia
post Sep 19 2017, 09:54 AM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 15-August 17
From: Thailand
Member No.: 52,119



Hi Burleson,

Thank you very much for your valued suggestion. I already order you book wait for reading your book. laugh.gif
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:10 PM