당신은 주제를 찾고 있습니까 “row cache lock – Oracle Database: Oracle Row Lock Contention“? 다음 카테고리의 웹사이트 Chewathai27.com/you 에서 귀하의 모든 질문에 답변해 드립니다: Chewathai27.com/you/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 Ni TSOH 이(가) 작성한 기사에는 조회수 957회 및 좋아요 60개 개의 좋아요가 있습니다.
The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.The solution to excessive row cache enqueue lock waits is: 1 – Serialize the DDL running on a small number of concurrent DDL tasks. 2 – If the row cache enqueue locks are for dc_sequences, consider using DBMS_SHARED_POOL. KEEP to pin your sequences.A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows.
Table of Contents
row cache lock 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 Oracle Database: Oracle Row Lock Contention – row cache lock 주제에 대한 세부정보를 참조하세요
Oracle Database Row Lock Contention
Oracle Row Lock Contention
Database row lock contention
LOCKS:
– Locks are used by Oracle databases to provide data concurrency and integrity between transactions.
– Locking within an Oracle database is an entirely automatic mechanism that prevent destructive interaction
between transactions accessing the same resource
– There are several levels to locking, but the Oracle Database automatically uses the lowest applicable level of
restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity
Consistency and concurrency
– Data concurrency means that many users can access data at the same time.
– Data consistency means that each user sees a consistent view of the data,
including visible changes made by the user’s own transactions and transactions of other users.
As DBA’s, we need to have a good understand of what happens when transactions are issued against the database.
ACID Model:
– A transaction is a logical, atomic unit of work that contains one or more SQL statements.
– A transaction groups SQL statements so that they are either all committed, which means they are applied to
the database, or all rolled back, which means they are undone from the database.
– Oracle Database assigns every transaction a unique identifier called a transaction ID.
– A transaction can either be a DDL or a DML
A Transaction ends when on of the following occurs:
– A user runs a DDL command (Implicit commit)
– A user issues a COMMIT or ROLLBACK
– A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed (graceful shutdown)
– A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment (abort)
To ensure consistency and concurrency, the database uses the ACID model
ACID is an acronym for the following 4 key properties that define a transaction:
A – Atomicity: The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.
C – Consistency: The transaction takes the resources from one consistent state to another.
I – Isolation: A transaction’s effect is not visible to other transactions until the transaction is committed.
The database must serialize concurrent access to data (Enqueue mechanism)
D – Durability: Changes made by the committed transaction are permanent and must survive system failure.
These 4 properties represent a set of database design principles that emphasize aspects of reliability
that are important for business data and mission-critical applications.
A TX lock therefore is acquired when a transaction initiates its first change and is held until the transaction
does a COMMIT or ROLLBACK.
It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete.
Oracle uses what we call an enqueue mechanism:
———————————————-
which is is a sophisticated locking mechanism that permits several concurrent processes to share known
resources to varying degrees.
Enqueues coordinate parallel access to Oracle resources such as objects or data records.
So When multiple transactions need to lock the same resource, the first transaction to request the lock obtains it,
This enqueue mechanism is automatic and requires no administrator interaction.
It is embedded withing the internals of the oracle architecture
There are different levels of locking which can either be automatic or manual but we will not talk about those in this demo
DEMO
#oracle #locks #enqueue #database #locking
Related Videos:
—————
Introduction to Structured Query Language SQL Oracle Database
https://youtu.be/ywWbkZwAtKE
Follow me:
———-
LinkedIn: https://www.linkedin.com/in/atsohmofor/
Twitter: https://twitter.com/Bindag
OR @Bindag
email: [email protected]
Telegram: NI TSOH
YouTube: https://www.youtube.com/channel/UCx06… OR NI TSOH
row cache lock 주제에 대한 자세한 내용은 여기를 참조하세요.
Row cache enqueue lock tips – Burleson Consulting
Answer: The row cache enqueue lock is caused by DDL locking portions of the data dictionary as you create a table/index of other DDL command. The row cache lock …
Source: www.dba-oracle.com
Date Published: 1/20/2021
View: 2963
Oracle Row Cache Lock Wait Event Explained – LogicalRead
The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as …
Source: logicalread.com
Date Published: 2/21/2021
View: 187
Wait event row cache lock – Database Adminstration
The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as …
Source: ora9212.wordpress.com
Date Published: 5/29/2021
View: 3504
Row Cache Lock waiting for the event – actorsfit
The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on …
Source: blog.actorsfit.com
Date Published: 7/21/2022
View: 9035
Row Cache Enqueues – Oracle Internals: An Introduction [Book]
The locks on the data dictionary rows themselves are called row cache enqueue locks. These locks are implemented in much the same way as general enqueue …
Source: www.oreilly.com
Date Published: 2/6/2021
View: 8377
row cache lock – Oracle Wait Event
The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is …
Source: databaseinternalmechanism.com
Date Published: 4/27/2021
View: 3598
Database hang and Row Cache Lock concurrency … – DaDBm
This row cache lock wait may occur similar during space management operations where tablespaces are fragmented or have inadequate extent sizes.
Source: www.dadbm.com
Date Published: 11/10/2022
View: 8083
주제와 관련된 이미지 row cache lock
주제와 관련된 더 많은 사진을 참조하십시오 Oracle Database: Oracle Row Lock Contention. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 row cache lock
- Author: Ni TSOH
- Views: 조회수 957회
- Likes: 좋아요 60개
- Date Published: 2022. 5. 19.
- Video Url link: https://www.youtube.com/watch?v=6GlzLpJblAU
How to solve row cache lock in oracle?
The solution to excessive row cache enqueue lock waits is: 1 – Serialize the DDL running on a small number of concurrent DDL tasks. 2 – If the row cache enqueue locks are for dc_sequences, consider using DBMS_SHARED_POOL. KEEP to pin your sequences.
What is row cache in Oracle?
A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows.
How do you fix latch row cache objects?
The row cache object latch can also indicate SQL with excessive hard parsing or excessive reliance on data dictionary information such as views row-level security, synonyms, etc. The general solution for row cache latch waits is to increase shared_pool_size.
What is Dc_sequences?
The dc_sequences Oracle metric is the maximum number of user sequences per session. In Oracle8 the maximum number of user sequences per session is specified by the initialization parameter DC_SEQUENCES.
What is library cache lock in Oracle?
The library cache lock controls the concurrency between clients of the library cache by acquiring a lock on the object handle so that one client can prevent other clients from accessing the same object or the client can maintain a dependency for a long time (no other client can change the object).
What is row cache in Cassandra?
Row caching
With row caching enabled, Cassandra will detect frequently accessed partitions and store rows of data into RAM to limit the cases where it needs to read from disk. This is a long time feature of Cassandra, but it receives some great optimizations in the upcoming 2.1 release.
What is library cache mutex?
My AWR report shows high counts for “library cache mutex x” events. Answer: Mutexes are objects that exist within the operating system to provide access to shared memory structures.
Resolving Issues Where ‘Row Cache Lock’ Waits are Occurring
Resolving Issues Where ‘Row Cache Lock’ Waits are Occurring (文档 ID 1476670.1)
Applies to:
Purpose
Oracle Database – Standard Edition – Version 10.2.0.1 and laterOracle Database – Enterprise Edition – Version 10.2.0.1 and laterOracle Database – Personal Edition – Version 10.2.0.1 and laterInformation in this document applies to any platform.
Disclaimer: This note is written in the context that the Performance Guided Resolution tool was used and recommended this article. It may not make as much sense if read standalone or by other audiences.
Troubleshooting Steps
Brief Definition:
The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.
Problem Confirmation:
Significant wait for latch: row cache objects
Slow overall performance with row cache lock
High CPU usage
Row Cache Lock
When DDLs execute, they must acquire locks on the row cache in order to access and change the Data Dictionary information. Once the locks are taken then they can be allowed to modify individual rows in the data dictionary.
Reducing Waits
1. The data dictionary resides in the shared pool. If the shared pool is not sized correctly then the data dictionary might not be fully cached. This should be handled automatically with the automatic shared memory tuning feature. The following documents provide more details:
2. Find which cache is being waited for:
SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event=’row cache lock’; P1TEXT P1 P2TEXT P2 P3TEXT P3 cache id 8 mode 0 request 3 SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8; PARAMETER COUNT GETS GETMISSES MODIFICATIONS DC_SEQUENCES 869 76843 508432 4500
In this example the cache is the “DC_SEQUENCES” cache.
3. Take cache dependent actions:
DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.
DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities
DC_SEGMENTS
Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.
DC_USERS
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.
DC_TABLESPACES
The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
4. For further information on row cache issues, review the following:
Document 278316.1 Troubleshooting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!”
Measuring Success
Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.
Known Issues
References
NOTE:34609.1 – WAITEVENT: “row cache lock” Reference Note
Row cache enqueue lock tips
Question: What are row cache enqueue locks? I see many row cache enqueue locks and I need to know how to remove this event?
Answer: The row cache enqueue lock is caused by DDL locking portions of the data dictionary as you create a table/index of other DDL command. The row cache lock escalates into the row cache enqueue lock when multiple DDL statements are waiting to execute. The row cache enqueue locks are allocated within the shared_pool_size region of the SGA.
If the lock wait exceed a predefined threshold, the DDL will abort and cause a trace file to be generated. This trace file will contain the string “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK”.
Note: Long-running DDL (alter table xxx move” is far more likely to generate a row cache enqueue lock than a fast-running DDL statement (e.g. truncate table).
The solution to excessive row cache enqueue lock waits is:
1 – Serialize the DDL running on a small number of concurrent DDL tasks.
2 – If the row cache enqueue locks are for dc_sequences, consider using DBMS_SHARED_POOL.KEEP to pin your sequences.
Specifically, the row cache enqueue lock is within the data dictionary files and they tend to be located in the dc_objects and dc_tablespaces.
You can run this script to see data dictionary row cache statistics by querying the v$rowcache view:
REM SET FLUSH OFF
REM SET TERM OFF
SET HEAD ON
SET PAGESIZE 59
SET LINESIZE 79
COLUMN parameter FORMAT A20
COLUMN type FORMAT a11
COLUMN percent FORMAT 999.99 HEADING “%”;
COLUMN gets FORMAT 999,999 HEADING ‘Gets’
COLUMN getmisses FORMAT 999,999 heading ‘Get|Misses’
COLUMN count FORMAT 999,999 heading ‘Count’
COLUMN usage FORMAT 999,999 HEADING ‘Usage’
ttitle “DATA DICTIONARY CACHE STATISTICS”
SPOOL rep_out/ddcache.lis
SELECT
parameter,
type,
gets,
getmisses,
( getmisses / gets * 100) percent,
count,
usage
FROM
v$rowcache
WHERE
gets > 100 AND
getmisses > 0
ORDER BY parameter;
Oracle Internals: An Introduction [Book]
Row Cache Enqueues
A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows. The need for data dictionary locking was introduced at the start of this chapter (see Section 4.1.3).
The locks on the data dictionary rows themselves are called row cache enqueue locks. These locks are implemented in much the same way as general enqueue locks. The cached data dictionary row acts as the resource structure, and enqueue lock structures are dynamically allocated from the shared pool as required. Locks can be requested, converted, and released, and requests can wait and time out, just like the general enqueue locks. However, row cache enqueue locks are not included in V$LOCK. In fact, they are not visible anywhere except in system and process state dumps.
Depending on the operation, some row cache enqueue locks are requested in no-wait mode and an ORA-54 error is returned if the lock is not immediately available. Otherwise, row cache lock requests are enqueued if necessary, and the process waits on a row cache lock wait. The parameters for this wait are shown in Table 4.4.
Table 4-4. Wait Parameters (row cache lock waits) Parameter Description p1 A number corresponding to the CACHE# column of V$ROWCACHE representing the data dictionary table for which a row lock is needed p2 The mode in which the lock is already held p3 The mode in which the lock is needed
The numeric codes used for the lock modes in the parameters for this wait are those for instance locks, rather than local locks, even when running single-instance Oracle. However, this wait is relatively rare in single-instance Oracle, resulting only from resource conflicts, whereas it is routine in parallel server because new lock requests must be socialized via the distributed lock manager.
Oracle does not expect row cache enqueue lock acquisitions and conversions to block for more than a few seconds. Therefore, row cache lock waits time out every 3 seconds, and if the lock has still not been acquired after 100 timeouts (5 minutes), an internal deadlock is assumed, and the operation is aborted. A message is written to the alert log saying that a process “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK,” and a process state dump is written to a trace file. Except for DDL against a long-running, in-use function, procedure, or package, this error should be treated as an Oracle bug and reported to Oracle Support.
Oracle row cache objects latch contention tips
O racle Metric row cache objects latch contention tips Oracle Consulting Tips by Burleson
The “row cache objects” is a serialization
The Row Cache Objects Latch is a correlated latch to the shared pool latch is the row cache objects latch. The purpose of this latch is to provide data protection for user processes that access the cached data dictionary area within Oracle. The Row Cache Objects Latch is a correlated latch to the shared pool latch is the row cache objects latch. The purpose of this latch is to provide data protection for user processes that access the cached data dictionary area within Oracle. latch that protects the access to the data dictionary cache in the SGA. This latch is acquired whenever you are referencing metadata objects in the data dictionary cache. In a nutshell, latch contention for “row cache objects” always signals a dictionary contention issue, and the common solution is to increase the size of the shared pool via the shared_pool_size parameter. Excessive SQL parsing on objects public synonyms may cause this latch contention and that library cache latches always precede row cache objects latch contention and A latch wait on a row cache object often means that there is a point of contention within the data dictionary. The row cache object latch can also indicate SQL with excessive hard parsing or excessive reliance on data dictionary information such as views row-level security, synonyms, etc. The general solution for row cache latch waits is to increase shared_pool_size. Get the Complete
Oracle SQL Tuning Information
The landmark book “Advanced Oracle SQL Tuning The Definitive Reference” is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
sequences tips
O racle dc_sequences Oracle Consulting Tips by Burleson
The dc_sequences Oracle metric is the maximum number of user sequences per session. In Oracle8 the maximum number of user sequences per session is specified by the initialization parameter DC_SEQUENCES.
If you are having difficulties regarding dc_sequences on v$rowcache. there isn’t anything you can do to increase memory specifically for the dictionary cache.
Dictionary cache RAM is dynamically tuned by oracle. However, you may want to look into using DBMS_SHARED_POOL to pin (keep) your sequence,
Also see my notes on Row cache enqueue locks
This, and many other Oracle performance metrics are discussed in my book “Oracle Tuning” by Rampant TechPress. You can buy it directly from the publisher and save 30% at this link:
Row cache enqueue lock tips
Question: What are row cache enqueue locks? I see many row cache enqueue locks and I need to know how to remove this event?
Answer: The row cache enqueue lock is caused by DDL locking portions of the data dictionary as you create a table/index of other DDL command. The row cache lock escalates into the row cache enqueue lock when multiple DDL statements are waiting to execute. The row cache enqueue locks are allocated within the shared_pool_size region of the SGA.
If the lock wait exceed a predefined threshold, the DDL will abort and cause a trace file to be generated. This trace file will contain the string “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK”.
Note: Long-running DDL (alter table xxx move” is far more likely to generate a row cache enqueue lock than a fast-running DDL statement (e.g. truncate table).
The solution to excessive row cache enqueue lock waits is:
1 – Serialize the DDL running on a small number of concurrent DDL tasks.
2 – If the row cache enqueue locks are for dc_sequences, consider using DBMS_SHARED_POOL.KEEP to pin your sequences.
Specifically, the row cache enqueue lock is within the data dictionary files and they tend to be located in the dc_objects and dc_tablespaces.
You can run this script to see data dictionary row cache statistics by querying the v$rowcache view:
REM SET FLUSH OFF
REM SET TERM OFF
SET HEAD ON
SET PAGESIZE 59
SET LINESIZE 79
COLUMN parameter FORMAT A20
COLUMN type FORMAT a11
COLUMN percent FORMAT 999.99 HEADING “%”;
COLUMN gets FORMAT 999,999 HEADING ‘Gets’
COLUMN getmisses FORMAT 999,999 heading ‘Get|Misses’
COLUMN count FORMAT 999,999 heading ‘Count’
COLUMN usage FORMAT 999,999 HEADING ‘Usage’
ttitle “DATA DICTIONARY CACHE STATISTICS”
SPOOL rep_out/ddcache.lis
SELECT
parameter,
type,
gets,
getmisses,
( getmisses / gets * 100) percent,
count,
usage
FROM
v$rowcache
WHERE
gets > 100 AND
getmisses > 0
ORDER BY parameter;
Oracle Row Cache Lock Wait Event Explained
In order for DDL (Data Definition Language) to execute, it must acquire a row cache lock to lock the data dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out, we see the row cache lock wait event.
A closer look at the row cache lock wait event
Each row cache lock will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache.
PARAMETER COUNT GETS GETMISSES MODIFICATIONS ——————— —– ———— ———- ————- dc_free_extents 0 0 0 0 dc_used_extents 0 0 0 0 dc_segments 5927 131379921 4142831 693734 dc_tablespaces 22 188609668 2436 0 dc_tablespace_quotas 12 22779303 3843 0 dc_files 0 165961 22493 21 dc_users 19 145681559 2078 21 dc_rollback_segments 67 3906307 66 232 dc_objects 1927 70725250 2247804 74803 dc_sequences 4 142714 1599 142714
Common row cache enqueue lock types
The tuning of the row cache lock wait is dependent upon the activity for each of the enqueue types. Of these, the most common are:
DC_SEQUENCES: this row cache lock wait may occur during the use of sequences. Tune by checking sequences to see if they have the cache option specified and if that cache value is reflective of the anticipated simultaneous inserts by the application.
DC_USED_EXTENTS and DC_FREE_EXTENTS: this row cache lock wait may occur during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented, extent sizes are too small, or tablespaces are managed manually.
DC_TABLESPACES: this row cache lock wait may occur during the allocation of new extents. If extent sizes are set too low the application may frequently request new extents which could cause contention. Tune by checking for rapidly increasing number of extents.
DC_OBJECTS: this row cache lock wait may occur during the recompilation of objects. If object compiles are occurring this can require an exclusive lock which will block other activity. Tune by examining invalid objects and dependencies.
Tuning for the row cache lock wait event
The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Checking activity within the V$ROWCACHE view is a good place to start for understanding this relationship, as tuning can only be accomplished with analysis of the enqueue type.
If a trace file is available you may also see the following error:
>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! << Also realize that the row cache lock wait event may appear more frequently when using RAC. This is because the library cache and the row cache are global in RAC—causing the row cache lock wait to be more pronounced.
Wait event row cache lock
In order for DDL (Data Definition Language) to execute, it must acquire a row cache lock to lock the data dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out, we see the row cache lock wait event.
select p1text,p1,p2text,p2,p3text,p3 from v$session where event = ‘row cache lock’; P1TEXT P1 P2TEXT P2 P3TEXT P3 ————– ——— ————– ——— ————– ———- cache id 13 mode 0 request 5 cache id 13 mode 0 request 5 cache id 13 mode 0 request 5 cache id 13 mode 0 request 5 cache id 13 mode 0 request 5 cache id 13 mode 0 request 5 cache id 13 mode 0 request 5
The tuning of the row cache lock wait is dependent upon the activity for each of the enqueue types. Of these, the most common are:
DC_SEQUENCES: this row cache lock wait may occur during the use of sequences. Tune by checking sequences to see if they have the cache option specified and if that cache value is reflective of the anticipated simultaneous inserts by the application.
DC_USED_EXTENTS and DC_FREE_EXTENTS: this row cache lock wait may occur during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented, extent sizes are too small, or tablespaces are managed manually.
DC_TABLESPACES: this row cache lock wait may occur during the allocation of new extents. If extent sizes are set too low the application may frequently request new extents which could cause contention. Tune by checking for rapidly increasing number of extents.
DC_OBJECTS: this row cache lock wait may occur during the recompilation of objects. If object compiles are occurring this can require an exclusive lock which will block other activity. Tune by examining invalid objects and dependencies.
check on V$ROWCACHE SQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13; PARAMETER COUNT GETS GETMISSES MODIFICATIONS ————————— ———- ———- ———- ————- dc_sequences 13 746449 210320 746449
The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Checking activity within the V$ROWCACHE view is a good place to start for understanding this relationship, as tuning can only be accomplished with analysis of the enqueue type.
If a trace file is available you may also see the following error:
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! << Finding Blockers: Holders and requesters can be seen in view X$KQRFP for parent objects, and X$KQRFS for subordinates. eg: The following select will show all holders of parent row cache objects so can be used to help find the blocking session. SELECT * FROM x$kqrfp WHERE kqrfpmod!=0; (KQRFPSES is the address of the holding session V$SESSION.SADDR)
Row Cache Lock waiting for the event
The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.
Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.
Look for any object compilation activity which might require an exclusive lock, blocking other activities
⑧ DC_AWR_CONTROL: This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for processes blocking these.
⑦ DB_ROLLBACK_SEGMENTS: This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. Remember that on a multi-node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required.
After 3 successive failures a sleep delay is introduced starting at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).
⑥ DC_USERS: Deadlock and resulting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.dc_users is and user error Password login related, In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay. This can lead to excessive row cache lock waits for DC_USERS for specific users/schemas.
⑤ DC_SEGMENTS: The row buffer queue lock will occur during segment allocation. Observe what the session holding the queue lock is doing. This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.
④ DC_OBJECTS: The line buffer queue lock will occur when the object is recompiled. When the object is compiled, it will apply for an exclusive lock to block other behaviors. Tuning by checking illegal objects and dependencies.
③ DC_TABLESPACES: The line buffer queue lock will occur when the new area is allocated. If the zone size is set too small, the program will often apply for new zones, which will cause conflicts. The tuning method is to rapidly increase the number of zones. Probably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
② DC_USED_EXTENTS and DC_FREE_EXTENTS: The row buffer queue lock may occur when space management encounters a table space split or insufficient area size. The tuning method is to check whether the table space is split, whether the area size is too small, or the table space is manually managed.
① DC_SEQUENCES: The row buffer queue lock will occur when the sequence is used. The tuning method is to check whether the sequence has a buffer option specified and make sure that the buffer value can withstand the expected concurrent insert operation. Check for appropriate caching of sequences for the application requirements.
The optimization of row buffer queue lock wait is based on the behavior of each queue lock type, among which the common ones are:
Need to check the Dictionary Cache Stats section in AWR to determine the problem.
Running DDL must request a row cache lock to lock the Data Dictionary information. The shared pool ( Shared Pool ) contains row buffers from the data dictionary, which can reduce disk I/O access and allow row locks. Data dictionary row locks are called Row Cache Enqueue Locks . The lock queue structure from the shared pool allocation request, and when these waiting times out requests will see the line buffer queue lock. The row cache lock wait event is a wait event related to the shared pool, which is caused by the access to the dictionary buffer. Each row buffer queue lock corresponds to a specific data dictionary object , which is called the queue lock type and can be found in the V$ROWCACHE view.
on behalf of a blank password); other types are likely to be
it may be caused by frequent user logins when the user password is wrong or empty, and then query and audit to determine (
④ The user password is wrong or an empty password is given and frequent logins
the expansion speed of the table space to keep up with the usage speed of the table space. This waiting event will occur
view. Need to check the Dictionary Cache Stats section in AWR to determine the problem. The common reasons are as follows:
wait event is a wait event related to the shared pool, which is caused by the access to the dictionary buffer. Each row buffer queue lock corresponds to a specific data dictionary object, which is called the queue lock type and can be found in the
Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.
DC_TABLESPACES The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
DC_USERS This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.
DC_SEGMENTS Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.
DC_OBJECTS Look for any object compilation activity which might require an exclusive lock, blocking other activities
In this example the cache is the “DC_SEQUENCES” cache.
1. The data dictionary resides in the shared pool. If the shared pool is not sized correctly then the data dictionary might not be fully cached. This should be handled automatically with the automatic shared memory tuning feature. The following documents provide more details:
When DDLs execute, they must acquire locks on the row cache in order to access and change the Data Dictionary information. Once the locks are taken then they can be allowed to modify individual rows in the data dictionary.
The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.
Disclaimer: This note is written in the context that the Performance Guided Resolution tool was used and recommended this article. It may not make as much sense if read standalone or by other audiences.
If a session times out when waiting for a row cache lock then it will report this to the alert log and tracefile with a message like:
In RAC the foreground will wait 60 seconds for the LCK0 to get the lock, the foreground will wait in infinite loop until the lock has been granted (LCK0 will notify foreground).
In a RAC environment the row cache locks use global enqueues of type ” Q[AZ] ” with the lock id being the hashed object name.
If running Real Application Clusters (RAC) then LCK0 is signalled to get the row cache lock for the foreground waiting on this event. The LCK0 process will get the lock asynchronously. In exclusive mode the foreground process will try to get the lock.
7.3-12.1This event is used to wait for a lock on a data dictionary cache specified by “cache id” (P1).
Troubleshooting: “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” (Document ID 2016422.1)
Document content
Applies to:
Oracle Database-Personal Edition-Version 8.0.6.0 and later
Oracle Database-Enterprise Edition-Version 8.0.6.0 and later
Oracle Database-Standard Edition-Version 8.0.6.0 and later
The information contained in this document applies to all platform
use
The purpose of this document is to help troubleshoot the cause “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!”
Troubleshooting steps
What is row cache enqueue lock (Row Cache Enqueue Lock)?
Row Cache or Data Dictionary Cache is the memory area of the shared pool that saves data dictionary information. Row cache saves data not in the form of data blocks, but in the form of rows. The row cache enqueue lock is the lock on the data dictionary row. This enqueue is about a specific data dictionary object. This is the so-called enqueue type and can be found in the view V$rowcache.
For a list of row cache types for each version, see:
Document 468334.1 How to Match a Row Cache Object Child Latch to its Row Cache
What does the “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” warning message mean?
When we try to acquire the row cache lock, this wait event will be used.
When a row cache conflict occurs, if the enqueue cannot be obtained within a predetermined time period, a trace file will be generated in the USER_DUMP_DEST or background_dump_dest directory, depending on whether the trace file is created by the user or a background process. alert.log usually updates the location of the warning message and tracking file accordingly.
The database detects that the core resource has been held for too long and notifies the administrator so that this situation can be resolved. This may also be accompanied by a hang or slow down of the database.
The alert.log messages and the generated trace files tend to contain messages:
> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< If the rowcache entry lock cannot be acquired immediately, then enter a loop, first release the row cache object latch, wait (waiting for the above waiting event), reacquire the latch, and then try to acquire the rowcache lock again. In single instance mode, it will repeat 1000 times until the process reports the error "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK". In the RAC environment, it will repeat until the instance lock cannot be obtained or is interrupted. Systemstate dump can provide some useful information to diagnose the cause of contention. Please note: The "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" will trigger this message when the threshold is reached, so it will not be triggered if the threshold is not reached. This means that for a less serious problem, even if it has the same cause, the message may not be output. "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" Possible cause SGA shrink (shrink)/resize operation (resize) If the SGA dynamically changes the size, it needs to hold various latches to avoid other processes operating at the same time until the operation is completed. If the resizing takes a while, or it happens frequently, you will see "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" happening. The way to locate this situation is that there are many'SGA: allocation forcing component growth' waiting events, or AWR's TOP list has similar waiting, and blocking waiting for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" The session is waiting' SGA: allocation forcing component growth' (or similar). There are some code fixes available, see: Bug 9267837-Auto-SGA policy may see larger resizes than needed Document 7189722.8 Bug 7189722-Frequent grow/shrink SGA resize operations Document 9267837.8 Bug 9267837-Auto-SGA policy may see larger resizes than needed row cache enqueue type For each enqueue type, there are corresponding operations that need to obtain such enqueue. The type of queue may give an indication of problems that may be caused by the operation. Some common reasons are as follows: The most likely reason is the allocation of new extents. If the extent size is set too small, then the application may continuously request new extents, which may cause contention. Do you have a very small extent size, a rapidly growing object? (You can locate them by looking for objects with a large number of extents). Check the traces of insert/update activities and find objects that have many extents. Check the size of the sequence cache used by the application: A session is performing GRANT on a user, and at the same time the user is logging in to the database. At this time, a deadlock may occur or cause "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!". Bug 6143420-Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"- fixed in 10.2.0.5 and 11.1.0.6 DC_OBJECTS Bug 12772404-Significant "row cache objects" latch contention when using VPD Document 4604972.8 Bug 4604972-Deadlock on dc_users by Concurrent Grant/Revoke-fixed in 11.1.0.6 Document 6143420.8 Bug 6143420-Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"- fixed in 10.2.0.5 and 11.1.0.6 DC_OBJECTS Document 12772404.8 Bug 12772404-Significant "row cache objects" latch contention when using VPD Document 11693365.8 Bug 11693365-Concurrent Drop table and Select on Reference constraint table hangs(deadlock)-fixed in 12.1 This is probably caused by the allocation of segments. Determine what the user holding the lock is doing and use errorstacks for diagnosis. This may be due to the allocation of rollback segments. Just like dc_segments, determine who holds the lock and collect errorstack for diagnosis. Keep in mind that on a multi-node system (RAC), the holder may be on another node, so systemstate of all nodes is required. Document 5756769.8 Bug 5756769-Deadlock between Create MVIEW and DML-fixed in 10.2.0.5 ,11.1.07 and 11.2.0.1 This enqueue is related to the control right of AWR (Automatic Workload Repository). Any operation that manipulates the AWR database will hold it. To analyze this problem, you need to find which processes are blocking them. RAC related bugs Bug 8666117-High row cache latch contention in RAC-fixed in 11.2.0.2 and 12.1 Bug 9866045-Long wait on'wait for master scn' in LCK causing long row cache lock waits-fixed in 12.1 Document 6004916.8 Bug 6004916-Hang involving row cache enqueues in RAC (ORA-4021)-fixed in 102.0.5 and 11.1.0.6 Document 8666117.8 Bug 8666117-High row cache latch contention in RAC-fixed in 11.2.0.2 and 12.1 Document 9866045.8 Bug 9866045-Long wait on'wait for master scn' in LCK causing long row cache lock waits-fixed in 12.1 What information can I collect to determine the cause? Systemstate dump When a problem occurs, the error will be recorded in alert.log, and a systemstate dump file will be automatically generated. Wed Sep 21 13:39:19 2011 > WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=37
System State dumped to trace file/oracle/diag/rdbms/…./.trc
AWR, ADDM and ASH reports
Collect two AWR reports, one for a problematic time period, and the other for a non-problem time period, because these can help us understand the status of the database when the problem occurs. AWR, ADDM, and ASH reports can learn from each other’s strengths, so as to be more complete Understand the whole problem.
Depending on the time interval of the AWR snapshot generation, collect reports with the smallest time interval. The default snapshot is an hour interval.
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql
Since analyzing systemstate is a very complicated matter, you can create a service request and upload alert.log, systemstate dump, and AWR reports before and when the problem occurs. Please ask Oracle technical support to analyze it.
How to analyze the collected diagnostic information?
Systemstate dump
Normally, row cache enqueue is part of a series of events, and the process that blocked the process applying for row cache enqueue is likely to be blocked by another process. Row cache enqueue is often the surface of the problem.
Systemstate dump can help find which row cache is being applied for, and may help find blocking processes.
Oracle process number: 77
Unix process pid: 10846, image: oracle@cpdb4532
*** 2011-05-13 08:08:58.775
*** SERVICE NAME:(ALFCMR_SERVICE) 2011-05-13 08:08:58.775
** * SESSION ID:(1076.796) 2011-05-13 08:08:58.775
> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< row cache enqueue: session: 0x1df57ade8, mode: N, request: S Example 1: The title of trace shows the following: The Oracle process ID (PID) waiting for the row cache enqueue lock (in this case, process 77). The mode of the row cache enqueue being applied for (request: S). Therefore, in the above example, process 77 is requesting to obtain row cache in shared mode (request: S). Systemstate contains the status information of each process in the database, so you can find this process in systemstate: 77 the PROCESS ---------------------------------------- . . ------ ---------------------------------- SO: 0x1cdf11958, type: 50, owner: 0x17d198288, flag: INIT/-/-/0x00 Row Cache the enqueue: the session. 1 COUNT = = = 0x1df57ade8 Object 0x1dc9a5d30, S = Request that the savepoint = 0x87b70d Row parent Object Cache: address = 0x1dc9a5d30 CID =. 7 (dc_users) . . From the above we see that process 77 requests the shared mode to obtain row cache dc_users. Process 77 is in the waiting state, which means it is blocked by other processes. We now need to check the systemstate to determine who holds the resources and blocked this process. Search for the referenced object, in this example, object=0x1dc9a5d30. After doing this, we find that process 218 is holding this object in exclusive mode: 218 the PROCESS: ---------------------------------------- . . SO: 0x1cdf118f8, type : 50, owner: 0x1ceb0f178, flag: INIT/-/-/0x00 row cache enqueue: count=1 session=0x1da54cf68 object=0x1dc9a5d30, request=X savepoint=0x11e row cache parent object: address=0x1dc9a5d30 cid=7(dc_users) Exclusive mode requests will always block shared mode requests until the process exclusive mode request is satisfied and the resource is released later. Therefore, this will prevent other sharing mode requests. Please note that this is requesting exclusive rather than exclusive holding, so this request must also be blocked. Looking at other processes, we see that process 164 is holding this object in shared mode (mode=s). 164 is the PROCESS: ---------------------------------------- . . O/S info: user: u1m, term:, ospid: 1234, machine: cpc44711 program: last wait for'SQL *Net message from client' blocking sess=0x(nil) seq=36289 wait_time=6943 seconds since wait started=2539 driver id=54435000 , # bytes =. 1, = 0 . . SO: 0x1cdf11418, type: 50, owner: 0x1ccc26120, In Flag: the INIT/-/-/0x00 Row Cache the enqueue: COUNT = 2 the session = 0x1df578318 Object = 0x1dc9a5d30, MODE = S that the savepoint = 0xb1bd8e row cache parent object: address=0x1dc9a5d30 cid=7(dc_users) hash=fc968070 typ=11 transaction=(nil) flags=00000002 own=0x1dc9a5e00[0x1cdf11448,0x1cdf11448] wat=0x1dc9a5e10[0x1cdf11928,0x17d5192e0] mode=S Therefore, the process 164 holds the row cache enqueue in the shared mode (mode=S), thereby preventing the process 218 from acquiring the row cache enqueue in the exclusive mode. In addition, we see that process 164 is on CPU (systemstate shows that the last wait is'SQL*Net message from client' instead of waiting for'SQL*Net message from client'). For further diagnosis, technical support needs to check the stack call to determine why this process is on the ON CPU and holds the queue for so long (has been waiting for 2539 seconds since the beginning). Example 2: In this example, process 18 (MMON) is waiting to obtain a row cache of type dc_awr_control in shared mode. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options ORACLE_HOME =/opt/oracle10/product/10.2.0 System name: SunOS Node name: saecopt51 Release: 5.10 Version: Generic_144488-04 Machine: sun4v instance name: PORT_V16 Redo the Thread Mounted by the this instance: 1 the Oracle Process Number The: 18 Unix Process pid: 6196, Image: the Oracle @ SAE (MMON) . . the PROCESS 18: - -------------------------------------- . . Last the wait for 'ksdxexeotherwait' WAIT_TIME = 0.000013 sec, seconds The Operating since the wait Started =. 6 . . SO: 39bf1f0e8, type: 50, owner: 3980783a0, In Flag: the INIT/-/-/0x00 Row Cache the enqueue: COUNT =. 1 the session = 3be37ea80 object=39a79f090, request=S savepoint=0x41f0ae row cache parent object: address=39a79f090 cid=22(dc_awr_control) hash=6f60197e typ=9 transaction=3bc39f560 flags=0000002a own=39a79f160[39bf1f178,39bf1f178] wat=39a79118 39bf1f118] = X-MODE . . The row cache lock of the object (object=39a79f090) is held by process 269 in exclusive mode (mode=x). The process is waiting for'SGA: allocation forcing component growth'. 269 the PROCESS: ---------------------------------------- . . Waiting for 'the SGA: Allocation Forcing Component Growth ' WAIT_TIME = 0, seconds The Operating since the wait Started =. 3 . . SO: 39bf1f148, type: 50, owner: 3bc39f560, In Flag: the INIT/-/-/0x00 Row Cache the enqueue: COUNT. 1 the session = 3be1b7c98 = Object = 39a79f090, mode=X savepoint=0x41efe8 row cache parent object: address=39a79f090 cid=22(dc_awr_control) hash=6f60197e typ=9 transaction=3bc39f560 flags=0000002a own=39a79f160[39bf1f178,39bf1f178] wat=39a79f118170,39bf1f178] wat=39a79 = X-MODE . . So the root cause is the size adjustment of the SGA, waiting for the row cache is a secondary result. We use the AWR report during the period to see related information: AWR report Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- SGA: allocation forcing compon 42,067,317 38,469 1 7.6 Other CPU time 2,796 0.6 db file sequential read 132,906 929 7 0.2 User I/O latch free 4,282,858 704 0 0.1 Other log file switch (checkpoint in 904 560 620 0.1 Configurat ------------------------------------------------------------- We can clearly see that in the Top 5 waiting events, there is a significant wait for this event in the entire system; and'SGA: allocation forcing component growth' is a major problem at this point in time. The root cause of the "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message is the memory adjustment activity. The TOP 5 wait events do not even show the symptoms of waiting for "row cache". If adjusting the memory size is not so serious, sometimes there is no "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message. -Mainly because the threshold mentioned before has not been reached. However, you may see other waiting events. A common example is outlined in the following literature: : pin S wait on Note:If adjusting the memory size is not so serious, sometimes there is no "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message. -Mainly because the threshold mentioned before has not been reached. However, you may see other waiting events. A common example is outlined in the following literature: Document 742599.1 High'cursor : pin S wait on X'and/or'library cache lock' Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity For frequent memory adjustments, there are several potential fixes available, see: Bug 9267837-Auto-SGA policy may see larger resizes than needed Document 7189722.8 Bug 7189722-Frequent grow/shrink SGA resize operations Document 9267837.8 Bug 9267837-Auto-SGA policy may see larger resizes than needed Possible problems with versions prior to 10g Versions before 10g have limited methods to detect row cache level deadlock. In order to minimize the possibility of deadlock, possible solutions: Set TIMED_STATISTICS=FALSE Set _row_cache_cursors=20 or more (default value 10) Don't do any tracing Troubleshoot other issues For troubleshooting of other performance issues, see: Bug 12772404-Significant "row cache objects" latch contention when using VPD (Doc ID 12772404.8) reference -GETTING ERROR'WAITED TOOL LONG FOR ROW CACHE ENQUEUE LOCK' -ROW CACHE DEADLOCK "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" -Bug 11693365-Concurrent Drop table and Select on Reference constraint table hangs (deadlock) * Troubleshooting Performance Issues -Init.ora Parameter "ROW_CACHE_CURSORS" Reference Note -RAC Hangs due to small cache size on SYS.AUDSES$ -Bug 4604972-Deadlock on dc_users by Concurrent Grant/Revoke -How to Match a Row Cache Object Child Latch to its Row Cache -Bug 5756769-Deadlock between Create MVIEW and DML -Bug 6004916-Hang involving row cache enqueues in RAC (ORA-4021) -Bug 6027068-Contention on ORA_TQ_BASE$ sequence -Bug 6143420-Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X" - : Pin S Wait On X','Library Cache Lock' And "Latch: Shared Pool" Waits due to Shared Pool/Buffer Cache Resize Activity -RAC and Sequences -Bug 8666117-High row cache latch contention in RAC -Bug 9866045-Long wait on'wait for master scn'in LCK causing long row cache lock waits -LCK0 PROCESS STUCK AT WAITING FOR "LATCH: ROW CACHE OBJECTS" preliminary knowledge: 1) row cache lock event -memory share pool is divided into library cache, dictionary cache; and row cache lock objects are distributed in the dictionary cache, because of It is caused by the access of the dictionary buffer; --This kind of resource competition belonging to the latch type is quite CPU-consuming. If the amount of concurrency is large, it is easy to shut down the machine. If you find that this wait is very high, it may generally be due to two reasons, one is sharing The pool is too small, and the shared pool needs to be increased. Another situation is that SQL analysis is too frequent and the concurrent access to the shared pool is too large. In any case, in most cases, increasing the shared pool will help reduce the wait, but you should also pay attention when increasing the shared pool, not necessarily increasing the shared pool in all cases will have a significant effect. Especially for the second case, accurate analysis is very important. In addition, further analysis to figure out which ROW CACHE waits the most will help solve the problem. The tuning of the row cache lock event is based on the behavior of each queue lock type. Common types of queue locks are: -------DC_SEQUENCES: The row buffer queue lock will occur when the sequence is used. The tuning method is to check whether the sequence has a buffer option specified and make sure that the buffer value can withstand the expected concurrent insert operation. Check for appropriate caching of sequences for the application requirements. -------DC_USED_EXTENTS and DC_FREE_EXTENTS: The row buffer queue lock may occur when space management encounters a table space split or there is not enough area size. The tuning method is to check whether the table space is split, whether the area size is too small, or the table space is manually managed. -------DC_TABLESPACES: The row buffer queue lock will occur when the new area is allocated. If the zone size is set too small, the program will often apply for new zones, which will cause conflicts. The tuning method is to rapidly increase the number of zones. Probably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents. -------DC_OBJECTS: The line buffer queue lock will be recompiled Occurs when the object. When the object is compiled, an exclusive lock will be applied to block other behaviors. Tuning by checking illegal objects and dependencies. -------DC_SEGMENTS: The row buffer queue lock will occur during segment allocation. Observe what the session holding this queue lock is doing. This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose. -------DC_USERS: Deadlock and resulting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database. -------DB_ROLLBACK_SEGMENTS: This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. Remember that on a multi-node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required. -------DC_AWR_CONTROL: This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for processes blocking these. 2) Description of related view fields: ROW CACHE LOCK basic description P1 – Cache Id P2 – Mode Held P3 – Mode Requested mode and REQUEST values: KQRMNULL 0 null mode – not locked KQRMS 3 share mode KQRMX 5 exclusive mode KQRMFAIL 10 fail to acquire instance lock 3) SQL query ----- Query row cache lock wait select * from v$session_wait where wait_class ='row cache lock'; find the value of p1 -----Query rowcache name to query according to the value of p1 select * from v$rowcache where cache# = &p1 ; Other: select event, p1 from v$session a where a.username is not null and a.status='ACTIVE'; 4) dba_hist_active_sess_history view dba_hist_active_sess_history view records the historical information of active sessions in memory, and dynamic performance view V$ACTIVE_SESSION_HISTORY The current active session information is recorded. Through the dba_hist_active_sess_history view associated v$sqlarea and DBA_HIST_SNAPSHOT can track the sql within a certain period of time. Of course, the amount of sql that can be tracked depends on v$sqlarea, after all, only the sql that remains in v$sqlarea can be tracked. The AWR report analysis database appeared on August 30 from 3 pm to 4 pm, so I pulled two AWR reports, one is from 3 pm to 4 pm on August 30, and the other is from 3 pm on 31st. At 4 o'clock, compare the two reports. The difference should be the problematic area. 1) It is found that among the top 5 foreground events, the first one is not DB CPU. By default, under normal circumstances, DB CPU will be ranked first. 2) Continue to look at the Wait Events Statistics and find that the top ranking is not the DB CPU, but the connection management call elapsed time. The name is related to the database connection. 3) Continue to compare and find that the Pct Miss of dc_users is relatively large, exceeding 20%. 4) View suspicious SQL statements: marked red SQL execution speed is very slow, some abnormal. SQL> select to_char(sample_time,’YY-MM-DD HH24:MI:SS’) sample_time,
2 instance_number,
3 sql_id,
4 P1,
5 event,
6 wait_class
7 from dba_hist_active_sess_history
8 where sample_time between
9 to_date(’16-08 -29 13:00:00′,’YY-MM-DD HH24:MI:SS’) and
10 to_date(’16-08-29 13:30:00′,’YY-MM-DD HH24:MI:SS ‘)
11 and sql_id in (‘bvtu633rnwrwv’,
12 ‘4a6uhr508t0p6′,
13’fpm2zazkfqhy6′,
14’bhrcaykh5tzsw’,
15 ‘9fxv1px768bd5’
SAMPLE_TIME INSTANCE_NUMBER SQL_ID P1 EVENT WAIT_CLASS
—————– ————— ———— ———– ——————— —————— —
16-08-29 13:00:59 1 4a6uhr508t0p6 10 row cache lock Concurrency
16-08-29 13:01:09 1 4a6uhr508t0p6 10 row cache lock Concurrency
16-08-29 13:01:19 1 4a6uhr508t0p6 10 row cache lock Concurrency
16-08-29 13:01:29 1 4a6uhr508t0p6 7 row cache lock Concurrency
16-08-29 13:01:39 1 bvtu633rnwrwv 10 row cache lock Concurrency
16-08-29 13:01:49 1 bvtu633rnwrwv 10 row cache lock Concurrency
16-08-29 13:01:59 1 bvtu633rnwrwv 10 row cache lock Concurrency
16-08-29 13:02:10 1 bvtu633rnwrwv 7 row cache lock Concurrency
16-08-29 13:02:20 1 fpm2zazkfqhy6 10 row cache lock Concurrency
16-08-29 13:02:30 1 fpm2zazkfqhy6 10 row cache lock Concurrency
16-08-29 13:02:40 1 fpm2zazkfqhy6 10 row cache lock Concurrency
16-08-29 13:02:41 2 bvtu633rnwrwv 7 row cache lock Concurrency
16-08-29 13:02:50 1 fpm2zazkfqhy6 7 row cache lock Concurrency
16-08-29 13:04:31 1 bhrcaykh5tzsw 10 row cache lock Concurrency
16-08-29 13:04:41 1 bhrcaykh5tzsw 10 row cache lock Concurrency
16-08-29 13:04:51 1 bhrcaykh5tzsw 7 row cache lock Concurrency
16-08-29 13:06:32 1 9fxv1px768bd5 10 row cache lock Concurrency
16-08-29 13:06:42 1 9fxv1px768bd5 10 row cache lock Concurrency
18 rows selected
According to the above sql, it is found that the waiting event caused by sql is row cache lock. Then according to P1=7 or 10, find out which category the waiting event occurred in
SQL> select cache#,parameter from v$rowcache where cache# in (‘7′,’10’);
CACHE# PARAMETER
— ——- ——————————–
10 dc_users
7 dc_users
7 dc_users
7 dc_users
According to the above sql, it is found that the parameter category is dc_users. On the Internet, it is said that dc_users is related to the user logging in with the wrong password:
In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay. This can lead to excessive row cache lock waits for DC_USERS for specific users/schemas.
…
After 3 successive failures a sleep delay is introduced starting at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).
Verify to see if row cache lock is occurring During this period, some users failed to log in.
SQL> select username,
2 userhost,
3 to_char(timestamp,’YY-MM-DD HH24:MI:SS’) timestamp,
4 action_name
5 from dba_audit_trail
6 where action_name =’LOGON’
7 and priv_used is null
8 and timestamp between
9 to_date(’16-08-29 13:00:00′,’YY-MM-DD HH24:MI:SS’) and
10 to_date(’16-08-29 13:30:00′,’YY-MM-DD HH24:MI:SS’) ;
USERNAME USERHOST TIMESTAMP ACTION_NAME
—————————— ————— —— —————– ————————— –
MAPP_PLATFORM IDC-APP-02 16-08-29 13:03:51 LOGON
MAPP_PLATFORM IDC-APP-01 16-08-29 13:03:41 LOGON
MAPP_PLATFORM IDC-APP-02 16-08-29 13:03:31 LOGON
MAPP_PLATFORM IDC-APP-01 16-08-29 13:03:21 LOGON
MAPP_PLATFORM IDC-APP-02 16-08-29 13:03:11 LOGON
MAPP_PLATFORM IDC-APP-01 16-08-29 13:03:01 LOGON
MAPP_PLATFORM IDC-APP-02 16-08-29 13:02:51 LOGON
MAPP_PLATFORM IDC-APP-01 16-08-29 13:02: 41 LOGON
MAPP_PLATFORM IDC-APP-02 16-08-29 13:02:31 LOGON
. . . .
It is found that the user MAPP_PLATFORM from 8.14 to 8.31 has been trying to log in to the database with the wrong password. It is verified that logging in with the wrong password will cause row cache lock and SQL execution slow.
Further query login audit information:
select * from
(
select os_username,userhost,terminal,username,count(*) failures
from dba_audit_trail
where returncode = 1017 and timestamp between to_date (‘2016-8-29 11:30:00′,’yyyy-mm-dd hh24:mi:ss’ ) and to_date (‘2016-8-29 13:30:00′,’yyyy-mm-dd hh24:mi:ss’)
group by os_username,userhost,username,terminal
order by 5 desc );
returncode = 1017 here 1017 is the error return code value defined internally by oracle.
In fact, no matter whether the password provided by the user is correct, Oracle will allocate a shadow process for the new connection. The service process has to obtain a small amount of resources in order to further verify the user information; if this is an opportunity, even in the case of unable to log in normally It can still cause instance deadlock in the short term.
Oracle Internals: An Introduction [Book]
Row Cache Enqueues
A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows. The need for data dictionary locking was introduced at the start of this chapter (see Section 4.1.3).
The locks on the data dictionary rows themselves are called row cache enqueue locks. These locks are implemented in much the same way as general enqueue locks. The cached data dictionary row acts as the resource structure, and enqueue lock structures are dynamically allocated from the shared pool as required. Locks can be requested, converted, and released, and requests can wait and time out, just like the general enqueue locks. However, row cache enqueue locks are not included in V$LOCK. In fact, they are not visible anywhere except in system and process state dumps.
Depending on the operation, some row cache enqueue locks are requested in no-wait mode and an ORA-54 error is returned if the lock is not immediately available. Otherwise, row cache lock requests are enqueued if necessary, and the process waits on a row cache lock wait. The parameters for this wait are shown in Table 4.4.
Table 4-4. Wait Parameters (row cache lock waits) Parameter Description p1 A number corresponding to the CACHE# column of V$ROWCACHE representing the data dictionary table for which a row lock is needed p2 The mode in which the lock is already held p3 The mode in which the lock is needed
The numeric codes used for the lock modes in the parameters for this wait are those for instance locks, rather than local locks, even when running single-instance Oracle. However, this wait is relatively rare in single-instance Oracle, resulting only from resource conflicts, whereas it is routine in parallel server because new lock requests must be socialized via the distributed lock manager.
Oracle does not expect row cache enqueue lock acquisitions and conversions to block for more than a few seconds. Therefore, row cache lock waits time out every 3 seconds, and if the lock has still not been acquired after 100 timeouts (5 minutes), an internal deadlock is assumed, and the operation is aborted. A message is written to the alert log saying that a process “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK,” and a process state dump is written to a trace file. Except for DDL against a long-running, in-use function, procedure, or package, this error should be treated as an Oracle bug and reported to Oracle Support.
row cache lock – Oracle Wait Event
The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information to reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occurring, or possibly recursive operations such as storage management and incrementing sequence numbers.
Use below query to find which cache (cache id) is being waited for:-
SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event=’row cache lock’; P1TEXT P1 P2TEXT P2 P3TEXT P3 cache id 8 mode 0 request 3 SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8; PARAMETER COUNT GETS GETMISSES MODIFICATIONS DC_SEQUENCES 869 76843 508432 4500
키워드에 대한 정보 row cache lock
다음은 Bing에서 row cache lock 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 Oracle Database: Oracle Row Lock Contention
- Oracle Database Row Lock Contention
- oracle row lock contention
- how to resolve oracle row lock contention
- enq: tx row lock contention
- oracle locking contention
- database row lock contention
- database locks
- oracle locks
- oracle deadlock
- killing blocking session oracle
- detecting blocking locks in oracle
- oracle row lock
- detecting oracle locks
- oracle enqueue
- oracle database
- locking
- detecting oracle locks with sql
- ni tsoh
- atsoh
- oracle
- oracle tutorial
- ora-00054
Oracle #Database: #Oracle # #Row #Lock #Contention
YouTube에서 row cache lock 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 Oracle Database: Oracle Row Lock Contention | row cache lock, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.
