enq: SS - contention" and "DFS lock handle" oracle event in Oracle 11g RAC

Issue Analysis on "enq: SS - contention" and "DFS lock handle" oracle event in Oracle 11g RAC

This enque is usually come for Sorting cases ( called Sort Segment issues).

In some cases you can see a "hang" situation when someone is modifying a file used for temp space.  IN these cases, the wait event for TEMP space will include:

-- This script was used to Find the blocking sessions

select INST_ID,
       sid,
       serial#,
       BLOCKING_SESSION,
       username,
       EVENT,
       status,
       BLOCKING_SESSION_STATUS "block_stat",
       program,
       sql_id
  from v$session a
 where BLOCKING_SESSION IS NOT NULL;

-- Some sample:

SID : 1712
EVENT enq: SS - contention
P1TEXT name|mode
P2TEXT tablespace #
P3TEXT dba
WAIT_CLASS# 0
WAIT_CLASS Other

-- This script was used to find the source of the TEMP usage, in this case, SS contention:

select distinct u.username,
                u.osuser,
                w.event,
                w.p2text as reason,
                ts.name as tablespace,
                nvl(ddf.file_name, dtf.file_name)
  from v$session_wait w, v$session u, v$tablespace ts
  left outer join dba_data_files ddf on ddf.tablespace_name = ts.name
  left outer join DBA_TEMP_FILES dtf on dtf.tablespace_name = ts.name
 where u.sid = w.sid
   and w.p2 = ts.TS#
   and w.event = 'enq: SS - contention';

-- Find block change tracking

select p1 "File #". p2 "Block #", p3 "Reason Code"
  from v$session_wait
 where event = 'xxx';

Note:

Next, you can find the source of the hanging contention.  Here is a complete article on getting the values from p1, p2 and p3.

P1-->The absolute file number for the data file involved in the wait.
P2-->The block number within the data file referenced in P1 that is being waited upon.
P3-->The reason code describing why the wait is occurring.


Solution I applied and working fine:

1) If you have 'n' nodes in RAC, then add 'n' number tempfiles for your TEMP tablespace.
2) Give suuficient space to tempfiles.
3) GTT use should be controlled in application.
4) Avoid un-necessary sorting, like if indexed column is used in as first in "select" and same is used by order by clause.

Background:- DFS Lock Handle

DFS stands for distributed file system is an ancient name, associated with cluster file system operations, in a Lock manager supplied by vendors in Oracle Parallel Server Environment (prior name for RAC). But, this wait event has morphed and is now associated with waits irrelevant to database files also.

This will occur in RAC environment, possible with sequences especially when you have sequences + cache + Ordered set.

Means created like this, create sequence s1 min value 1 cache 20 order;

As RAC is multi instance environment, the values of the sequences need to be synchronized as they are need to be ordered.

Showing a real-time issue:

For example consider this sequence of sessions and their possible waits while accessing sequence next value:-

Session 1 on node-A: nextval -> 1001 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval –> 1002
Session 1 on node-B: nextval -> 1003 (DFS Lock handle)
Session 1 on node-B: nextval –> 1004
Session 1 on node-A: nextval -> 1005 (DFS Lock handle)
Session 1 on node-A: nextval -> 1006 (more selects)
Session 1 on node-A: nextval –> 1998
Session 1 on node-B: nextval -> 1999 (DFS Lock handle)
Session 1 on node-B: nextval -> 2000 (CR read)

If you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence.

Solution( In case of high transaction based OLTP):

Create sequences with nocache.

enq: KO - fast object checkpoint - a described bug in Oracle

Bug fixes on wait even "enq: KO - fast object checkpoint"

I found "enq: KO - fast object checkpoint" wait event one of the production database. When I serach from the metalink, I found this is one of bug where are fixed in higher versions:

Bug in : 11.2.0.2 / 11.2.0.3 
-- Bug 16342845  Excessive CPU in DBW processes for fast object checkpoints

Fixed on : 

•12.2 (Future Release)
•12.1.0.2 (Server Patch Set)
•11.2.0.4 (Server Patch Set)


See the below Documents:

1) Doc ID 16463153.8
2) Doc ID 16342845.8
3) Doc ID 1377830.1

Symptoms:

•Excessive CPU Usage
•Performance Affected (General)
•Waits for "enq: KO - fast object checkpoint"


But you analyze the application code and find which procedure / query is causing the issue:

Reason:1 - Analyzing "enq: KO - fast object checkpoint" enque

It is normal you get this wait event and this "slow" as TRUNCATE is a heavy and complex operation due to the fact that Oracle must guarantee database consistency even if there is a crash during this operation.

The only action you can make is to truncate less.

(You can also put this table in recycle buffer cache knowing this will slow down queries.)

Reason:2 -  If Less DBWR process

As per your system configuration increase DB writers. Follow Oracle document.

Reason:3 - Give value to DB_CACHE_SIZE

Fix value for DB_CACHE_SIZE parameter. Usually give 25% to 30% of SGA to this parameter. Follow Oracle document.

Reason:4 - "none" may be in filesystemio_options

Change filesystemio_option parameter "none" to "ASYNCH" in case better storage configuration or "SETALL". Follow Oracle document.

Here are some parameters I have changed ( SGA set based on my database requirement and DB_WRITERs based on my avialble cores and transaction ratios)

filesystemio_options= none # Set to 'ASYNCH' ( set SETALL for local disks)
sga_max_size =10737418240 # set to 12G
sga_target= 8589934592 # set 12G
DB_CACHE_SIZE = # set to 4G
DB_WRITER_PROCESSES = 2 set to 3

