[20190417]隐含参数_SPIN_COUNT.txt

来源：这里教程网时间：2026-03-03 13:17:30 作者：

[20190417]隐含参数_SPIN_COUNT.txt --//在探究latch spin计数之前,先简单探究_SPIN_COUNT.实际上oracle现在版本latch spin的数量不再是2000,而是记录在 --//x$ksllclass里面.通过例子说明: 1.环境: SYS@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> select * from x$ksllclass ; ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00000000861986C0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 0000000086198744 3 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 8 rows selected. SYS@book> select CLASS_KSLLT,count(*) from x$kslltr group by CLASS_KSLLT; CLASS_KSLLT COUNT(*) ----------- ---------- 2 1 0 581 SYS@book> select CLASS_KSLLT,decode(CLASS_KSLLT,2,KSLLTNAM) name,count(*) from x$kslltr group by CLASS_KSLLT,decode(CLASS_KSLLT,2,KSLLTNAM); CLASS_KSLLT NAME COUNT(*) ----------- ------------------ -------- 0 581 2 process allocation 1 --//还可以看出仅仅1个latch属于2类(latch name='process allocation').其它都是0类.实际上即使是0类,后面的SLEEP0-7不再使用. --//可以发现缺省全部SPIN=20000. 2.测试1: SYS@book> alter system set "_spin_count"=200 scope=memory; System altered. --//实际上动态修改无效.重新登录会话: SYS@book> select * from x$ksllclass ; ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00000000861986C0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 0000000086198744 3 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 8 rows selected. --//SPIN数量不变.实际上动态修改,对应exclusive latch无效.仅仅对shared latch有效,导致实际spin 数量等于_spin_count*2.(我当前没有测试) --//参考链接:http://andreynikolaev.wordpress.com/2011/01/14/spin-tales-part-2-shared-latches-in-oracle-9-2-11g/ 3.测试2: SYS@book> alter system set "_spin_count"=200 scope=spfile ; System altered. --//重启数据库观察: SYS@book> select * from x$ksllclass ; ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00000000861986C0 0 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861986EC 1 1 200 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198718 2 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 0000000086198744 3 1 200 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198770 4 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 000000008619879C 5 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987C8 6 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987F4 7 1 200 0 1 8000 8000 8000 8000 8000 8000 8000 8000 8 rows selected. --//这样更改才生效. SYS@book> alter system reset "_spin_count"; System altered. --//实际上可以但是设置_latch_class_N参数. SYS@book> alter system set "_latch_class_0"=1000 scope=spfile; System altered. --//重启数据库观察: SYS@book> select * from x$ksllclass ; ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00000000861986C0 0 1 1000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 0000000086198744 3 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 8 rows selected. --//你甚至指定特定的latch采用特定的类.还可以修改后面的YIELD,WAITTIME,SLEEP0-SELLP7值.例子: *._latch_classes='8:3' *._latch_class_3='100 0 1 10000 20000 30000 40000 50000 60000 70000 80000' SYS@book> select * from v$latchname where name='process allocation'; LATCH# NAME HASH ---------- ---------------------------------------- ---------- 8 process allocation 2600548697 --//_latch_classes 里面8 值LATCH#,后面3值类.可以指定多个,例子: --//alter system set "_latch_classes"='46:3 103:3' scope=spfile; --//还原: SYS@book> alter system reset "_latch_class_0"; System altered. 4.加强记忆我找一个latch测试看看: --//上午测试是process allocation,现在测试看看类0的修改是否有效. select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_parent where lower(name) like '%'||lower('test excl. parent l0')||'%' ADDR NAME LEVEL# LATCH# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS WAIT_TIME ---------------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- 00000000600098D8 test excl. parent l0 0 4 7 0 0 0 0 0 0 0 0 --//LATCH#=4. SYS@book> create pfile='/tmp/@.ora' from spfile ; File created. --//修改/tmp/book.ora 加入: *._latch_classes='4:3' *._latch_class_3='100 0 1 10000 20000 30000 40000 50000 60000 70000 50000' SYS@book> startup pfile=/tmp/@.ora ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. SYS@book> select * from x$ksllclass ; ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00000000861986C0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000 0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 0000000086198744 3 1 100 0 1 10000 20000 30000 40000 50000 60000 70000 50000 0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000 8 rows selected. SYS@book> select CLASS_KSLLT,decode(CLASS_KSLLT,2,KSLLTNAM,3,KSLLTNAM) name,count(*) from x$kslltr group by CLASS_KSLLT,decode(CLASS_KSLLT,2,KSLLTNAM,3,KSLLTNAM); CLASS_KSLLT NAME COUNT(*) ----------- ---------------------------------------- ---------- 0 580 3 test excl. parent l0 1 2 process allocation 1 --//latch_name='test excl. parent l0',CLASS_KSLLT=3. $ cat p4.sh #! /bin/bash vdate=$(date '+%H%M%S') echo $vdate source peek.sh "$1" 20 | timestamp.pl >| /tmp/peekx_${vdate}.txt & sqlplus -s -l / as sysdba <<EOF >| /tmp/latch_free_${vdate}.txt & $(seq 20 | xargs -I {} echo -e '@latch_free \n host sleep 1') EOF sleep 1 # 参数如下: @ exclusive_latch.txt latch_name willing why where sleep_num sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null & sleep 2 sqlplus /nolog @ exclusive_latch.txt "$1" 1 6 7 10 > /dev/null & p=$! strace -ftrT -p $p -o /tmp/pp_${vdate}.txt > /dev/null & wait $ . p4.sh 'test excl. parent l0' 172843 Process 29626 attached - interrupt to quit Process 29628 attached Process 29645 attached Process 29626 suspended [1] Done source peek.sh "$1" 20 | timestamp.pl >|/tmp/peekx_${vdate}.txt [3] Done sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null [2] Done sqlplus -s -l / as sysdba >|/tmp/latch_free_${vdate}.txt <<EOF $(seq 20 | xargs -I {} echo -e '@latch_free \n host sleep 1') EOF Process 29626 resumed Process 29645 detached Process 29626 detached [4]- Done sqlplus /nolog @ exclusive_latch.txt "$1" 1 6 7 10 > /dev/null Process 29628 detached [5]+ Done strace -ftrT -p $p -o /tmp/pp_${vdate}.txt > /dev/null --// /tmp/pp_172843.txt 29628 0.000081 write(10, "\n", 1) = 1 <0.000026> 29628 0.000149 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010082> 29628 0.010161 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020110> 29628 0.020195 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030115> 29628 0.030207 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040118> 29628 0.040216 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050120> 29628 0.050209 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060129> 29628 0.060218 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070140> 29628 0.070230 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050124> 29628 0.050213 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050118> 29628 0.050207 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050124> 29628 0.050216 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050134> 29628 0.050224 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050117> 29628 0.050207 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050120> 29628 0.050209 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050117> 29628 0.050206 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050120> 29628 0.050211 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050125> --//可以发现一样有效. $ awk '/select/ {print $NF}' /tmp/pp_172843.txt | tr -d '<>' | xargs | sed 's/ /+/g' | bc -l 7.899805 --//接近8秒.spin占用时间不多.

编辑推荐：

返回列表

[20190417]隐含参数_SPIN_COUNT.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推