Database Tuning (MOSC)

MOSC Banner

Create index online cause concurrency waits cursor: pin S wait on X on selects

edited Apr 3, 2017 9:53AM in Database Tuning (MOSC) 8 commentsAnswered ✓

Hi masters,

On 11gR2 db when we execute this statment:

ALTER TABLE GE_FRAUD_LIST ADD CONSTRAINT IDX_GE_FRAUD_LIST_ID PRIMARY KEY (ID)

USING INDEX TABLESPACE EDR_INDEX ONLINE;

 

and sessions that are executing queries like this..

SELECT id, fraud_level

FROM GE_FRAUD_LIST

WHERE buyer_phone = :1 AND active = 'T' AND upper(buyer_zipcode) = :2

 

have concurrency waits with exclusive lock!

I thought that Oracle create an IOT table and use an internal trigger to keep track of the DML changes and after apply all DML changes to the target..

Why add constraint online cause concurrency waits: cursor: pin S wait on X?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center