and Set " DB_KEEP_CACHE_SIZE" value to a non-zero value ( as granule_size * cpu_count )

Note: DB_KEEP_CACHE_SIZE is a dynamic parameter, you can change online.

You can calculate the value for " DB_KEEP_CACHE_SIZE" as like following:
Example:
1) No. of CPUs in the system/ server ( from show parameter cpu)
Assume CPU count is =10

2) Granule size :
SQL> connect / as sysdba;
SQL> select name,bytes from V$SGAINFO where name='Granule Size';

NAME                                  BYTES
-------------------------------- ----------
Granule Size                       33554432

i.e., 33554432*10=335544320 to be set for DB_KEEP_CACHE_SIZE parameter.

If above steps not help you, you may follow below steps with the help of Oracle Support. Must be executed in complete off peak time.

 Check below kernel parameters in your linux environment. If any thing missing add it.

$cat /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768

Set the below value :

# ulimit -Hs 32768
-- Check the output
# ulimit -Hs
32768

-- If you are observing the same issue again, change the below parameter and fush buffer_cache and shared_pool. ( you may do at your own risk).

SQL> connect / as sysdba;
SQL> alter system set "_db_fast_obj_ckpt"=FALSE';
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> alter system flush shared_pool;

I hope this may help you.
Add your feedbacks....

Latches and Locks in Oracle

Latches in Oracle

There are lots of concepts about latches and locks in oracle. Few documents I read and and investigated in many production environments. 

About Latch in Oracle:

Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the SGA).  In simple terms latches prevent two processes from simultaneously updating - and possibly corrupting - the same area of the SGA.. It is low-level serialization mechanism.

For example, the data buffer latches (sometimes called LRU latches) ensure that Oracle processes are 'serialized', such that only one process may alter the data buffer address chain.  This twiddling of RAM addresses happens very fast (RAM speed is expressed in nanoseconds), yet busy Oracle databases may experience waits on these events.

In-other way we can say, Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code.  The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.

Contention on an LRU latch usually means that there is a RAM data block that is in high demand.  If a latch is not available a 'latch free miss' statistics is recorded.

When Latch will occur:

Oracle sessions need to update or read from the SGA for almost all database operations.  For instance:

1. When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU (Least Recently Used) chain.
2. When a session reads a block from the SGA, it will modify the LRU chain.
3. When a new SQL statement is parsed, it will be added to the library cache within the SGA.
4. As modifications are made to blocks, entries are placed in the redo buffer.
5.  The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”).
6.  The redo log writer writes entries from the redo buffer to the redo logs.
7.  Latches prevent any of these operations from colliding and possibly corrupting the SGA. 

How Latches will work:

Because the duration of operations against memory is very small (typically in the order of nanoseconds) and the frequency of latch requests very high, the latching mechanism needs to be very light-weight.   On most systems, a single machine instruction called “test and set” is used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it (by changing the value in the memory address).

If the latch is already in use, Oracle can assume that it will not be in use for long, so rather than go into a passive wait (e.g., relinquish the CPU and go to sleep) Oracle will retry the operation a number of times before giving up.  This algorithm is called acquiring a spin lock and the number of “spins” before sleeping is controlled by the Oracle initialization parameter “_spin_count”.

The first time the session fails to acquire the latch by spinning it will attempt to awaken after a millisecond or so.  Subsequent waits will increase in duration and in extreme circumstances may reach 100s of milliseconds.   In a system suffering from intense contention for latches, these waits will have a severe impact on response time and throughput.

Root causes of Latch contention:

The latches that most frequently affect performance are those protecting the buffer cache, areas of the shared pool and the redo buffer.

• Library cache and shared pool latches:  These latches protect the library cache in which sharable SQL is stored.  In a well defined application there should be little or no contention for these latches, but in an application that uses literals instead of bind variables (for instance “WHERE surname=’HARRISON’” rather that “WHERE surname=:surname”, library cache contention is common.
• Cache buffers chain latches: These latches are held when sessions read or write to buffers in the buffer cache. There are typically a very large number of these latches each of which protects only a handful of blocks. Contention on these latches is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).
• Redo copy/redo allocation latches:  These latches protect the redo log buffer, which buffers entries made to the redo log.   These latches were a significant problem in earlier versions of Oracle, but are rarely encountered today. 

Detecting/ Finding latch Contention:

Oracle’s wait interface makes it relatively easy to detect latch contention and – from 10g onwards – to accurately identify the specific latch involved.   In 10 and 11g, each latch has it’s own wait category if waits on the specific latch become significant then we can deduce a latch contention problem.

See more : Click here & here

Latch and Concurrency:

An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch.  Latches are a type of lightweight lock. Locks are serialization devices. Serialization devices inhibit concurrency.  To build applications that have the potential to scale, ones that can service 1 user as well as 1,000 or 10,000 users, the less latching we incur in our approaches, the better off will be.

·         You have to choose always an approach that takes longer to run on the wall clock but that uses 10 percent of the latches. We know that the approach that uses fewer latches will scale substantially better than the approach that uses more latches.  Latch contention increases statement execution time and decreases concurrency.

Latch and Queuing:

Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it.

Latch spinning occurs when a process repeatedly requests a latch in a loop, whereas 

Latch sleeping occurs when a process releases the CPU before renewing the latch request.

Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches.

Data Dictionary:

The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.

See more:  Click here & here also

User Action:

Determine which latch is causing the highest amount of contention.

To find the problem latches since database startup, run the following query:

SELECT n.name, l.sleeps

  FROM v$latch l, v$latchname n

  WHERE n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps;

