Nalanta

There are many ways to troubleshoot the performance Issues in oracle database. Here you can find some methods to troubleshoot performance Issues.

 Scenario 1:

  Users are complaining that Application is slow.

1. Check the CPU load and the processes running for longtime.
 
$ top   (Sun OS)
        
If CPU Load is high then look for the processes which is consuming more CPU.   
Note down the OS process id of those processes.

2.  Then login to the Database, Execute the below query to identify Serial #,

select serial# from v$process where spid = (OSprocessid);         

Using that serial# we can get session details,
           
select  SID,serial#,username|| ‘   ‘ ||status|| ‘          ‘ ||
to_char(logon_time,’DD/MM/YYYY HH24:MI:SS’)|| ‘   ‘ ||
last_Call_et || ‘         ‘ || machine,program,osuser from v$session
where serial#=(number got from the previous query)
order by username,logon_time,last_call_et,status ;

3. To Identify the Sql query being executed by the session,

  select sql_text from v$sqlarea where address=(select sql_address from v$session where sid=(SID gto from the previous query));

4. Use the below quereis to get Locked object details,

  select * from v$locked_object;

  select * from dba_objects where object_id in (select object_id from v$locked_object);
 

5. To Identify whether sessions are waiting for the locked object,

    select * from dba_waiters;

6. Inform the application team about the sessions and locked object details.

7.  We can kill the locking sessions once we get confirmation from the users.
  
   Alter system kill session ‘sid,serial#’;

Scenario 2:

  Users are complaining that particular activity is slow.
 

1 .Login to the Database, Execute the below query to get particular session details,
           
select  SID,serial#,username|| ‘   ‘ ||status|| ‘          ‘ ||
to_char(logon_time,’DD/MM/YYYY HH24:MI:SS’)|| ‘   ‘ ||
last_Call_et || ‘         ‘ || machine,program,osuser from v$session
order by username,logon_time,last_call_et,status ;

2. Enable tracing for the Sid, serial# got from the previous query,

  DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION   (SID,SERIAL#,TRUE);

3.  Inform the user to do the same activity again.

4. If you are not able to identify the session details, we can enable the tracing at database level.

    In the Init parameter file,

          SQL_TRACE=TRUE;

4. Using the TKPROF utility we can get query details from the trace file.

  $TKPROF ora53269.trc ora53269.prf

Sample output from TKPROF is as follows:

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

call   count      cpu    elapsed     disk    query current    rows
—- ——-  ——-  ——— ——– ——– ——-  ——
Parse      1     0.16      0.29         3       13       0       0
Execute    1     0.00      0.00         0        0       0       0
Fetch      1     0.03      0.26         2        2       4      14
 
Misses in library cache during parse: 1
Parsing user id: (8) SCOTT
Rows     Execution Plan
——-  —————————————————
14  MERGE JOIN
 4   SORT JOIN
 4     TABLE ACCESS (FULL) OF ‘DEPT’
14    SORT JOIN
14      TABLE ACCESS (FULL) OF ‘EMP’
For this statement, TKPROF output includes the following information:
• The text of the SQL statement
• The SQL Trace statistics in tabular form
• The number of library cache misses for the parsing and execution of the statement.
• The user initially parsing the statement.
• The execution plan generated by EXPLAIN PLAN.

5. Inform the application team about the query and work with them to identify the bottleneck.
Some useful queries:
——————–

1.To identify INACTIVE sessions;

select SID,serial#,username|| ‘  ’ ||status|| ‘   ’ ||
to_char(logon_time,’DD/MM/YYYY HH24:MI:SS’)|| ‘  ’ ||
last_Call_et || ‘  ’ || machine from v$session
where type <> ‘BACKGROUND’ and status=’INACTIVE’
      order by username,logon_time,last_call_et,status ;

2. To identify who are all the sessions accessing particular object,

select * from v$access
where object like     (’OBJECT NAME’) ;
   

 3. To identify the locked object’s session details,

select s.sid, s.serial#, s.username,s.program,s.machine,s.command,p.spid
from v$session s, v$process p
where s.paddr = p.addr and s.sid in (select SESSION_ID from V$LOCKED_OBJECT);

4. To get Temp tablespace usage details,

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM   V$temp_space_header GROUP  BY tablespace_name;

5. To find Inactive sessions which is running more than 8 hrs,

select substr(s.sid,1,8),substr(s.process,1,8)PROCESS,substr(p.spid,1,8)SPID,
substr(s.program,1,30)PROGRAM,substr(round(sw.seconds_in_wait/3600,2),1,5) HRS_WAIT from v$session s,v$session_wait sw,V$process p
where s.paddr=p.addr and s.sid=sw.sid and sw.state=’WAITING’ and s.status=’INACTIVE’and round(sw .seconds_in_wait/3600,2) > 8 order by substr(round(sw.seconds_in_wait/3600,2),1,5)  desc;

6. To identify how much work is completed by a session,

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) “%COMPLETED”,
100-ROUND(SOFAR/TOTALWORK*100,2) “%LEFT”
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;