Performance Tuning:

Performance Tuning:
===============

1)Top

Find pid which takes High CPU utilization then after find sid from below query.


select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid;


set linesize 500
select sid,serial#,program,module,logon_time,client_identifier,status from v$session where sid='&SID';



2)Blocking sessions:
------------------------

select  client_identifier,sid, serial#,process, blocking_session, machine, program, module, action, event, status,sql_exec_start, prev_exec_start from gv$session
where sid in (select sid from gv$lock where block=1) order by sql_exec_start;


3)Find long running sessions/Request from more than 2 hrs (or) 6 Hrs:
---------------------------------------------------------------------

select sid,serial#,program,module,status,logon_time,client_identifier from v$session where status='INACTIVE' and MODULE like '%oracle.apps.wf.notification%';


4)Dead Locks:
----------------

select sid,serial#,program,status from v$session where sid in (select sid from v$lock where block=1); ...


5)Every week run gather schema statistics . Its better to reduce performance


6)Check Network level....All seem too good.or Not............




As Apps DBA:
----------------

a)Clearing your Internet Browser Cache, may assist with this issue.  Please perform the following:-

1.  Close down all Internet Explorer sessions
2.  Open up a new Internet Explorer session - go to Tools -> Internet options 
3.  In Browsing History first click on the 'Settings' button and check that 'Check for newer versions of stored pages' is set to 'Every time I visit the webpage' - if not please change it.
4. ‘Days to Keep History’ is to be set to ‘0’.
5. Then click on the OK button
6. Then in Browsing History please click on the Delete button then on Delete All.
7. In the pop up window "Delete Browsing History' select Yes. 
8. Close the Internet Explorer session. 

b)Still facing issues Please be follow below at user level:
------------------------------------------------------------

What process/activity were you running - does it include Core Forms / Self Service Pages / login page? Both Self Service and Login pages.
2. Did you enter or use any specific parameters when running this process/activity?
3. which responsibility you were using?
4. Do you know if other users around you are facing similar issues?
5. Is performance consistently slow or does it improve at times and if so when? 
6. How much time is it taking for Oracle to respond to your request?



c)Its also one main reason getting some front issues...


select count(*) from dba_objects where status='INVALID';

then comiple the invalids

$ORACLE_HOME/rdbmd/admin/utlrp.dql----------------->db level

or

Run adadmin and choose 3 option and Compile apps schema


d)Check free space at mount level like /tmp ,/home  and /Archive.....................


e)Facing login issue please be follow;
============================


check apace log and error log

/inst/apps/SID/logs/ora/10.1.3/Apache.log

/inst/apps/SID/logs/ora/10.1.3/error.log


[madhuapps@madhu Apache]pwd

/inst/apps/SID/logs/ora/10.1.3/Apache.log

$grep 'username=madhu' access_log.1367452800




Madhu.If you have any queries please reach me kmadhuapps2@gmail.com