Troubleshooting queries on Oracle Database
Troubleshooting queries on Oracle Database
If there is any performance issue in the environment/application , need to do some troubleshooting steps on Database as given below to verify if there is any issue at DB level as a precautionary measure.
Set the time and timing and verify DB name
SQL> set time on
SQL> set timing on
07:26:44 SQL> show parameter db_name
db_name string ORCLP
Find out if any locked objects
Got the object_ids from dba_objects for some of the important objects.
07:26:48 SQL> select * from gv$locked_object where object_id=201979;
no rows selected
Elapsed: 00:00:00.03
07:26:53 SQL> select * from gv$locked_object where object_id=80229;
no rows selected
Elapsed: 00:00:00.02
Blocking sessions
07:26:58 SQL> select sid,serial#,inst_id,username,machine,sql_id,event,seconds_in_wait,final_blocking_session blocking_session,final_blocking_instance blocking_instance
from gv$session where sid in (select session_id from gv$locked_object);
no rows selected
Elapsed: 00:00:00.03
processes and sessions
07:27:03 SQL> select * from v$resource_limit where RESOURCE_NAME in (‘processes’,’sessions’);
processes 43 114 2000 2000
sessions 144 204 3024 3024
Elapsed: 00:00:00.00
INACTIVE Sessions
07:27:11 SQL> select count(*), machine,status,osuser,username from v$session where status=’INACTIVE’ group by machine,status,osuser,username;
36 APPs_Server01 INACTIVE apadm APPS_ADM
1 DB_Server INACTIVE oracle SYS
34 APPs_Server02 INACTIVE apadm APPS_ADM
Elapsed: 00:00:00.00
ACTIVE Sessions
07:27:17 SQL> select count(*), machine,status,osuser,username from v$session where status=’ACTIVE’ group by machine,status,osuser,username;
32 DB_Server ACTIVE oracle
1 APPs_Server02 ACTIVE apadm APPS_ADM
2 DB_Server ACTIVE oracle SYS
Elapsed: 00:00:00.01
07:27:24 SQL> alter session set nls_date_format=’YYYY-MM-DD HH24:MI:SS’;
Session altered.
Elapsed: 00:00:00.00
07:27:31 SQL> select machine,status,osuser,username,logon_time from v$session where status=’ACTIVE’ and machine=’APPs_Server02′;
APPs_Server02 ACTIVE apadm APPS_ADM 2016-08-17 03:17:18
Elapsed: 00:00:00.00
FRA destination
07:27:37 SQL> select name,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,SPACE_RECLAIMABLE/1024/1024/1024 from v$recovery_file_dest;
/u01/ORCLP/archives
500 177.873978 1.75776672
Elapsed: 00:00:00.02
Events
07:27:58 SQL> select event,count(1) from v$session group by event order by 2 desc;
SQL*Net message from client 100
rdbms ipc message 19
DIAG idle wait 2
smon timer 1
class slave wait 1
SQL*Net message to client 1
Space Manager: slave idle wait 1
Streams AQ: qmn coordinator idle wait 1
VKTM Logical Idle Wait 1
Streams AQ: waiting for time management or cleanup tasks 1
pmon timer 1
wait for unread message on broadcast channel 1
ASM background timer 1
Streams AQ: qmn slave idle wait 1
14 rows selected.
Elapsed: 00:00:00.01
Note:
- SQL*Net message from client means Oracle is just waiting on some work to do.
- SQL*Net message from client means that you have a session connected and Oracle is waiting for a command so it can do something.
Locked Objects
07:28:05 SQL> select * from gv$locked_object;
no rows selected
Elapsed: 00:00:00.02
07:28:11 SQL> select * from dba_dml_locks;
no rows selected
Elapsed: 00:00:00.09
LONGOPS query
07:28:22 SQL> SELECT SQL_ID, SID,ROUND(TIME_REMAINING /60,2)”time remaining(min)”,ROUND(ELAPSED_SECONDS/60,2)”elapsed_mins”,
SOFAR,TOTALWORK-SOFAR “Work Remaining”,SQL_PLAN_OPTIONS||’ ‘||SQL_PLAN_OPERATION “PLAN OPERATION”, MESSAGE
FROM gV$SESSION_LONGOPS WHERE TIME_REMAINING>0;
no rows selected
Elapsed: 00:00:00.01
Blocking sessions
07:29:56 SQL>
select sid,serial#,BLOCKING_INSTANCE,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,username,wait_class,SECONDS_IN_WAIT/60,sql_id,osuser
from v$session where BLOCKING_SESSION is not null order by BLOCKING_SESSION;
no rows selected
Elapsed: 00:00:00.00
07:30:28 SQL>
See also