#!/bin/bash
#===========================================================
function ShowCopyright ()
{
cat << \EOF
################################################################################
# SSS.SH is program that analyzes the SystemState developed by Shunya suzuki.
# Copyright (C) 2015 CO-Sol Inc.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public
# License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program. If not, see .
################################################################################
EOF
#-----------------------------------------------------------
}
# History
# ~~~~~~~
# 0. shunya.suzuki@cosol.jp v1.0.0a1 2014/09/16
# start
# 1. shunya.suzuki@cosol.jp v1.0.0a2 2014/10/06
# implement - add matrix output method
# refactoring - ???
# 2. shunya.suzuki@cosol.jp v1.0.0b1 2014/11/15
# implelemt - add lock detect method(terminal psr)
# +--+-------------------------+--+---+---+---+---+---+
# | | |9i|10g|10g|11g|11g|12c|
# |No|feature |R2|R1 |R2 |R1 |R2 |R1 |
# +--+-------------------------+--+---+---+---+---+---+
# | 1|enqueue |o | o | o | o | o | o |
# | 2|latch |d | o | o | o | o | d |
# | 3|mutex |- | - | o | o | o | o |
# | 4|row cache enqueue(rcache)|x | x | x | x | o | o |
# | 5|library cache lock(lock) |o | o | o | o | o | o |
# | 6|library cache pin(pin) |o | o | o | o | o | o |
# +--+-------------------------+--+---+---+---+---+---+
# o:testcase
# x:no testcase,no test
# -:not implement in oracle db
# d:test with debugger
# 3. shunya.suzuki@cosol.jp v1.0.0b3 2015/01/02
# bug fix - how to analysis Session SO
# extension - sstree(beta
# 4. shunya.suzuki@cosol.jp v1.0.0 2015/01/09
# enhance - how to analysis 'enq convert'
# extension - hungchk(beta
# 5. shunya.suzuki@cosol.jp v1.0.0 2015/05/31
# refactoring - japanese comments into english
# 6. shunya.suzuki@cosol.jp v1.0.1 2015/06/03
# implement - add html report mode
# refactoring - change progress output method(stdout -> use /dev/stderr)
# 7. shunya.suzuki@cosol.jp v2.0.0 2015/06/29
# refactoring - change file type(awk -> sh)
# add several function
# o analyze the running sql
# o summary Proc/SessSO
# some extension is removed temporarily
# 8. shunya.suzuki@cosol.jp v2.0.1b 2017/11/29
# testing for 12cR2
# bug fix - how to analysis Enqueue SO
#===========================================================
# awk script - common function
#-----------------------------------------------------------
function ShowAwkCommonFunction ()
{
cat << \EOF
#===========================================================
# Function - common function
#-----------------------------------------------------------
# Function : MID
# get the characters enclosed by the specified patterns.
function MID(str,pattern1,pattern2, idx){
if(pattern1!="" && pattern2!=""){ #when specified both pattern1,pattern2
if(index(str,pattern1)==0) return ""
idx=index(str,pattern1) + length(pattern1)-1 #get the characters enclosed by the parameters
str=substr(str,idx+1,length(str)-idx)
str=substr(str,1,index(str,pattern2)-1)
}else if(pattern2==""){ #when specified only pattern1
idx=index(str,pattern1) + length(pattern1)-1 #get pattern1 subsequent characters
str=substr(str,idx+1,length(str)-idx)
}else if(pattern1==""){ #when specified only pattern2
idx=index(str,pattern2)-1 #get pattern1 previous characters
str=substr(str,1,idx)
}
return str
}
# Function : ElemN
# get the n-th string separated by specified character.
function ElemN(str,pattern,n, arr){
split(str,arr,pattern)
return arr[n]
}
# Function : Replace,Remove
# this function like sub,but get replaced characger
# instead substitution number of times.
# (this function can be replaced in gensub in gawk)
function Replace(str,r,s){gsub(r,s,str);return str}
function Remove(str,r) {return Replace(str,r,"")}
# Function : Repeat
# get the characters repeated specified character.
function Repeat(s,n, r,i) {for(;i,/",arr1,",")
split("\\&,\\ ,\\",\\<,\\>,\\⁄",arr2,",")
for(i=1;i<=length(arr1);i++)
str=Replace(str,arr1[i],arr2[i]) #pattern1
#gsub(arr1[i],arr2[i],str) #pattern2
#str=gensub(arr1[i],arr2[i],"g",str) #pattern3
return str
}
# I wondered if pattern2,3 is faster than pattern1, so I confirmed
# the processing time of pattern1,2,3 using the following code.
# as a result, there was hardly any difference.
#
# $echo " ,\",&,<,>,/" | time awk '
# > function Replace(str,r,s){gsub(r,s,str);return str}
# > function HtmlEncStr(str, arr1,arr2,i){..... omit}
# > {
# > for(i=1;i<=100000;i++) HtmlEncStr($0)
# > print "Elapsed Time: " end_time - start_time " (sec)";
# > }'
# 5.95user 0.00system 0:05.96elapsed 99%CPU (0avgtext+0avgdata 552960maxresident)k
# 0inputs+0outputs (2230major+0minor)pagefaults 0swaps
# Funciton : Max
# get the number of large value.
# exception pattern assumes that you are numeric input value without taking into account
function Max(n1,n2){
if(n1>n2) return n1
return n2
}
# Function : IsNum
# return 1 if its number
# else return 0
function IsNum(n){return (n~"^-?0?\\.?[0-9]+$")}
# Function : ExistArr
# return true, if exist specified pattern in arr
function ExistArr(arr,pattern, i){
for(i=1;i<=length(arr);i++){
if(arr[i]==pattern) return 1
}
return 0
}
# Function : IndexArr
# returns the subscript if the specified value exists
# else return -1
function IndexArr(arr,str, i){
for(i in arr) if(arr[i]==str) return i
return -1
}
# Function : AddItem
# set specified value at [number of items in arr]+1
function AddItem(arr,val){arr[length(arr)+1]=val}
EOF
}
#===========================================================
# awk script - ss.awk
#-----------------------------------------------------------
function ShowAwkSs ()
{
cat << \EOF
#===========================================================
# Function - ss.awk
#-----------------------------------------------------------
# Function : InitMaxLenMtx
function InitMaxLenMtx(arr1,arr2, i){
for(i=1;i<=length(arr2);i++) arr1[i]=length(arr2[i])
}
# Function : SetMaxLenMtx
function SetMaxLenMtx(arr1,arr2, i,j,val,len){
for(i in arr2){
val=arr2[i]
j=ElemN(i,SUBSEP,3)
len=length(val)
if(arr1[j]
")
for(i=1;i<=length(arr1);i++)
printf("
%s
",arr1[i])
print("
")
}
# Function : SetHdrMtxExLen
# csv1 stop list
# arr1 len list
function SetHdrMtxExLen(csv1,arr1,arr2, i,j,s,arr3){
split(csv1,arr3,",")
s=j=0
for(i=1;i<=length(arr1);i++){
if(ExistArr(arr3,i)){
arr2[++j]=--s
s=0
}
s+=arr1[i]+1
}
arr2[++j]=--s
}
# Function : GetHdrTtlMtxEx
# csv1 stop list
# csv2 element(title name)
# arr1 len list
function GetHdrTtlMtxEx(csv1,csv2,arr1, i,f,arr2,arr3,ret){
ret=C_OUTPUT_RESULT_WITH_BLANK
SetHdrMtxExLen(csv1,arr1,arr2)
split(csv2,arr3,",")
for(i=1;i<=length(arr2);i++){
f=sprintf("%%%s-s",arr2[i])
ret=ret sprintf(f,arr3[i])
if(i!=length(arr2)) ret=ret C_SEP
}
return ret
}
# Function : OutputHdrTtlMtxExHtml
# csv1 stop list
# csv2 element(title name)
# arr1 len list
function OutputHdrTtlMtxExHtml(csv1,csv2,arr1, i,f,arr2,arr3,ret){
SetHdrMtxExLen(csv1,arr1,arr2)
split(csv1,arr2,",")
split(csv2,arr3,",")
AddItem(arr2,length(arr1) - arr2[length(arr2)])
printf("
")
for(i=1;i<=length(arr3);i++){
printf("
%s
",arr2[i],arr3[i])
}
print("
")
}
# Function : GetHdrSepMtx
function GetHdrSepMtx(arr1, ret,i){
ret=C_OUTPUT_RESULT_WITH_BLANK
for(i=1;i<=length(arr1);i++){
ret=ret Repeat("-",arr1[i])
if(i!=length(arr1)) ret=ret C_SEP
}
return ret
}
# Function : GetHdrSepMtxEx
# csv1 stop list
# arr1 len list
function GetHdrSepMtxEx(csv1,arr1, arr2,ret,i){
SetHdrMtxExLen(csv1,arr1,arr2)
ret=C_OUTPUT_RESULT_WITH_BLANK
for(i=1;i<=length(arr2);i++){
ret=ret Repeat("-",arr2[i])
if(i!=length(arr2)) ret=ret C_SEP
}
return ret
}
# Function : OutputMtx
# arr1 data
# arr2 format
# arr3 row count
function OutputMtx(arr1,arr2,arr3,i, j,k,val){
for(j=1;j")
for(j=1;j")
for(k=1;k<=length(arr2);k++){
val=Replace(arr1[i,j,k]," ",C_PRINT_IN_PLACE_BLANK)
val=HtmlEncStr(val)
align=(arr2[k]=="" ? "align=\"right\"" : "")
printf("
%s
",align,val)
}
print("")
}
printf("")
}
# Function : AddRes
# arr : manage resource("_holder or _waiter")
# sc : SystemstateCount
# pid : oracle pid
# id : resource id
# type: locking type
# mode: mode/request level
# evt : wait event
function AddRes(arr,sc,pid,id,type,mode,evt, cnt){
arr[sc,"cnt"]++
cnt=arr[sc,"cnt"]
arr[sc,cnt,"pid"]=pid
arr[sc,cnt,"id"]=id
arr[sc,cnt,"type"]=type
arr[sc,cnt,"mode"]=mode
arr[sc,cnt,"event"]=evt
if(type=="latch"){
arr[sc,cnt,"event"]="not implement yet. see above"
arr[sc,cnt,"mode"]="N/A"
}
}
# Function : AddRes
# get enq-name from NameAndType parameter
function GetNameAndType(p1, val1,val2){
val1=sprintf("%c",strtonum("0x" substr(p1,1,2)))
val2=sprintf("%c",strtonum("0x" substr(p1,3,2)))
return sprintf("%s%s",val1,val2)
}
# Function : SetProcSessElem
# set each element of proc / sess state
function SetProcSessElem(){
if(_ver>=92) {AddItem(e2,"pid"); AddItem(f2,"")} #v$process.pid
if(_ver>=92) {AddItem(e2,"addr"); AddItem(f2,"")} #v$process.addr
if(_ver>=92) {AddItem(e2,"user"); AddItem(f2,"")} #v$process.username
if(_ver>=92) {AddItem(e2,"term"); AddItem(f2,"-")} #v$process.terminal
if(_ver>=92) {AddItem(e2,"ospid"); AddItem(f2,"")} #v$process.spid
if(_ver>=92) {AddItem(e2,"image"); AddItem(f2,"-")} #v$bgprocess.name
if(_ver>=92) {AddItem(e4,"saddr"); AddItem(f4,"")} #v$session.saddr
if(_ver>=102){AddItem(e4,"sid"); AddItem(f4,"")} #v$session.sid
if(_ver>=111){AddItem(e4,"ser"); AddItem(f4,"")} #v$session.serial#
if(_ver>=102){AddItem(e4,"service"); AddItem(f4,"-")} #v$session.service_name(10.2.0.4 -)
if(_ver>=92) {AddItem(e4,"username"); AddItem(f4,"-")} #v$session.username(Oracle User Name)
if(_ver>=92) {AddItem(e4,"process"); AddItem(f4,"")} #v$session.process(OS の Client Process ID)
if(_ver>=92) {AddItem(e4,"term"); AddItem(f4,"-")} #v$session.terminal
if(_ver>=92) {AddItem(e4,"cli_info"); AddItem(f4,"-")} #v$session.client_info(dbms_application_info.set_client_info)
if(_ver>=92) {AddItem(e4,"module"); AddItem(f4,"-")} #v$session.module
if(_ver>=92) {AddItem(e4,"action"); AddItem(f4,"-")} #v$session.action
if(_ver>=92) {AddItem(e4,"machine"); AddItem(f4,"-")} #v$session.machine
if(_ver>=92) {AddItem(e4,"osuser"); AddItem(f4,"-")} #v$session.osuser
if(_ver>=92) {AddItem(e4,"program"); AddItem(f4,"-")} #v$session.program
if(_ver>=92) {AddItem(e4,"command"); AddItem(f4,"-")} #v$session.command
if(_ver>=92) {AddItem(e4,"sql_addr"); AddItem(f4,"")} #v$session.sql_address
if(_ver>=92) {AddItem(e4,"block_sess");AddItem(f4,"")} #v$session.blocking_session
if(_ver>=92) {AddItem(e4,"wait_time"); AddItem(f4,"")} #(v$session.wait_time)
if(_ver>=92) {AddItem(e4,"seq"); AddItem(f4,"")} #v$session.seq#
if(_ver>=92) {AddItem(e4,"event"); AddItem(f4,"-")} #v$session.event
}
# Function : OutputTextReport
function OutputTextReport(){
#========================================================
#get length,format
#--------------------------------------------------------
#---proc/sess state
InitMaxLenMtx(l24,e24) #initialize each element by element-name
SetMaxLenMtx(l24,d24) #set each element's length
SetFmtMtx(fmt24,l24,f24) #set each element's format
httl24=GetHdrTtlMtx(e24,fmt24) #set header
hsep24=GetHdrSepMtx(l24) #set header(separator)
httl24ex=GetHdrTtlMtxEx(en2+1,"proc state,sess state",l24)#set (extra)header
hsep24ex=GetHdrSepMtxEx(en2+1,l24) #set (extra)header(separator)
#---waiter/holder
InitMaxLenMtx(lWH,eWH) #initialize each element by element-name
SetMaxLenMtx(lWH,dWH) #set each element's length
SetFmtMtx(fmtWH,lWH,fWH) #set each element's format
httlWH=GetHdrTtlMtx(eWH,fmtWH) #set header
hsepWH=GetHdrSepMtx(lWH) #set header(separator)
httlWHex=GetHdrTtlMtxEx(enW+1,"waiter,holder",lWH) #set (extra)header
hsepWHex=GetHdrSepMtxEx(enW+1,lWH) #set (extra)header(separator)
#========================================================
#output
#--------------------------------------------------------
for(i=1;i<=_sc;i++){
tmp=sprintf("%s.%s,%s",i,_s[i,"ts"],_s[i,"fn"]) #title
printf("\n\n\n%s\n%s",tmp,Repeat("~",length(tmp))) #title
#---proc/sess state
tmp=C_OUTPUT_RESULT_WITH_BLANK
printf("\n%s%s\n%s%s\n",tmp,"proc/sess state summary",tmp,Repeat("~",23)) #title
printf("%s\n%s\n%s\n%s\n%s\n",hsep24ex,httl24ex,hsep24,httl24,hsep24)
OutputMtx(d24,fmt24,rn24,i)
print hsep24
#---waiter/holder
printf("\n%s%s\n%s%s\n",tmp,"waiter/holder summary",tmp,Repeat("~",21)) #title
if(length(dWH)==0){ #if no waiter
print "couldn't detect waiter." #
}else{
printf("%s\n%s\n%s\n%s\n%s\n",hsepWHex,httlWHex,hsepWH,httlWH,hsepWH)
OutputMtx(dWH,fmtWH,rnWH,i)
print hsepWH
}
}
if(analyze_sql){
printf("\n%srunning sql(addr:sql_text)\n%s%s\n",tmp,tmp,Repeat("~",26))
for(key in _sql) printf("%s : %s\n",key,_sql[key])
}
print("\n\n\nSSS.SH - Copyright(C) 2015 CO-Sol Inc.")
}
# Function : OutputHtmlReport
function OutputHtmlReport(){
#========================================================
#output
#--------------------------------------------------------
for(i=1;i<=_sc;i++){
tmp=sprintf("%s.%s,%s",i,_s[i,"ts"],_s[i,"fn"]) #title
printf("
%s %s",tmp,Repeat("~",length(tmp))) #title
#---proc/sess state
tmp=C_OUTPUT_RESULT_WITH_BLANK
printf(" %s%s %s%s ",tmp,"proc/sess state summary",tmp,Repeat("~",23)) #title
print("