[20190417]隐含参数_SPIN_COUNT.txt

喜欢ヅ旅行 2022-01-10 02:55 480阅读 0赞

\[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占用时间不多.

转载于:https://www.cnblogs.com/lfree/p/10728457.html