To see latches that are currently a problem on the database run:

SELECT n.name, SUM(w.p3) Sleeps

  FROM V$SESSION_WAIT w, V$LATCHNAME n

 WHERE w.event = `latch free'

   AND w.p2 = n.latch#

 GROUP BY n.name;

Take action based on the latch with the highest number of sleeps.

Q&A:

How are latches different from locks, and how does a DBA learn about Oracle latch management?

Ans: Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code.  The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.

Difference between Latch and Lock?

Ans: Latches occur and removed automatically internally and fully managed by Oracle database. i.e., A latch is a low-level internal lock used by Oracle to protect memory structures. It may tell degrade of performance. Lock may created due to various reason. One of the major root cause is bad application code and user’s mistake. Read this document to clear the idea more.

Buffer busy waits & reverse index - Performance improvement tips & tricks

Buffer busy waits & reverse index - Performance improvement tips & tricks
-- What is Buffer busy waits?
-- How will reduce Buffer busy waits?
-- Reduce Buffer busy waits with implementing reverse index ( one factor)

From mail, I received one query "How can I decrease the buffer busy waits?". This query usually comes from new DBAs. Some cases it is most important question for all DBAs. We discuss how we gain performance using reverse index.

Buffer busy waits is normally due to waiting to get a clean buffer when the block buffer is dirty (and requires DBWR process to clean up) Increasing the size of buffer cache will help, until the cache is full of dirty buffers again. It will fix the problem only if the extra size of the cache, enables free buffers to be found long enough for the DBWR to clean up others.

Make sure there isn't any bad SQL running which is doing too much loading of data. i.e full table scans to update a single row etc.

If db block hit ratio is good and still u see high buffer busy waits , u need to dig little further:


1) Catch sqls at the same time when u see high buffer busy waits.

2) Also see in v$latch_children which of the cache buffer chains are highly used then others
Then try to locate the object/s which is/are highly used
thru x$bh.addr and v$latch_children.addr and then getting dba block address.

3) Also u should check if the number of waits on cache buffer chains is high, u may need to increase their number but generally is not reqd.

4) After u get the object , try to tune that object, by finding if storage parameters are ok or u may want to relocate it on to another datafile .

5) Also see any other waits which could relate to DBWR ,db block buffers like write complete waits, or DBWR dirty buffer inspected which may point to some I/O issue or less number of DBWRs .

Find following query outputs and analyse them:

SQL>
select * from v$waitstat where class ='data block';

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block            1991536    2307343

SQL>
select substr(event,1,25)"Event",Total_waits, Total_timeouts,Time_waited,Average_wait from v$system_event where event='buffer busy waits';

Event                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
----------------------------------------------------------------------------
buffer busy waits       1745        0             2355597       1349.91

Using reverse key indexes to solve buffer busy wait problems:

Buffer busy wait and related events can cripple performance of concurrent inserts. Bad in a single instance database, far worse in a RAC (think "gc buffer busy"). Often the problem is because of a primary key populated from a sequence. Reversing the index can fix this problem.

Contention for index blocks when inserting grows can cause an application to hang up completely. This is because with a b-tree index on a monotonically increasing key, even though there will never be row lock all the inserted keys are going onto the same block at the edge of the index. A reverse key index will fix this. If you index, for example, 19, 20 and 21 as themselves, all three keys will probably be in the same block of the index. Instead, you would index them as 91, 02, and 12. So consecutive values will not be adjacent in the index: they will be distributed across the whole width of the index. You could do this programmatically, but Oracle provides the reverse key index for exactly this purpose. Here's an example:

create table t1 (c1 number);
create index idx_normal on t1(c1);

create table t2 (c1 number);
create index idx_reverse on t2(c1) reverse;

Do some inserts with loop and check the following query and you can see the difference.

SQL>
select object_name,value 
from v$segment_statistics
where owner='APP' and object_type='INDEX' 
and statistic_name='buffer busy waits';


See sample output. ( I have tested in my database)

OBJECT_NAME                         VALUE
------------------------------ ----------
IDX_NORMAL                         161347
IDX_REVERSE                          8983

The reverse key index has reduced the buffer busy waits by around 95%. Impressed? I hope you are. This is even more significant in a RAC environment, where buffer busy wait is globalized.
I am not saying that all indexes should be reversed. You do need to understand your data and how it is being accessed. For example, a non-equality predicate on the key cannot use the index. But when would you use a non-equality predicate on a primary key? Probably, never. It is hard to find a reason for not reversing all your monotonically increasing keys.

Hope this will help !!!

v$session_wait Tips & Tricks

v$session_wait Tips - Analyzing waits during bottleneck situation

Analyzing real-time physical I/O waits is an important step in improving performance

what are in v$session_wait?

The v$session_wait view displays information about wait events for which active sessions are currently waiting. The following is the description of this view, and it contains some very useful columns, especially the P1 and P2 references to the objects associated with the wait events.

SQL> desc v$session_wait 

Name Null? Type
--------------------------- -------- ------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

Using v$session_wait, it is easy to interpret each wait event parameter using the corresponding descriptive text columns for that parameter. Also, wait class columns were added so that various wait events could be grouped into the related areas of processing such as network, application, idle, concurrency, etc.

This view provides the DBA with a dynamic snapshot of the wait event picture for specific sessions. Each wait event contains other parameters that provide additional information about the event. For example, if a particular session waits for a buffer busy waits event, the database object causing this wait event can easily be determined:

SQL> 
select username, event, p1, p2 from 
v$session_wait 
where sid = 74;

The output of this query for a particular session with SID 74 might look like this:

USERNAME    EVENT            SID P1 P2 
---------- ----------------- --- -- ---
HR         buffer busy waits 74  4  155

Columns P1 and P2 allow the DBA to determine file and block numbers that caused this wait event. The query below retrieves the object name that owns data block 155, the value of P2 above:

SQL> select segment_name,segment_type
from dba_extents
where file_id = 4 
and 155 between block_id and block_id + blocks – 1;

OR

SQL> select segment_name,segment_type
from dba_extents
where file_id = &file_id
and &Block_id between block_id and block_id + blocks – 1;

Note: Here you can enter values

If you are getting below error:
ERROR:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small

Solution:

Please refer my link....

Click Here

SEGMENT_NAME              SEGMENT_TYPE
------------------------------ ---------------
employee                                     TABLE

The above output shows that the table named orders caused this wait event, a very useful clue when tuning the SQL within this session. Also, see my notes on v$session_wait.

The ability to analyze and correct Oracle Database physical read wait events is critical in any tuning project. The majority of activity in a database involves reading data, so this type of tuning can have a huge, positive impact on performance.

System wait tuning has become very popular because it can show you those wait events that are the primary bottleneck for your system. Some experts like the 10046 wait event (level 8 and higher) analysis technique and Oracle MOSC now has an analysis tool called trcanlzr.sql to interpret bottlenecks via 10046 trace dumps. However, some Oracle professionals find dumps cumbersome and prefer to sample real-time wait events.

When doing wait analysis, it is critical to remember that all Oracle databases experience wait events, and that the presence of waits does not always indicate a problem. In fact, all well-tuned databases have some bottleneck. (For example, a computationally intensive database may be CPU-bound and a data warehouse may be bound by disk-read waits.) In theory, any Oracle database will run faster if access to hardware resources associated with waits is increased.

Finding the Contentions:

For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read, but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).

Same P1,P2,P3 means they are waiting on same file, same block and same number of blocks. But could be on different rows or same rows.

You need to query v$lock to find out more info.

The values represent:

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:
SQL>
select p1 "File#",p2 "Block#",p3 "ReasonCode"
from v$session_wait
where event = '&event_name';

You can trace P1 and P2 back to the specific table or index with these scripts:

If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:

SELECT relative_fno, owner, segment_name, segment_type 
FROM dba_extents 
WHERE file_id = &file 
AND &block BETWEEN block_id AND block_id + blocks - 1;

Verification via Event name:

Oracle 10g v $ session view different wait events corresponding p1, p2, p3 of meaning, we can not remember all waiting for an event corresponding p1, p2, p3 of meaning.

The meaning of each wait event corresponds know by querying the V $ EVENT_NAME p1, p2, p3 of
SQL> 
col name format a25; 
col p1 format a10; 
col p2 format a10; 
col p3 format a10; 
SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3 
2 FROM V$EVENT_NAME 
3 WHERE NAME = '&event_name'; 

The event_name input values: db file scattered read 
Original value of 3: WHERE NAME = '& event_name A' 
The new value 3: WHERE NAME = 'db file scattered read' 

The name P1 P2 P3 
-------------------------------------------------- -------- 
db file scattered read file # block # blocks 

file #: data file number 
Block #: starting block number 
blocks: to read the the the number of of the data block 

If you want to trace and analyze the session detail or query details to proceed further, the trace the query or session.

Click here to read more about SQL Trace and Oradebug.

Oracle Log File Sync Wait Event - a short analysis and troubleshoot methods

Oracle Log File Sync Wait Event 

The Oracle "log file sync" wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session. The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation of its completion back to the user session. The wait time includes the writing of the log buffer and the post, and is sometimes called "commit latency".

The P1 parameter in <View:V$SESSION_WAIT> is defined as follows for the log file sync wait event:

Note: P1 = buffer#

All changes up to this buffer number (in the log buffer) must be flushed to disk and the writes confirmed to ensure that the transaction is committed and will be kept on an instance crash. The wait is for LGWR to flush up to this buffer#.

Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written. DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait.


Root causes of ‘log file sync’ waits :

Root causes of ‘log file sync’, essentially boils down to few scenarios and following is not an exhaustive list, by any means!

1. LGWR is unable to complete writes fast enough for one of the following reasons:

(a) Disk I/O performance to log files is not good enough. Even though LGWR can use asynchronous I/O, redo log files are opened with DSYNC flag and buffers must be flushed to the disk (or at least, written to disk array cache in the case of SAN) before LGWR can mark commit as complete.

(b) LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve for CPU too. This will lead to slower response from LGWR, increasing ‘log file sync’ waits. After all, these system calls and I/O calls must use CPU. In this case, ‘log file sync’ is a secondary symptom and resolving root cause for high CPU usage will reduce ‘log file sync’ waits.

(c) Due to memory starvation issues, LGWR can be paged out. This can lead to slower response from LGWR too.

(d) LGWR is unable to complete writes fast enough due to file system or unix buffer cache limitations.

2. LGWR is unable to post the processes fast enough, due to excessive commits. It is quite possible that there is no starvation for cpu or memory and I/O performance is decent enough. Still, if there are excessive commits, then LGWR has to perform many writes/semctl calls and this can increase ‘log file sync’ waits. This can also result in sharp increase in redo wastage’ statistics’.

3. IMU undo/redo threads. With Private strands, a process can generate few Megabytes of redo before committing. LGWR must write generated redo so far and processes must wait for ‘log file sync’ waits, even if redo generated from other processes is small enough.

4. LGWR is suffering from other database contention such as enqueue waits or latch contention. For example, we have seen LGWR freeze due to CF enqueue contention. This is a possible scenario however unlikely.

5. Various bugs. Oh, yes, there are bugs introducing unnecessary ‘log file sync’ waits.

Note : 
    CF enqueue :  The CF enqueue is a Control File enqueue and happens during parallel access 6to the control files.  The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands.

Root cause analysis :

It is worthwhile to understand and identify root cause and resolve it.

1. First make sure, ‘log file sync’ event is indeed a major wait events. For example in the statspack report for 60 minutes below, ‘log file sync’ is indeed an issue. Why? Statspack is for 1800 seconds and there are 8 CPUs in the server. Approximately, available CPU seconds are 14,400 CPU seconds. There is just one database alone in this server and so, approximate CPU usage is 7034/14,400 : 50%

But, 27021 seconds were spent waiting. In average, 27021/3600=7.5 processes were waiting for ‘log file sync’ event. So, this is a major bottleneck for application scalability.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                      % Total
Event                                Waits          Time (s)       Ela Time
--------------------------- ------------ --------   --------
log file sync                     1,350,499     27,021     50.04
db file sequential read      1,299,154     13,633     25.25
CPU time                       7,034             13.03
io done                           3,487,217      3,225      5.97
latch free                        115,471         1,325      2.452.

Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is).

Find which "sid" is showing this wait event / causing issue with commit / rolback.

SQL> select sid, event, time_waited, time_waited_micro
from v$session_event where sid=3 order by 3;


   SID EVENT                          TIME_WAITED       TIME_WAITED_MICRO
------ ------------------------------ -----------         -----------------
..
     3 control file sequential read        237848              2378480750
     3 enqueue                             417032                    4170323279
     3 control file parallel write         706539                7065393146
     3 log file parallel write             768628                  7686282956
     3 io done                           40822748                   4.0823E+11
     3 rdbms ipc message                208478598          2.0848E+12

When LGWR is waiting ( using semtimedop call) for posts from the user sessions, that wait time is accounted as ‘rdbms ipc message’ event. This event, normally, can be ignored. Next highest waited event is ‘io done’ event. After submitting async I/O requests, LGWR waits until the I/O calls complete, since LGWR writes are done synchronous writes. [ asynchronous and synchronous are not contradictory terms when comes to I/O! Google it and there is enormous information about this already]


Reducing Oracle waits / wait times:

If a SQL statement is encountering a significant amount of total time for this event, the average wait time should be examined. If the average wait time is low, but the number of waits is high, then the application might be committing after every row, rather than batching COMMITs. Oracle applications can reduce this wait by committing after "n" rows so there are fewer distinct COMMIT operations. Each commit has to be confirmed to make sure the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can be very beneficial.

If the SQL statement is a SELECT statement, review the Oracle Auditing settings. If Auditing is enabled for SELECT statements, Oracle could be spending time writing and commit data to the AUDIT$ table.

If the average wait time is high, then examine the other log related waits for the session, to see where the session is spending most of its time. If a session continues to wait on the same buffer# then the SEQ# column of V$SESSION_WAIT should increment every second. If not then the local session has a problem with wait event timeouts. If the SEQ# column is incrementing then the blocking process is the LGWR process. Check to see what LGWR is waiting on as it may be stuck.


Some solution:

1) Reduce other I/O activity on the disks containing the redo logs, or use dedicated disks.
2) Try to reduce resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.
2) Alternate redo logs on different disks to minimize the effect of the archiver on the log writer.
3) Move the redo logs to faster disks or a faster I/O subsystem (for example, switch from RAID 5 to RAID 1).
4) Consider using raw devices (or simulated raw devices provided by disk vendors) to speed up the writes.
5) See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options in order to reduce the amount of redo being written.
6) See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
7) Check the size of the log buffer as it may be so large that LGWR is writing too many blocks at one time.

Log file sync wait event: other considerations:

There may be a problem with LGWR's ability to flush redo out quickly enough if Oracle "log file sync" waits are significant for the entire system. The overall wait time for "log file sync" can be broken down into several components. If the system still shows high "log file sync" wait times after completing the general tuning tips above, break down the total Oracle wait time into the individual components. Then, tune those components that take up the largest amount of time.

The "log file sync" wait event may be broken down into the following components:

1. Wakeup LGWR if idle
2. LGWR gathers the redo to be written and issues the I/O
3. Wait time for the log write I/O to complete
4. LGWR I/O post processing
5. LGWR posting the foreground/user session that the write has completed
6. Foreground/user session wakeup

Tune the system based on the "log file sync" component with the most wait time. Steps 2 and 3 are accumulated in the "redo write time" statistic. (i.e. as found under STATISICS section of Statspack) Step 3 is the "log file parallel write" wait event. (See Metalink Note 34583.1:"log file parallel write") Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take some time for the OS to schedule it to run.


Myth# User Action :

There are 3 main things you can do to help reduce waits on "log file sync":

1) Tune LGWR to get good throughput to disk.

• Do not put redo logs on RAID 5.
• Place log files on dedicated disks.
• Consider putting log files on striped disks.

2) If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.

3) Determine whether any activity can safely be done with NOLOGGING / UNRECOVERABLE options.

Thanks.....

Trouble shoot -- enq: TM - contention

Resolve  "enq: TM - contention " issues in Oracle

Recently, during  monitoring production system, I found "enq: TM - contention" oracle event. The blocked sessions were executing simple INSERT & UPDATE statements similar to:

INSERT INTO customer VALUES (:1, :2, :3);

Query to find blocking session details:

select sid,serial#,event, blocking_session, username,status,terminal,program,sql_id
from v$session
where BLOCKING_SESSION  IS NOT NULL;

About "enq: TM - contention" :

These kind of Waits i.e., enq: TM - contention indicate there are un-indexed foreign key constraints. Reviewing the CUSTOMER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed with development team and verified with DDL. We added the index on the column referencing the PRODUCT table and the problem was solved.

Finding the root cause of the enq: TM - contention wait event

Using the above query to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the CUSTOMER  table several times a week. As a result, rows from the CUSTOMER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.

Reproducing a typical problem that leads to this wait

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the same issue to see what happens under the covers. I first created a subset of the tables from this CUSTOMER and loaded them with sample data.

CREATE TABLE customer
( customer_id number(10) not null,
customer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT customer_pk PRIMARY KEY (customer_id)
);
INSERT INTO customer VALUES (1, 'customer 1', 'Contact 1');
INSERT INTO customer VALUES (2, 'customer 2', 'Contact 2');
COMMIT;

CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
customer_id number(10) not null,
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE );
INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;

I then executed statements similar to what we found at this customer:

User 1: DELETE customer WHERE customer_id = 1;
User 2: DELETE customer WHERE customer_id = 2;
User 3: INSERT INTO customer VALUES (5, 'customer 5', 'Contact 5');

Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

-- Find details of blocking sessions
sql>
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

-- Solution

Following along with the solution we used for our customer, we added an index for the foreign key constraint on the CUSTOMER table back to the PRODUCT table:

sql> CREATE INDEX idx_fk_customer ON product (customer_id);

When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Sample query to find unindexed foreign key constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

Thanks
Please feel free to post comments...

Fix high Oracle “cache buffer chain” & “Buffer Busy Waits” events

Trouble-shoot Oracle “cache buffer chain” & “Buffer Busy Waits” events

About Oracle “cache buffer chain” event:

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache.

Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table. The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is protected by a single child latch. Processes need to get the relevant latch to allow them to scan a hash chain for a buffer so that the linked list does not change underneath them.

Contention on this latch usually means that there is a block that is in great contention (known as a hot block). See the sample AWR report (fig-1) showing “cache buffer chain” wait event as top event.

Drilling & Solution:

The "cache buffer chain" latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a free-list shortage on an index or table segment header.
Query-1:
SQL> select count(*) child_count,
   sum(gets)   sum_gets,
   sum(misses) sum_misses,
   sum(sleeps) sum_sleeps
from gv$latch_children
where name = 'cache buffers chains';

Sample-output:

The first main type of latch that will be detailed for Oracle is called the buffer cache latch. The buffer cache latch family consists of two types of latches: the cache buffers chain latchand the other is the cache buffers LRU chain latch. First, take a look at the cache buffers chain latch. Cache buffers chain latches are acquired at the moment in time when a data block within the Oracle buffer cache is accessed by a process within Oracle. Usually latch contention for these buffer caches is due to poor disk I/O configuration. Reducing contention with these latches involves tuning the logical I/O for the associated SQL statements as well as the disk subsystem.

Another factor for latch contention with buffers chain latches could possibly be hot block contention. Oracle Metalink Note # 163424.1 has some useful tips on tuning and identifying hot blocks within the Oracle database environment.

The other buffer cache latch type is the cache buffers LRU chain latch. Whenever a new block enters the Oracle buffer cache within the SGA, this latch is acquired to allow block management in the Oracle SGA. Also, the latch is acquired when buffers are written back to disk such as when a scan is performed to move the LRU or least recently used chain of dirty blocks to flush out to disk from the buffer cache.

Query-2:

SQL> select inst_id,sid,event,p1,p2,p3,wait_class,seconds_in_wait
from gv$session_wait
where event = 'cache buffer chains';

The columns of the gv$session_wait view that are of particular interest for a buffer busy wait event are:

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

To find hot blocks:
Query-3:
select /*+ RULE */
   e.owner ||'.'|| e.segment_name segment_name,
   e.extent_id extent#,
   x.dbablk - e.block_id + 1 block#,
   x.tch,
   l.child#
from
   sys.v$latch_children l,
   sys.x$bh x,
   sys.dba_extents e
where
   x.hladdr = 'ADDR' and
   e.file_id = x.file# and
   x.hladdr = l.addr and
   x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc;

Note : If this query is not returning any row(s), then you don't have any hot blocks.

Most buffer cache waits can be fixed with additional freelists. But there are some limitations. If you observed very rarely then find what query is causing the issue. If required clear application sessions from the web/app layer for issuing user. I experienced with same.

About Oracle “Buffer Busy Waits” event:

This is the most common confounding wait event in Oracle. There are various kinds resolution methods for "buffer busy wait events". Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle AWR report, like this:

See the below part of AWR report. It is clearly showing

(Figure-1)

The main way to reduce the total I/O on the system is to reduce buffer busy waits. This can be possible by tuning the SQL queris to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes.  But remember adjusting the database writer or adding freelists to tables and indexes may have some limitations. Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists to a table and index, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), and, of course, and adding a missing index to reduce buffer touches.

In order to look at system-wide wait events, we can query the v$system_event performance view. This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

Run the Query-3 as shown above to find out the hot block.

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where COUNT is the sum of all waits for the class of block, and TIME is the sum of all wait times for that class:

select inst_id,EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT
from v$system_event a
where event in('buffer busy waits','free buffer waits');

output :

As for the workaround, the idea is to spread the hot blocks across multiple cache buffers chains latches. This can be done by relocating some of the rows in the hot blocks. The new blocks have different block addresses and, with any luck, they are hashed to buckets that are not covered by the same cache buffers chains latch. You can spread the blocks in a number of ways, including:

• Deleting and reinserting some of the rows by ROWID.
• Exporting the table, increasing the PCTFREE significantly, and importing the data. This minimizes the number of rows per block, spreading them over many blocks. Of course, this is at the expense of storage and full table scans operations will be slower.
• Minimizing the number of records per block in the table. This involves dumping a few data blocks to get an idea of the current number of rows per block. Refer to the “Data Block Dump” section in Appendix C for the syntax. The “nrow” in the trace file shows the number of rows per block. Export and truncate the table. Manually insert the number of rows that you determined is appropriate and then issue the ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK command. Truncate the table and import the data.
• For indexes, you can rebuild them with higher PCTFREE values, bearing in mind that this may increase the height of the index.
• Consider reducing the block size. You may move the table or recreate the index in a tablespace with an 8K block size. This too will negatively impact full table scans operations. Also, various block sizes increase management complexity.
• For other workarounds, if the database is on Oracle9i Database Release 2 or higher, you may consider increasing the _SPIN_COUNT value as discussed earlier. As a last resort, you may increase the number of hash buckets through the _DB_BLOCK_HASH_BUCKETS parameter. This practice is rarely necessary starting in Oracle8i Database. If you do this, make sure you provide a prime number—if you don’t, Oracle will round it up to the next highest prime number
• Finally, at last not in the list, re-organize tablespace in certain interval. i.e., when you have high water mark or high value for 'initial' extents for tables etc.

Please click here to gather more knowledge.

Oracle "Read by Other Session" Wait Event

Trouble-shoot Oracle "Read by Other Session" Wait Event
-- scope : Orcale 10g/11g

When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known, there are several alternative methods for solving the issue.

When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Finding the contentions
When a session is waiting on the "read by other session" event, an entry will be seen in the v$session_wait system view, which will give more information on the blocks being waited for:

-- Find BLOCKING_SESSION id
select INST_ID "Inst",sid,a.serial#,BLOCKING_SESSION "Block_ID",username,
status,program,sql_id,LOGON_TIME,BLOCKING_SESSION_STATUS "Block_status",EVENT
from gv$session a where BLOCKING_SESSION  IS NOT NULL;

--- Find file#
SELECT p1 "file#", p2 "block#", p3 "class#"
 FROM gv$session_wait
 WHERE event = 'read by other session';

If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:

SELECT relative_fno, owner, segment_name, segment_type
 FROM dba_extents
 WHERE file_id = &file
 AND &block BETWEEN block_id AND block_id + blocks - 1;

Eliminating contentions: - Solution

Depending on the Oracle database environment and specific performance situation the following variety of methods can be used to eliminate contention:

Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. If you are using separate storage / ASM, then tune the query will resolve the issue in max cases. If you are using native storage,  then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information you can obtain from the operating system.

Redistribute data from the hot blocks - Deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:

SELECT data_object_id
 FROM dba_objects
 WHERE owner='&owner' AND object_name='&object';

 SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid
 FROM dual;

 --rowid for the first row in the block

 SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid
 FROM dual;

 --rowid for the 500th row in the block

 SELECT <column_list>
 FROM <owner>.<segment_name>
 WHERE rowid BETWEEN <start_rowid> AND <end_rowid>;

Adjust PCTFREE and PCTUSED - Adjusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block. Adjusting the PCTUSED value for an object keeps that object from getting prematurely put back on the freelist.

Depending on the type of contention, adjusting these values could help distribute data among more blocks and reduce the hot block problem. Be careful to optimize these parameters so blocks do move in and out of the freelist too frequently.

Reduce the Block Size - This is very similar to adjusting the PCTFREE and PCTUSED parameters in that the goal is to reduce the amount of data stored within one block. In Oracle 9i and higher this can be achieved by storing the hot object in a tablespace with a smaller block size. In databases prior to Oracle 9i the entire database must be rebuilt with a smaller block size.

Optimize indexes - A low cardinality index has a relatively small number of unique values, e.g. a column containing state data with only 50 values. Similar to inefficient queries, the use of a low cardinality index could cause excessive number of blocks to be read into the buffer cache and cause premature aging out of "good" blocks.

Troubleshoot Oracle Event : 'switch logfile command'

Troubleshoot Oracle Event : 'switch logfile command'

When log switch will take more time in oracle database, you may observed oracle event "switch logfile command" which block the session for some time.


-- Basics Checks:
If native disk has I/O issue, you may found the above event also. So, change the 'log_archive_dest' location to high throughput performing I/O disk area and observe the issue again. If issue persists then do the following checks and fix the issue:

-- Check the following parameters :

SQL> show parameter disk_asynch_io;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io                       boolean     TRUE

SQL> show parameter filesystemio_options;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none


Solution :

'filesystemio_options' parameter value is showing as 'none'. Set this value as 'asynch' in RAC environments and 'SETALL' in non-ASM instances.

This parameter "FILESYSTEMIO_OPTIONS" controls which IO options are used.

"setall" Enables both ASYNC and DIRECT IO, hence can lead to faster writes and therefore better performance.

Sure you don't have said oracle event.
If any doubt, please feel free to post queries/comment/suggestions.

Troubleshoot Oracle Event : Library Cache: mutex X & High SQL version counts

-- Resolving Oracle Event : Library cache: mutex X
-- Resolving high version count ( bug in Oracle 11.2.0.1 / 11.2.0.2.0 - in RAC)

-- To find if high version count observed or not
Steps:
1) Take an AWR report
2) Go to : Main report --> SQL Statistics --> SQL ordered by Version Count
3) If counts are withing duble digit, then ok. If it is observed any kind of triple digit or more than 100, i.e., you are facing high version count for the queries.

-- What is SQL version count in Oracle?
when you issue a SQL statement, the database searches the library cache to find a cursor with matching SQL text. Then it can happen that even though the text matches, there are some other differences that prevent you from using existing cursor (e.g. different optimizer settings, different NLS settings, different permissions etc.). In such cases, a new child cursor is created. So basically child cursors are different versions of the same SQL statement.

If you have SQL statements with thousands of versions, this could mean a problem for your shared pool (child cursors taking up lots of space and causing fragmentation), as well as a potential for performance problems due to plan instability (if the same SQL text is parsed to a new plan every time, sooner or later it will be a bad plan). That's why AWR report has this list.

According to Oracle support, up to a couple of hundreds versions doesn't indicate a problem (cursor sharing mechanism isn't perfect), but when you have thousands or tens of thousands of versions, you should check your cursor sharing settings (first of all, CURSOR_SHARING parameter).

Click here to read More from Oracle Blog

Sample AWR report snap-shot when high version count observed:

-- Techical desrciption

According to notes 9282521.8 and 9239863.8 describing the patches, the enhancements should be used:
When there is true contention on a specific library cache object….

For example:- A package that is so hot (heavily accessed ) in library cache will be contended and the sessions appear to be waited on Library Cache: mutex X.

There are many bugs and cases appeared in metalink with mutexes where in the below case is just a one of them.

Disclaimer:- Do not test in production

The below script is just calling dbms_application_info package and when executed concurrently in many sessions it may cause the contention on library cache.

declare
i number;
begin
for i in 1..1000000
loop
   execute immediate ‘begin dbms_application_info.set_client_info(”mutex”);end;’;
end loop;
end;
/

As the sessions running, generate a awr report and you can see the Wait event library cache: mutex X in concurrency class.

So this is evident that you are having latch(mutex) issue.

How to overcome this.?

Oracle gives the ability to create a multiple clones of the hot objects in library cache and the sessions will access/use them individually rather contending for one.

Please note, its not pin (pin in the library cache), its marking the library cache object as hot to allow oracle to create multiple copies of the same.

Solution 1: Prior to 11gR2

a) Parameter "_kgl_hot_object_copies" controls the maximum number of copies.

b) Complementary parameter _kgl_debug marks hot library cache objects as a candidate for cloning.

Syntax of this parameter can be found in MOS descriptions of bugs 9684368, 11775293 and others. One form of such marking is

"_kgl_debug"="name=’schema=’ namespace= debug=33554432?

With our example the syntax would be,

SQL> alter system set "_kgl_debug"="name='DBMS_APPLICATION_INFO' schema='SYS' namespace=1 debug=33554432?, "name='DBMS_APPLICATION_INFO' schema='SYS' namespace=2 debug=33554432' scope=spfile;

SQL> alter system set "_kgl_hot_object_copies"= 255 scope=spfile;

Solution 2: 11gr2 onwards

dbms_shared_pool.markhot(
schema IN VARCHAR2,
objname IN VARCHAR2,
namespace IN NUMBER DEFAULT 1, — library cache namespace to search
global IN BOOLEAN DEFAULT TRUE); — If TRUE mark hot on all RAC instances

or

dbms_shared_pool.markhot(
hash IN VARCHAR2, — 16-byte hash value for the object
namespace IN NUMBER DEFAULT 1,
global IN BOOLEAN DEFAULT TRUE);

exec dbms_shared_pool.markhot(‘SYS’,’DBMS_APPLICATION_INFO’,1);
exec dbms_shared_pool.markhot(‘SYS’,’DBMS_APPLICATION_INFO’,2);
exec dbms_shared_pool.markhot(hash=>3222383532,NAMESPACE=>0);

The namespace can be found with the following query (Andrey.Nikolaev blog)

col name format a20
col cursor format a12 noprint
col type format a7
col LOCKED_TOTAL heading Locked format 99999
col PINNED_TOTAL heading Pinned format 99999999
col EXECUTIONS heading Executed format 99999999
col NAMESPACE heading Nsp format 999
set wrap on
set linesize 80
select *
  from (select case
                 when (kglhdadr = kglhdpar) then
                  'Parent'
                 else
                  'Child ' || kglobt09
               end cursor,
               kglhdadr ADDRESS,
               substr(kglnaobj, 1, 20) name,
               kglnahsh hash_value,
               kglobtyd type,
               kglobt23 LOCKED_TOTAL,
               kglobt24 PINNED_TOTAL,
               kglhdexc EXECUTIONS,
               kglhdnsp NAMESPACE
          from x$kglob
         order by kglobt24 desc)
 where rownum <= 10;

--- Found version count history

--set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;

-- Most effective and easy solution
Note : Bug fixed in Oracle 11.2.0.3

-- Check the parameter

SQL> show parameter optimizer_secure_view_merging;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_secure_view_merging boolean     TRUE


-- If TRUE, then chnage the below paramter:

sql> alter system set optimizer_secure_view_merging=FALSE;

-- Verified changed or not
SQL> show parameter optimizer_secure_view_merging;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_secure_view_merging boolean     FALSE

Sample AWR report snap-shot when high version minimized after changing the above parameter:

When I applied one of production database server where we had high version count issue, then amazingly it reduced. You can see the difference from both snap-shots.

Sure your version count will be minimized.