Home » RDBMS Server » Performance Tuning » Wait event problem (Oracle 10g)
Wait event problem [message #420687] Tue, 01 September 2009 09:05 Go to next message
Nikolin
Messages: 2
Registered: September 2009
Location: DDFDA
Junior Member

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 #420688 is a reply to message #420687] Tue, 01 September 2009 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Whey Oracle show me this objects?
When FK exists, but no index on column(s) involved, Oracle must lock whole child table when DML is done to parent table.
This is done to ensure FK is maintained as valid constraint.
Re: Wait event problem [message #421107 is a reply to message #420688] Fri, 04 September 2009 05:15 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
So why, if an index exists, oracle doesn'y need to lock the child table ?
Re: Wait event problem [message #421121 is a reply to message #421107] Fri, 04 September 2009 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it is sufficient to lock the index entry/entries.

Regards
Michel
Re: Wait event problem [message #421188 is a reply to message #421121] Sat, 05 September 2009 05:33 Go to previous messageGo to next message
Nikolin
Messages: 2
Registered: September 2009
Location: DDFDA
Junior Member

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 Go to previous message
BlackSwan
Messages: 25050
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?
Previous Topic: Query run faster without indexes. Why?
Next Topic: To limit Cpu consumption
Goto Forum:
  


Current Time: Sun Dec 11 08:09:21 CST 2016

Total time taken to generate the page: 0.14404 seconds