gt; <processes waiting for latch2> ... Process <PID2> ... */ set head off set feedback off set linesize 120 select sysdate from dual; select LPAD(' ', (LEVEL - 1) ) ||case when latch_holding is null then 'Process '||pid else 'holding: '||latch_holding||' "'||name||'" lvl='||level#||' whr='||whr||' why='||why ||', SID='||sid end || case when latch_waiting is not null then ', waiting for: '||latch_waiting||' whr='||whr||' why='||why end latchtree from ( /* Latch holders */ select ksuprpid pid,ksuprlat latch_holding, null latch_waiting, to_char(ksuprpid) parent_id, rawtohex(ksuprlat) id, ksuprsid sid,ksuprllv level#,ksuprlnm name,ksuprlmd mode_,ksulawhy why,ksulawhr whr from x$ksuprlat union all /* Latch waiters */ select indx pid,null latch_holding, ksllawat latch_waiting,rawtohex(ksllawat) parent_id,to_char(indx) id, null,null,null,null,ksllawhy why,ksllawer whr from x$ksupr where ksllawat !='00' union all /* The roots of latch trees: processes holding latch but not waiting for latch */ select pid, null, null, null, to_char(pid),null,null,null,null,null,null from ( select distinct ksuprpid pid from x$ksuprlat minus select indx pid from x$ksupr where ksllawat !='00') ) latch_op connect by prior id=parent_id start with parent_id is null;
$ cat /usr/local/bin/timestamp.pl #!/usr/bin/perl while (<>) { ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(); printf("%02d:%02d:%02d", $hour, $min, $sec); print ": $_"; #print localtime() . ": $_"; } --//使用timestamp.pl在开始标注时间.这样更加清晰.
2.测试: --//补充测试 X mode,S mode ,X 模式的情况. $ cat f1.sh #! /bin/bash source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt & seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >| /tmp/latch_free.txt & # 参数如下: @ latch.txt latch_name willing why where mode sleep_num sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null & sleep 2 sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 s 6 > /dev/null & sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 s 6 > /dev/null & sleep 0.1 sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null & wait
$ grep -v '^.*: $' /tmp/peeks.txt 09:28:38: SYSDATE LADDR 09:28:38: ------------------- ---------------- 09:28:38: 2019-04-16 09:28:38 0000000060018A18 09:28:38: Statement processed. 09:28:38: [060018A18, 060018A20) = 0000001C 20000000 09:28:39: [060018A18, 060018A20) = 0000001C 20000000 09:28:40: [060018A18, 060018A20) = 0000001C 20000000 09:28:41: [060018A18, 060018A20) = 0000001C 20000000 09:28:42: [060018A18, 060018A20) = 0000001C 20000000 09:28:43: [060018A18, 060018A20) = 0000001C 20000000 09:28:44: [060018A18, 060018A20) = 00000001 00000000 09:28:45: [060018A18, 060018A20) = 0000 |