Convert hexadecimal to decimal
I recently needed to do analyze a database for TM contention I was having in an Oracle database that I support. First, I had to obtain the hex value from the trace file generated by the deadlock in the oracle database:
example:
*** 2011-04-05 12:59:41.511
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-000156cc-00000000 58 323 SX 129 499 S
TM-00001ad3-00000000 129 499 SX 58 323 SX SSX
session 323: DID 0001-003A-0000727C session 499: DID 0001-0081-0000845F
session 499: DID 0001-0081-0000845F session 323: DID 0001-003A-0000727C
Above, the hexadecimal values are 000156cc and 00001ad3. Run the following SQL statement in the oracle database in question to obtain object information. Then address the issue accordingly. Usually for the wait event TM contention, there are missing foreign key indexes for the child tables. Add the missing foreign key indexes and your issues should cease.
select owner,object_name,object_type from dba_objects where object_id=(select to_number('&HEX_VALUE', 'XXXXXXXX') from dual);
example:
*** 2011-04-05 12:59:41.511
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-000156cc-00000000 58 323 SX 129 499 S
TM-00001ad3-00000000 129 499 SX 58 323 SX SSX
session 323: DID 0001-003A-0000727C session 499: DID 0001-0081-0000845F
session 499: DID 0001-0081-0000845F session 323: DID 0001-003A-0000727C
Above, the hexadecimal values are 000156cc and 00001ad3. Run the following SQL statement in the oracle database in question to obtain object information. Then address the issue accordingly. Usually for the wait event TM contention, there are missing foreign key indexes for the child tables. Add the missing foreign key indexes and your issues should cease.
select owner,object_name,object_type from dba_objects where object_id=(select to_number('&HEX_VALUE', 'XXXXXXXX') from dual);
Comments