>> command on Dev (Surprisingly works fine, but in prod give's an error)
Good Clue!Is DEV smaller?
Does DEV have less active users?
Did you open an SR to check for a bug? The "update global indexes" can cause an insert statement waiting to complete this task on " cursor: pin S wait on X ".
It's a semaphore (mutex) issue, an exclusive lock . . .
Check out V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY for more details . . .
The Oracle docs note that "cursor: pin S wait on X" is when a session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.
P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps
See MOSC Note: 401435.1 and bug 5907779 - Self deadlock hang on "cursor: pin S wait on X"