Production Database Slowness Due To High Number Of Session With Wait Event On Shared Pool Latches (Doc ID 2439852.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Database hanging with cursor: pin S wait on X
There is high message 'Hard Parse Due to Parse Errors' in ADDM report:
Finding 1: Hard Parse Due to Parse Errors
Impact is 368.21 active sessions, 100% of total activity.
---------------------------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.
Diag file shows following message:
Verified Hangs in the System
Root Chain Total Hang
Hang Hang Inst Root #hung #hung Hang Hang Resolution
ID Type Status Num Sess Sess Sess Conf Span Action
----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
4022 HANG UNRSLVBL 2 1539 2 2 LOW LOCAL Unresolvable:User
Hang Ignored Reason: This hang is probably the result of a user application
problem. External invention is required. This hang will be ignored.
inst# SessId Ser# OSPID PrcNm Event
----- ------ ----- --------- ----- -----
2 5906 43725 7471632 FG enq: TX - row lock contention
2 1539 65113 27066854 FG library cache lock

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.