web application page/form/uery taking longer time to submit/execute

Application user is sitting in Remote area accessiing
wed based application saying that his one of his web
page(form) is taking longer time to submit/execute,
he says there were no problems till yesterday but this
problem started today only

As a DBA i know there are 4 database running on same
server but iam New oracle DBA i donot know which appliaction
or database he is using. My senior DBA says there are no
changes on database side or no parameters change,
no OS side change


Could any one tell me what are the different step by
step solution can i give to applicatuion user.what could
lead this type of problems

Tks in advance

Hi
Do you have any Weblogic SERVER pool. Ask the USER which server in the pool is he hitting (IP address).From this u can know if the server is heavely loaded, if so restarting the weblogic on the server might solve the issue.If this is not the issue.issue top command on the DB server and check for process id consuming high CPU and based on the process id use the following SQL to find the ORACLE SESSION ID and from the session id find the HIGH RESOURCE SQL

select s.sid, s.serial#
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &pid; ---unix process id

select sql_text from v$sql a , v$session b
where a.hash_value = b.sql_hash_value
and b.sid = &sid; --- oracle session id

also u can use this SQL to find out the WAIT EVENT by the ACTIVE session.From the EVENT NAME u can know if a SQL is doing FULL TABLE SCAN or buffer busy wait or other wait event


select sw.sid,s.serial#,sa.sql_text,sw.event,s.schemaname "oracle user",s.osuser "os user",s.machine from v$session_Wait sw,v$sqlarea sa,v$session s where s.status='ACTIVE' and s.sql_hash_value=sa.hash_value
and s.sid = sw.sid;