Oracle Point, Oracle Life.

There is a good posting by Tom at AskTom about concept of Latch and Latch Wait Time. Also, a followed posting mentioned a nasty buy about undocument parameter _spin_count, which controls the number of latch time and is typically set to 2000.

Upon this AskTom posting, we can do followings to check the event latch wait time on Oracle database (further, resolve it upon Oracle Support’s suggestion).

1. Run Script to present overall event statistics including “Latch Time”
Script to check “contention for CPU usage, Disk I/O, resource waits and routine latencies”

If the percentage of event “Latch Time” is large, then you may need to tune Latch Wait Time.

• Determine the part of SGA encountered heavy latch contention by referring Here
• Adjust size of Shared Pool/Data Buffer or init parameter upon type of latch defined in Here (if it doesn’t work, then recur to the adjustment of parameter _spin_count)
• Tune undocumented parameter _spin_count upon Oracle Support’s approval

2. Check undocumented parameter _spin_count
Log with sys as sysdba, and run

select x.indx+1,ksppinm,ksppity,ksppstvl
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ksppinm = ‘_spin_count’;

3. Change it value of _spin_count / number of CPUs
set _spin_count to _sping_count/number of CPUs

Note: The above changes on _spin_count is showed in Oralce Metalink 4696143.8. And, any changes on undocumented parameter must be approved by Oracle Support. Please don’t do it if you didn’t experience the long latch wait event.

Popularity: 2% [?]

In Oracle,

“The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

• Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
• I/O server processes
• Oracle backup and restore operations
• Parallel execution message buffers, if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to true (otherwise, these buffers are allocated to the shared pool)”

In oracle 10g, the size of large pool is defined by setting parameter “Large_Pool_Size” and the minimum size of large pool is pre-defined by setting hidden init parameter “_Large_Pool_Min_Alloc” with default size 16K.

Usually, setting large pool as 20M to 30M is sufficient to most of OLTP applications even though RMAN is used to do daily backup.

Popularity: 3% [?]