| Slow response during peak activity (merged) [message #622529] |
Wed, 27 August 2014 07:26  |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
hi ,
i have observed in alert log that at regular intervals too many log switches occur. e.g today (27 Aug )log switch occur at 10:37 (AM) and continues 10:44(AM) which are total 6 new sequence number (33280 to 33285) now the sized of log file is 512 mb which means 3 gb of log generated now this story continues . as in early morning it was started at 8:41 till 8:44 and 4 log generated and then starts at 9:40 to 9:44 and 8 logs were generated. now at 10:37 i have seen user I/O wait class increases and crossing that 16 cores(Machine) line and some concurrency wait class also observed .
can any one help me about these logs generation and any relation with slowing down the app at 10:37
Kindly Guide
Regards
[EDITED by LF: fixed topic title typo]
[Updated on: Wed, 27 August 2014 10:30] by Moderator Report message to a moderator |
|
|
|
| Re: to many log switch occur [message #622530 is a reply to message #622529] |
Wed, 27 August 2014 07:44  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
So these spikes occur at the same time every day? Do you have a scheduled batch job that does a lot of dml? Or even a user-initiated (not scheduled) process, where said user is kicking off a heavy dml at the same time every day?
[EDITED by LF: removed unnecessary quote of the whole previous message]
[Updated on: Wed, 27 August 2014 07:54] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: to many log switch occur [message #622541 is a reply to message #622532] |
Wed, 27 August 2014 09:04 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Log miner for analyzing 4 GB of redo? Woo! Rather you than me.
I would start with statspack (or AWR, if you have the licence). Increase the snapshot frequency to every 15 minutes (I would never go more frequently than that) just for tomorrow morning, and get the reports out. Post them here.
|
|
|
|
| Re: to many log switch occur [message #622558 is a reply to message #622541] |
Wed, 27 August 2014 11:27 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
A fine coincidence. Today morning at office, an application developer reached me with an issue about frequent log switching. Customer sent us the log information already. It showed too frequent log switching during a particular time interval.
Based on the provided info, I probed the customer if it is production or some other environment. I got an immediate reply that it is the training environment. The next thing I found that, the interval during which the log switching caused an issue, there were too many users connected to training DB and doing 4x times DML activity than normal(production DB) around that time, thus filling the redo logs too frequently.
|
|
|
|
| Re: to many log switch occur [message #622559 is a reply to message #622541] |
Wed, 27 August 2014 11:51 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
@EdStevens. no there is no scheduled jobs and it is a bio-metric entry system . DB has the record of employee and their thumb impression so Db just confirm this data upon request, Now in morning a lot of employees come to office and during days hours this values decreases so that is the purpose of the DB. i will increase the AWR snapshots to 15 minutes and will post the report.
regards,
|
|
|
|
|
|
| Re: to many log switch occur [message #622567 is a reply to message #622563] |
Wed, 27 August 2014 12:12 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
no i recently increase the redo log files as i was getting checkpoint not complete alert in alert log . at that time it was 50 mb each with only one member in each gp, i increase the gps from 2 to 3 and also added the 2nd member for redundancy and increase the size to 512 mb. i dont have any problem of space but i have a doubt that is there any thing strange/ or erroneous / wrong about it.
Regards
|
|
|
|
|
|
| ADDM report says Cluster and USER I/O wait event occured [message #622576 is a reply to message #622529] |
Wed, 27 August 2014 13:47 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
hi,
i have observed a wait in my 2 node RAC db i run ADDM for that period and found that it says 67% were the SQl queries and 33 % were 'Cluster' and 'USer I/O' wait event contributing the over all wait. Now under 'Cluster' and 'User I/O' it says that run 'Segement Advisor' on schema1.table1 with object ID 73883 and under action label it says check the application logic involving I/O table schemea.table1 with object Id 73883. the other heading was of 'segment tuning'. i didn;t really understand all these as i dont have any knowledge of performance tuning . i cant post my ADDM report right now but before this i need help on these findings.
other above findings it also says that undersized SGA which i will increase.
please Guide now
Thanks and regards
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622578 is a reply to message #622576] |
Wed, 27 August 2014 14:01 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ADDM reports are a waste of time. All they do is identify certain figures and ratios, and suggest actions that might change them. So what? No end user has ever complained that the "the cluster wait events are too high". They complain about things like "the overnight batch jobs didn't finish until lunchtme". You need to identify and tune business problems, not just respond to some dumb wizard that knows nothing about your application.
Delete that report, and never look at another.
|
|
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622581 is a reply to message #622579] |
Wed, 27 August 2014 14:41 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
well thanks for reply but the problem was the delay. infact it is db for authenticating the user data and then his/her thumb impression it is a bio metric system so today user have to wait for 30 seconds at office gates to enter as the db was slow to respond so i checked at that time the EM performance tab that User I/O and Cluster waits spikes. a concurrency spikes were also found so then i run ADDM so if i dont belive the ADDM then what should i do. i check SQL monitoring tab and the sql queries were taking 8 sec by average to execute no alert in alert log and no blocking session so kindly tell me what should i do what kinds of views should i check or tak ASH reports or what please i need guide lines and ready to study and learn quickly. suggest something in such cases. i have checked the table statistics and all tables were recently analyzed, is there any problem with the INDEXES ?are they need to rebuild? i have c found all these on surfing the need but i dont know the sequence how to start about this Performance Tuning.
thanks
Regards
|
|
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622585 is a reply to message #622581] |
Wed, 27 August 2014 14:58 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You say that you have found the problem: a SQL that tkes 8 seconds. First, how long do you need it to take? Second, since you seem to have all the licences, generate the AWR rport for the SQL (do you know how to so that? The script is awrsqrpt.sql) and post it here. Third, generate an AWR report for the period when the SQLs were slow and post that here. Fourth, do some reading. Have you read the Performance Tuning Guide yet?
Is this the same problem as in your other thread? If so, I shall merge them.
|
|
|
|
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622599 is a reply to message #622585] |
Thu, 28 August 2014 01:12 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
Quote:Is this the same problem as in your other thread? If so, I shall merge them. yes same problem and i havn't run the AWR yet. how to run it please.'
appl is of 3 tiers . it is secure data centre and websphere is deployed for web server etc. we are using ASM, we are using SUN M4000 server with 32 gb of RAM with 1 gb of cross cable as heart beat.
i dont have any problem with the SQL . the wait event like Cluster and USer I/O have peaks in the performance tab of EM when problem occur so ormallly machine CPU is 10 % but at that time it was 45 %. ADDM sayd u might have an issue of SGA undersize and current SGA size is 15 gb.
Regards
please forgive my english and spelling mistake
thak you all
[Updated on: Thu, 28 August 2014 01:17] Report message to a moderator |
|
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622603 is a reply to message #622601] |
Thu, 28 August 2014 01:22 |
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
|
Senior Member |
|
|
|
For RAC, you should have a 10gig Ethernet interconnect. It is important that this link not go through a firewall. Not sure if that is causing your performance issue, but 1 gigabit interconnect won't cut it in most cases. Also should turn on jumbo frames for the interconnect network.
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622604 is a reply to message #622599] |
Thu, 28 August 2014 01:23 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've merged your topics. When you have the reports, perhaps some progress can be made.
However, one thing you must address is that your use of a crossover cable for the interconnect is not supported. There is no reason for using such a pathetic solution. You say it is "1 gb" is that gigabyte or gigabit? If gigabit, it is too slow. Either way, you need at least two of them and a switch. The cost of the extra hardware is trivial, it is silly to continue as you are.
|
|
|
|
|
|
| Re: ADDM report says Cluster and USER I/O wait event occured [message #622607 is a reply to message #622605] |
Thu, 28 August 2014 01:38 |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
with 8 seconds of query is ok ? why i am saying ? because i never face problem with that ? i dont know what that query is and what should be the ideal time but it ok no complaints regarding this 8 seconds.
it is one gigabit and normally on other dbs we have 10 gigabit. it is direct cable and if u think that it may be the problem so can u tell me any queries to check if the rate of cache fusion and other s are in the safe range.
how to turn on jumbo frame and how check if it is not on. i want to RUN THE AWR through EM. i am searching on net how to do it
Reagrds
[Updated on: Thu, 28 August 2014 01:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
