Version: Oracle 10g
Hi I got a awr report with the below top 5 timed events , and I found that enq: TM-contention is happening on tables , but I want to know exactly which are those perticular tables in the
awr report that are giving this avg wait times in the below report.
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|
| enq: TM - contention | 39,056 | 114,630 | 2,935 | 84.4 | Application |
| CPU time | | 18,545 | | 13.7 | |
| db file sequential read | 682,834 | 772 | 1 | .6 | User I/O |
| log file parallel write | 54,636 | 358 | 7 | .3 | System I/O |
| log file sync | 35,167 | 272 | 8 | .2 | Commit |
When I try to find out in the schema that which are the tables does not have the index on the foreign keys then I found lot many tables,
with the below query around 1400 records, hence first I want to know which is the main culpript in my awr report which I took when the issue of slowness happend from DBA.
SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;
I am little new to understanding awr report , please help me out.