You Asked

Hi ,

My question is about performance tuning , i faced issue where most of session was waiting on enq:tm contention
around 80%+ timing was utilize for this wait event and rest was enq:tx row lock contention

blocking session in my ash report was showing session id and in other column it was showing no detail found .

As per my understanding TM contention is caused by missing index on foreign key

however i am not able to found child table invole in query because sql statement was delete and contain one table in it .

please help me out to clear things and my facts for investigation is correct

Additionally i also request you to help me out for solution in such situation

and Connor said...

Unindexed foreign keys is *one* of possible causes for contention that manifests itself as TM/TX waits. There is also:

- two sessions writing the same unique key
- full blocks which cannot obtain more ITL entries
- bitmap indexes

so you need to look for multiple potential causes.

*Assuming* it is indeed a foreign key issue, you can run the following query to look for unindexed FK's in your schema

column columns format a20 word_wrapped 
column table_name format a30 word_wrapped 

select decode( b.table_name, NULL, '****', 'ok' ) Status, 
a.table_name, a.columns, b.columns 
from 
( select a.table_name, a.constraint_name, 
max(decode(position, 1, substr(column_name,1,30),NULL)) || 
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns 
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name 
and b.constraint_type = 'R' 
group by a.table_name, a.constraint_name ) a, 
( select table_name, index_name, 
max(decode(column_position, 1, substr(column_name,1,30),NULL)) || 
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns 
from user_ind_columns 
group by table_name, index_name ) b 
where a.table_name = b.table_name (+) 
and b.columns (+) like a.columns || '%' 
/

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 18, 2017 - 2:10 pm UTC

Hi Conner,

Thanks ,however I am not sure about my issue doesit it related to foreign key or not

Basically I just wanted to get different cause for this

I am still chashing out for the cause , strange thing I encounter is about ash report blocking session sid is capture but at rest of detail it wont able to get

August 19, 2017 - 2:01 am UTC

Dont forget that a *blocking* session does not have to be active, so is quite likely *not* in an ASH report, eg

Session 1
----------

SQL> create table t as select 1 x from dual;

Table created.

SQL> delete from t;

1 row deleted.

SQL>

This session is now idle...not doing anything, BUT has an open transaction.

Session 2
----------

SQL> delete from t;

[stuck]

So when I look at (for example) v$session

SQL> select * from v$session where status = 'ACTIVE' and event like 'enq%'
  2  @pr
==============================
SADDR                         : 00007FFDA5DA57E0
SID                           : 24
SERIAL#                       : 36241
AUDSID                        : 4194573871
PADDR                         : 00007FFDA5B506C8
USER#                         : 107
USERNAME                      : MCDONAC
COMMAND                       : 7
OWNERID                       : 2147483644
TADDR                         : 00007FFD9D69EAB8
LOCKWAIT                      : 00007FFDA4A4D418
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 107
SCHEMANAME                    : MCDONAC
OSUSER                        : XPS13\hamcdc
PROCESS                       : 4552:5904
MACHINE                       : WORKGROUP\XPS13
PORT                          : 0
TERMINAL                      : XPS13
PROGRAM                       : sqlplus.exe
TYPE                          : USER
SQL_ADDRESS                   : 00007FFD7FFA1988
SQL_HASH_VALUE                : 1063131497
SQL_ID                        : 1hws8f0zpw6b9
SQL_CHILD_NUMBER              : 0
SQL_EXEC_START                : 19-AUG-17
SQL_EXEC_ID                   : 16777217
PREV_SQL_ADDR                 : 00007FFD9447CE48
PREV_HASH_VALUE               : 2979506080
PREV_SQL_ID                   : ghu850kstg9x0
PREV_CHILD_NUMBER             : 1
PREV_EXEC_START               : 19-AUG-17
PREV_EXEC_ID                  : 16777236
PLSQL_ENTRY_OBJECT_ID         :
PLSQL_ENTRY_SUBPROGRAM_ID     :
PLSQL_OBJECT_ID               :
PLSQL_SUBPROGRAM_ID           :
MODULE                        : SQL*Plus
MODULE_HASH                   : 3669949024
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 974409
ROW_WAIT_OBJ#                 : 139850
ROW_WAIT_FILE#                : 7
ROW_WAIT_BLOCK#               : 167603
ROW_WAIT_ROW#                 : 0
TOP_LEVEL_CALL#               : 94
LOGON_TIME                    : 19-AUG-17
LAST_CALL_ET                  : 109
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       : OTHER_GROUPS
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : ENABLED
PQ_STATUS                     : ENABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :
BLOCKING_SESSION_STATUS       : VALID
BLOCKING_INSTANCE             : 1
BLOCKING_SESSION              : 397
FINAL_BLOCKING_SESSION_STATUS : VALID
FINAL_BLOCKING_INSTANCE       : 1
FINAL_BLOCKING_SESSION        : 397
SEQ#                          : 49
EVENT#                        : 284
EVENT                         : enq: TX - row lock contention
P1TEXT                        : name|mode
P1                            : 1415053318
P1RAW                         : 0000000054580006
P2TEXT                        : usn<<16 | slot
P2                            : 262156
P2RAW                         : 000000000004000C
P3TEXT                        : sequence
P3                            : 19760
P3RAW                         : 0000000000004D30
WAIT_CLASS_ID                 : 4217450380
WAIT_CLASS#                   : 1
WAIT_CLASS                    : Application
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 108
STATE                         : WAITING
WAIT_TIME_MICRO               : 108393765
TIME_REMAINING_MICRO          : -1
TIME_SINCE_LAST_WAIT_MICRO    : 0
SERVICE_NAME                  : SYS$USERS
SQL_TRACE                     : DISABLED
SQL_TRACE_WAITS               : FALSE
SQL_TRACE_BINDS               : FALSE
SQL_TRACE_PLAN_STATS          : FIRST EXEC
SESSION_EDITION_ID            : 133
CREATOR_ADDR                  : 00007FFDA5B506C8
CREATOR_SERIAL#               : 193
ECID                          :
SQL_TRANSLATION_PROFILE_ID    : 0
PGA_TUNABLE_MEM               : 0
SHARD_DDL_STATUS              : DISABLED
CON_ID                        : 0
EXTERNAL_NAME                 : XPS13\hamcdc
PLSQL_DEBUGGER_CONNECTED      : FALSE

PL/SQL procedure successfully completed.

you can see that session is stuck on 'enq: TX - row lock contention'. But even though session 1 is idle, you once you know the blocking session ID you can can still query v$session to see what it is (module, program, user etc).

You could also check out Resource Manager for this

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11842

which lets you cancel sessions like session 1 above, that are idle but are blocking people.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer