Home » RDBMS Server » Performance Tuning » application is slow? what to check immediately (oracle 11.2.0.1 RAC of two nodes,solaris10)
application is slow? what to check immediately [message #543763] Thu, 16 February 2012 13:42 Go to next message
janakors
Messages: 171
Registered: September 2009
Senior Member
hi,
well i am asking a very basic question and novice in performance tuning of oracle databases so pardon me please.
one fine morning clients come up with a complaint that application is dead slow and application tier checked it and found nothing and i have been asked to see your DB, what straight away action i will take to see that to see if there any issue with my DB ? on EM and on sql

kindly advice

regards

janakors
Re: application is slow? what to check immediately [message #543764 is a reply to message #543763] Thu, 16 February 2012 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
At OS level is any resource (RAM, CPU, Disk Network) a bottleneck?
Is every SQL slow or only some subset (which)?
What is still working OK/normally?
Re: application is slow? what to check immediately [message #543765 is a reply to message #543763] Thu, 16 February 2012 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the very first question is: "where time is spent"?
If only 5% of the time is spent in the DB, even if you can reduce it to 0 the overall increase in performances will be no more than 5%.

Regards
Michel
Re: application is slow? what to check immediately [message #543768 is a reply to message #543765] Thu, 16 February 2012 14:31 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thats a query I use in my database every time someone complains "it is slow...."

select DISTINCT s.sid,s. SERIAL#, event, p1text, p1, p2text, p2, 
                p3text, p3,
                wait_time, seconds_in_wait, state, 
                osuser, program,client_info, s.MODULE, 
                sql_text
  from v$session s
  join v$sql q ON q.hash_value = s.sql_hash_value
 where event not in ('SQL*Net message from client', 
                     'SQL*Net message to client', 
                     'jobq slave wait')
  AND  event not like 'PX Deq Credit%';


It basically gives me a quick "which user is waiting for what database events, with which running sql".

95% of the time someone user is complaining about slowness that catches the problematic part for me.

(If the cause is really the database)
Re: application is slow? what to check immediately [message #543984 is a reply to message #543768] Sat, 18 February 2012 16:04 Go to previous message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
1. your query should be problematic on very busy systems (unsorted long output, which is difficult for analysis),
2. a complete list of idle events is much longer,
3. some of 'PX Deq Credit%' events aren't idle.

So you have to improve your query.
Previous Topic: need help to understand this execution plan
Next Topic: Oracle parameters - processes, session, and transactions
Goto Forum:
  


Current Time: Mon Sep 01 21:51:41 CDT 2014

Total time taken to generate the page: 0.08993 seconds