| Wait event problem [message #420687] |
Tue, 01 September 2009 09:05  |
|
|
I have problem with enq: TM contention wait events.
From ASH report in Top Event P1/P2/P3 I find problematic object table T1. It has two columns C1 and C2. There are PK and FK constraints on these two columns.Table T1 has one UNIQUE INDEX - index_T1(C1, C2).
With this:
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 find unindexed Foreign Key constraints, and show me that no index on C2 column. Way this tall me that I don't have index on C2 column?
Can I create new index on this C2 column: CREATE INDEX index_name_c2 ON T1(C2) or to rebuild or drop and create again UNIQUE INDEX on C1 and C2 column.
If I create new index on column C2 can I have a problem with index_T1?
In ASH report in Top DB Objects I see another indexes and table with enq: TM contention event. Whey Oracle show me this objects?
|
|
|
|
|
|
|
|
|
|
| Re: Wait event problem [message #421188 is a reply to message #421121] |
Sat, 05 September 2009 05:33  |
|
|
In EM:
Instance Locks
Session ID SQL ID Lock Type Mode Held Mode Requested Object Type Object Name
---------------------------------------------------------------------------------------------------------
123 4r464t444 TM ROW EXCLUSIVE NONE TABLE T1
321 w384ji985 TM NONE ROW SHARE TABLE T1
Blocking Sessions
Username Sessions Blocked Wait Class Wait Event P1 P2 P3 Seconds in Wait
-----------------------------------------------------------------------------------------------------------
C555 27 User I/O db file sequential read C:\DATA1.DBF 23223 1 0
C100 24 Application enq: TM - contention 4321234123 54323 0 180
The P2 value from C100 username is the table T1.
|
|
|
|
| Re: Wait event problem [message #421197 is a reply to message #420687] |
Sat, 05 September 2009 10:13 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I see unformatted output without knowing what SQL produced it & without any possible explanation of why it might matter.
Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
What problem are you trying to solve?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
