LATCH FREE :space background task
Latch free is a memory related specifically shared pool event which occur due to lack of space for the session to hold a latch.
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 3,914 47.2
latch free 234,621 2,162 9 26.1
In AWR report, the top latch with high sleep activity is space background task which is a background task which manage the latch space with the slaves.
Latch Activity DB/Inst: DB9ZX/DB9 Snaps: 14242-14243
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
space background task la 37,163 20.4 0.6 2 6,132 3.4
Top sleep breakdown related to this latch is also significant
Latch Sleep breakdown DB/Inst: DB9ZX/DB9 Snaps: 14242-14243 -> ordered by misses desc
space background task latc 37,163 7,595 4,309 3,360
Latch Miss Sources DB/Inst: DB9ZX/DB9 Snaps: 14242-14243
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
space background task la ktsj_grab_task 0 2,603 4,239
space background task la ktsj_detach_task 0 1,625 69
space background task la ktsj_smco_purgeexpired 0 75 0
space background task la ktsjCreateTask 0 6 1
Solution:
Please increase the parameter to cache the cursors for efficient parsing and also reduce the space background process slaves which spawn higher.
.
alter system set session_cached_cursors=150 scope=spfile;
alter system set "_max_spacebg_slaves"=64;
Note: Reboot is required to set the parameter.