Home » RDBMS Server » Server Administration » Slow response during peak activity (merged) (ORACLE 11.2.0.3 2 nodes RAC soalris)
Slow response during peak activity (merged) [message #622529] Wed, 27 August 2014 07:26 Go to next message
janakors
Messages: 180
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 Go to previous messageGo to next message
EdStevens
Messages: 983
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 #622532 is a reply to message #622530] Wed, 27 August 2014 08:08 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
DBMS_LOGMNR can be used ti reveal which tables are being impacted by the DML that result in REDO
Re: to many log switch occur [message #622541 is a reply to message #622532] Wed, 27 August 2014 09:04 Go to previous messageGo to next message
John Watson
Messages: 7619
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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 Go to previous messageGo to next message
janakors
Messages: 180
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 #622563 is a reply to message #622559] Wed, 27 August 2014 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
If you want fewer REDO log switches, then just increase the size of the REDO files.
Re: to many log switch occur [message #622567 is a reply to message #622563] Wed, 27 August 2014 12:12 Go to previous messageGo to next message
janakors
Messages: 180
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
Re: to many log switch occur [message #622575 is a reply to message #622567] Wed, 27 August 2014 13:43 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
I would size the REDO file at 2GB each.
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 Go to previous messageGo to next message
janakors
Messages: 180
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 Go to previous messageGo to next message
John Watson
Messages: 7619
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 #622579 is a reply to message #622576] Wed, 27 August 2014 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
what problem are you trying to solve?
How will you, I or anyone reading this know when correct solution has been posted?
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 Go to previous messageGo to next message
janakors
Messages: 180
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 #622583 is a reply to message #622581] Wed, 27 August 2014 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
>i check SQL monitoring tab and the sql queries were taking 8 sec
is 8 seconds fast, slow, or typical? Any metric without context is meaningless.

is the application 3-tier?

If you don't know where time is being spent, you can not begin to determine what exactly needs to change to make things faster.
You can see where time is being spent after you do as below

ALTER SESSION SET SQL_TRACE=TRUE

then processing trace file using "tkprof"
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 Go to previous messageGo to next message
John Watson
Messages: 7619
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: to many log switch occur [message #622588 is a reply to message #622559] Wed, 27 August 2014 18:17 Go to previous messageGo to next message
EdStevens
Messages: 983
Registered: September 2013
Senior Member
janakors wrote on Wed, 27 August 2014 11:51
DB has the record of employee and their thumb impression so Db just confirm this data upon request,



So comparing input from a device with a value stored on a table? Wouldn't that just be a SELECT? What is going on that produces DML?

I suspect your log switches are a red-herring to your business problem.
Re: to many log switch occur [message #622589 is a reply to message #622588] Wed, 27 August 2014 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
>What is going on that produces DML?
I suspect concurrent with employee validation, the employee timesheet record gets INSERTED.


> it is a bio-metric entry system
what is the ball park count for total number of finger print readers that are connected to this database?

Does application utilize Connection Pooling?
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 Go to previous messageGo to next message
janakors
Messages: 180
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 #622601 is a reply to message #622599] Thu, 28 August 2014 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i havn't run the AWR yet. how to run it please.

John Watson wrote on Wed, 27 August 2014 21:58
...generate the AWR rport for the SQL (do you know how to so that? The script is awrsqrpt.sql)...


Did you try to run the script?

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7619
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 #622605 is a reply to message #622599] Thu, 28 August 2014 01:26 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Quote:
i dont have any problem with the SQL
You DO have a problem with the SQL. You said it earlier: it takes 8 seconds to run. You have not yet said how long you would like it to take, nor provided the SQL, nor any execution information.
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 Go to previous messageGo to next message
janakors
Messages: 180
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

Re: ADDM report says Cluster and USER I/O wait event occured [message #622610 is a reply to message #622607] Thu, 28 August 2014 01:44 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Look, I've tried to help. But I don't think I can: you do not like my problem solving approach, and do not appear to have the technical skills needed to implement it or to tune your hardware.
Perhaps someone else can assist.
Re: ADDM report says Cluster and USER I/O wait event occured [message #622615 is a reply to message #622610] Thu, 28 August 2014 02:28 Go to previous messageGo to next message
janakors
Messages: 180
Registered: September 2009
Senior Member
No Mr John you are helping but i have just chat with a application guy and we both agreed on the SQL problem
thank you,
Re: ADDM report says Cluster and USER I/O wait event occured [message #622616 is a reply to message #622615] Thu, 28 August 2014 02:32 Go to previous messageGo to next message
janakors
Messages: 180
Registered: September 2009
Senior Member
i want to take AWR report on SQL prompt so i want to ask is it resource hungry task i mean can it slow down system or not as i am doing it right now on production. please reply

Regards

[Updated on: Thu, 28 August 2014 02:34]

Report message to a moderator

Re: ADDM report says Cluster and USER I/O wait event occured [message #622633 is a reply to message #622616] Thu, 28 August 2014 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not particularly and even if it was so what? It's a one shot hit and you need the data it provides.
Re: ADDM report says Cluster and USER I/O wait event occured [message #622700 is a reply to message #622633] Thu, 28 August 2014 10:34 Go to previous messageGo to next message
janakors
Messages: 180
Registered: September 2009
Senior Member
i have successfully generated a HTML based AWR report but file extension is .lst, how should i open it after trasfering to my windows machine
Re: ADDM report says Cluster and USER I/O wait event occured [message #622701 is a reply to message #622700] Thu, 28 August 2014 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
janakors wrote on Thu, 28 August 2014 08:34
i have successfully generated a HTML based AWR report but file extension is .lst, how should i open it after trasfering to my windows machine



ren fubar.lst fubar.html

then open it in Browser of choice
Re: ADDM report says Cluster and USER I/O wait event occured [message #622712 is a reply to message #622701] Thu, 28 August 2014 11:55 Go to previous message
janakors
Messages: 180
Registered: September 2009
Senior Member
ohhh.. You are right ...thanks
Previous Topic: Reuse storage of tablespace
Next Topic: Changing SGA parameter
Goto Forum:
  


Current Time: Fri Oct 19 18:41:21 CDT 2018