玉面飞龙的BLOG

Who protect large pool?

June 11th, 2007 | Categories: Boring | Tags:

Recently,read a document sent by wwwf. It’s reference Note: 62140.1.And says “The large pool is protected by the ’shared pool’ latch for memory allocation and management”.

I did test and show that shared pool latch has more contention when sorting & hash join during MTS mode,compared with dedicate mode.

Test Scripts and Steps:

SQL> exec statspack.snap

cat binzhang.sh
-----------------------------------------------
sqlplus binzhang/binzhang@mts9205 <<EOF >>binzhang.text.log
alter session set hash_area_size=100000;
select /*+ use_hash(a1 a2) */count(*) from t a1,t a2
where a1.object_id=a2.object_id;
exit;
EOF
-------------------------------------------

cat runtest.sh
----------------------------------------
wait
./binzhang.sh &
.
.
.
./binzhang.sh &
wait
./binzhang.sh &
.
.
.
./binzhang.sh &
wait
./binzhang.sh &
.
.
.
./binzhang.sh &
wait
---------------------------------------

SQL> exec statspack.snap

Results under MTS mode:it shows 13,870 shared pool latch requests.

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
shared pool 13,870 0.6 0.0 0 0
^LLatch Sleep breakdown for DB: MTS9205 Instance: MTS9205 Snaps: 2 -3
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
shared pool 13,870 80 2 78/2/0/0/0
cache buffers chains 361,377 13 8 0/0/0/0/0
-------------------------------------------------------------
^LLatch Miss Sources for DB: MTS9205 Instance: MTS9205 Snaps: 2 -3
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbgtcr: kslbegin excl 0 4 8
cache buffers chains kcbrls: kslbegin 0 4 2
shared pool kghalo 0 2 0
-------------------------------------------------------------

Yong told me that kghalo means “kernel generic heap allocate”.

Under dedicate mode, it needs 2,962 shared pool latch requests,far less than MTS mode

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
shared pool 2,962 0.3 0.0 0 0
^LLatch Sleep breakdown for DB: MTS9205 Instance: MTS9205 Snaps: 4 -5
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 359,873 39 23 0/0/0/0/0
library cache 8,989 34 5 29/5/0/0/0
-------------------------------------------------------------
^LLatch Miss Sources for DB: MTS9205 Instance: MTS9205 Snaps: 4 -5
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbrls: kslbegin 0 14 3
cache buffers chains kcbgtcr: kslbegin excl 0 7 18
cache buffers chains kcbchg: kslbegin: bufs not 0 1 0
cache buffers chains kcbgcur: kslbegin 0 1 1
library cache kglpndl: child: before pro 0 3 0
library cache kgllkdl: child: cleanup 0 1 0
library cache kglupc: child 0 1 0
-------------------------------------------------------------

Aslo,shared pool latch has less contention,compared with other latches, in dedicate mode.

Shared pool latch also protect shared pool in sga. So some applications that do overmuch sorts&hash opeartions would work worse under MTS if it already has latch contention on shared pool, literal SQL etc.