OraFAQ Forum: Performance Tuning » Identify index key entry by rowid

Home » RDBMS Server » Performance Tuning » Identify index key entry by rowid (Oracle SE 11.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Identify index key entry by rowid [message #659438]

Tue, 17 January 2017 04:02

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Not possible.

Report message to a moderator

Re: Identify index key entry by rowid [message #659440 is a reply to message #659438]

Tue, 17 January 2017 06:35

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Thanks anyway

Michael

Report message to a moderator

Re: Identify index key entry by rowid [message #659513 is a reply to message #659438]

Thu, 19 January 2017 14:00

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?

Report message to a moderator

Re: Identify index key entry by rowid [message #659548 is a reply to message #659513]

Fri, 20 January 2017 14:38

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

Report message to a moderator

Re: Identify index key entry by rowid [message #659550 is a reply to message #659548]

Fri, 20 January 2017 14:44

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Identify index key entry by rowid [message #659551 is a reply to message #659550]

Fri, 20 January 2017 15:16

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.

Report message to a moderator

Re: Identify index key entry by rowid [message #659552 is a reply to message #659548]

Fri, 20 January 2017 15:34

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.

Report message to a moderator

Re: Identify index key entry by rowid [message #659553 is a reply to message #659548]

Fri, 20 January 2017 15:47

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'?

Report message to a moderator

Re: Identify index key entry by rowid [message #659606 is a reply to message #659553]

Tue, 24 January 2017 01:11

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Identify index key entry by rowid [message #659608 is a reply to message #659607]

Tue, 24 January 2017 03:57

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Identify index key entry by rowid [message #659611 is a reply to message #659609]

Tue, 24 January 2017 04:04

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Forget about it

Report message to a moderator

Re: Identify index key entry by rowid [message #659614 is a reply to message #659611]

Tue, 24 January 2017 04:51

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:10:24 CST 2025