Day to Day performance monitoring and troubleshooting

select metric_name, value
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
'Database Wait Time Ratio') and
intsize_csec =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);


METRIC_NAME VALUE
Database Wait Time Ratio 11.454006523398
Database CPU Time Ratio 88.545993476602

-------------------------------------------------

Identifying SQL Statements with the Most Waits


select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from v$active_session_history ash,
v$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time

--------------------------------------------------

Session Waits

select event, count(*) from gv$session_wait
group by event;

EVENT COUNT(*)
SQL*Net message from client 944
Streams AQ: waiting for messages in the queue 3
wait for unread message on broadcast channel 9
ASM background timer 4
LNS ASYNC end of log 4
ges remote message 4
gcs remote message 12
rdbms ipc message 136
gc cr request 4
pmon timer 4
smon timer 4
Streams AQ: qmn slave idle wait 6
class slave wait 37
Space Manager: slave idle wait 6
GCR sleep 4
VKTM Logical Idle Wait 4
PX Deq: Execution Msg 4
Streams AQ: qmn coordinator idle wait 4
Streams AQ: waiting for time management or cleanup tasks 4
DIAG idle wait 8
PING 4
PX Deq: Execute Reply 1

---------------------------------------------------------------
By Wait Class

select wait_class, name from v$event_name where name LIKE 'enq%' and wait_class <> 'Other' order by wait_class


Current Waits Grouped by Class

select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
sum_waits
from v$system_wait_class
group by wait_class
order by 3 desc;

WAIT_CLASS SUM(TIME_WAITED) SUM_WAITS
Application 5114987325 1236.63268678519
Configuration 19206831 129.40341314864
Idle 127912730395 19.9353549521315
Administrative 3582 11.5176848874598
Commit 8132795 0.51095881296746
Scheduler 684 0.167318982387475
User I/O 5158294 0.0932240008372694
System I/O 23393149 0.0638575254188075
Cluster 16901345 0.035756175515619
Other 111387930 0.0324436090207606
Concurrency 14777738 0.0247518634871635
Network 17120600 0.00876380876777323

---------------------------------------------------------------
Further investigate Application waits 

select a.event, a.total_waits, a.time_waited, a.average_wait
from v$system_event a, v$event_name b, v$system_wait_class c
where a.event_id=b.event_id
and b.wait_class#=c.wait_class#
and c.wait_class in ('Application','Concurrency')
order by average_wait desc;

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
enq: TX - row lock contention 16045 564318935 35171.01
enq: TM - contention 3150991 4550552492 1444.17
Wait for Table Lock 28 2524 90.15
enq: UL - contention 1034 625 0.6
enq: RC - Result Cache: Contention 21677 3781 0.17
SQL*Net break/reset to client 585024 88464 0.15
enq: RO - fast object reuse 101104 6976 0.07
enq: KO - fast object checkpoint 258523 13470 0.05
enq: PW - flush prewarm buffers 1839 59 0.03

-------------------------------------------------------------------

Further Investigate sessions with row lock contention

select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from gv$session_event a, gv$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';

------------------------------------------------------------------
Finding who is holding locks


select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

------------------------------------
Identifying a Locked Object

select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User",
o.owner, o.object_name, o.object_type
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
order by o.object_id, 1 desc;

--------------------------------------
Resolving enq: TM Lock Contention

The enq: TM – contention event is usually due to missing foreign key constraints on a table that’s part of an Oracle DML operation. Once you fix the problem by adding the foreign key constraint to the relevant table, the enq: TM – contention event will go away.

Oracle takes out an exclusive lock on a child table if you don’t index the foreign key constraints in that table. To illustrate how an unindexed foreign key will result in contention due to locking, we use the following example. Create two tables, STORES and PRODUCTS, as shown here: 

SQL> create table stores
(store_id number(10) not null,
supplier_name varchar2(40) not null,
constraint stores_pk PRIMARY KEY (store_id));
SQL>create table products
(product_id number(10) not null,
product_name varchar2(30) not null,
supplier_id number(10) not null,
store_id number(10) not null,
constraint fk_stores
foreign key (store_id)
references stores(store_id)
on delete cascade);


If you now delete any rows in the STORES table, you’ll notice waits due to locking. You can get rid of these waits by simply creating an index on the column you’ve specified as the foreign key in the PRODUCTS table:

create index fk_stores on products(store_id);

----------------------------------------------------
You can find all unindexed foreign key constraints in your database by issuing the following query:

SQL> select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;

If you don’t index a foreign key column, you’ll notice the child table is often locked, thus leading to contention-related waits. Oracle recommends that you always index your foreign keys.

----------------------------------------------------------
Identifying Recently Locked Sessions

select to_char(h.sample_time, 'HH24:MI:SS') TIME,h.session_id,
decode(h.session_state, 'WAITING' ,h.event, h.session_state) STATE,
h.sql_id,
h.blocking_session BLOCKER
from v$active_session_history h, dba_users u
where u.user_id = h.user_id
and h.sample_time > SYSTIMESTAMP-(2/1440);

-------------------------------------------------------------
To find out which of your users experienced the most waits in the past 15 minutes, issue the following query:

SQL> select s.sid, s.username,
sum(a.wait_time +
a.time_waited) total_wait_time
from v$active_session_history a,
v$session s
where a.sample_time between sysdate – 30/2880 and sysdate
and a.session_id=s.sid
group by s.sid, s.username
order by total_wait_time desc;

-------------------------------------------------------------
Execute the following query to find out the objects with the highest waits.

SQL>select a.current_obj#, o.object_name, o.object_type, a.event,
sum(a.wait_time +
a.time_waited) total_wait_time
from v$active_session_history a,
dba_objects d
where a.sample_time between sysdate – 30/2880 and sysdate
and a.current_obj# = d.object_id
group by a.current_obj#, d.object_name, d.object_type, a.event
order by total_wait_time;

--------------------------------------------------------
You can identify the SQL statements that have been waiting the most during the last 15 minutes with this query.

SQL> select a.user_id,u.username,s.sql_text,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a,
v$sqlarea s,
dba_users u
where a.sample_time between sysdate – 30/2880 and sysdate
and a.sql_id = s.sql_id
and a.user_id = u.user_id
group by a.user_id,s.sql_text, u.username;