Home » RDBMS Server » Performance Tuning » Identify index key entry by rowid (Oracle SE 11.2.0.4.0)
Identify index key entry by rowid [message #659438] Tue, 17 January 2017 04:02 Go to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Hi, I got a question:

Is it possible to identify locked INDEX ENTRY using information from V$SESSION ( row_wait_obj#, row_wait_file#, row_wait_block# and row_wait_row#)?
It's easily done for table ( by using DBMS_ROWID package ), but what about index?

Oracle SE 11.2.0.4.0

Michael

[Updated on: Tue, 17 January 2017 04:03]

Report message to a moderator

Re: Identify index key entry by rowid [message #659439 is a reply to message #659438] Tue, 17 January 2017 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not possible.

Re: Identify index key entry by rowid [message #659440 is a reply to message #659438] Tue, 17 January 2017 06:35 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Thanks anyway

Michael
Re: Identify index key entry by rowid [message #659513 is a reply to message #659438] Thu, 19 January 2017 14:00 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
just 2 questions:

1. what exactly do you mean with identity of index entry?
2. what is the real issue? For what purpose do you need this?
Re: Identify index key entry by rowid [message #659548 is a reply to message #659513] Fri, 20 January 2017 14:38 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
I have an application with o lot of locking issues on index entries and I would like to identity these "hot" entries
Re: Identify index key entry by rowid [message #659550 is a reply to message #659548] Fri, 20 January 2017 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you know you have lock on index entries then you know which entries they are, doesn't you?

Re: Identify index key entry by rowid [message #659551 is a reply to message #659550] Fri, 20 January 2017 15:16 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
No, I don't. I can get the locking data: fileid, blockid and rowno. I would like to know the entry key: for example employee name.
Re: Identify index key entry by rowid [message #659552 is a reply to message #659548] Fri, 20 January 2017 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
michael_bialik wrote on Fri, 20 January 2017 12:38
I have an application with o lot of locking issues on index entries and I would like to identity these "hot" entries

post SQL & results that show above is true.

Are the locking issues on B-Tree index or on bit mapped index or both?

Locking on bit mapped INDEX is done at the block level, which can impact MANY rows; which is why bit mapped index is poor choice for OLTP application.
Re: Identify index key entry by rowid [message #659553 is a reply to message #659548] Fri, 20 January 2017 15:47 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
- do you mean enqueue waits on "enq: TX - index contention"?
- are there B-tree indexes?
- what operations cause these enqueue waits?
- what is with the statistics '%node%splits'?
Re: Identify index key entry by rowid [message #659606 is a reply to message #659553] Tue, 24 January 2017 01:11 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
I have a locking/blocking issues (from time to time). When blocking occurs I see (at V$SESSION view) values in following columns:
Row_Wait_Obj#
Row_Wait_File#
Row_Wait_BLock#
Row_Wait_Row#
If Row_Wait_Obj# points to table, I'm able using DBMS_ROWID package to identify the locked row and to retrieve it's data, however it's not possible when Row_Wait_Obj# point to index.
So I wandered if someone have any idea about it.

Michael

P.S. It's regular B-tree indexes

[Updated on: Tue, 24 January 2017 01:12]

Report message to a moderator

Re: Identify index key entry by rowid [message #659607 is a reply to message #659606] Tue, 24 January 2017 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you answer to the questions you have been asked?

Re: Identify index key entry by rowid [message #659608 is a reply to message #659607] Tue, 24 January 2017 03:57 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Right now I got an example(still for tables):
SELECT s.inst_id, s.sid, s.serial#, s.event#, s.event, s.blocking_instance blk_i, s.blocking_session blk_s,
row_wait_obj# obj, o.owner, o.object_name, o.object_type type,
s.row_wait_file# file, s.row_wait_block# block, s.row_wait_row# row
FROM gv$session s
LEFT JOIN dba_objects o ON o.object_id = s.row_wait_obj#
WHERE blocking_session IS NOT NULL

Inst_ID sid serial# event# event blk_i blk_s obj object_name type file block row
1 1753 17369 241 enq: TX - row lock contention 1 214 103409 RM_NUMERATORS TABLE 1024 12186676 1
1 1680 27371 241 enq: TX - row lock contention 1 214 103409 RM_NUMERATORS TABLE 1024 12186676 1
1 1087 58331 241 enq: TX - row lock contention 1 214 103409 RM_NUMERATORS TABLE 1024 12186676 1

And yes - I got "enq: TX - index contention" when indexes are involved (in that case I got an index name in object_name column and the same values for file/block/row columns )

[Updated on: Tue, 24 January 2017 04:03]

Report message to a moderator

Re: Identify index key entry by rowid [message #659609 is a reply to message #659608] Tue, 24 January 2017 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Not formatted
2/ "And yes - I got "enq: TX - index contention" when indexes are involved" so what is the purpose of your "example" which does not show that?
3/ You didn't answer ALL the questions.

Re: Identify index key entry by rowid [message #659611 is a reply to message #659609] Tue, 24 January 2017 04:04 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Forget about it
Re: Identify index key entry by rowid [message #659614 is a reply to message #659611] Tue, 24 January 2017 04:51 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure, I will forget the whole topic that goes nowhere.

Previous Topic: Tuning update query - for more than 3 billion rows
Next Topic: TOP_LEVEL_SQL_ID
Goto Forum:
  


Current Time: Thu Mar 28 07:47:48 CDT 2024