Oracle Session每日統(tǒng)計(jì)功能實(shí)現(xiàn)

更新時(shí)間：2022年02月08日 09:40:06 作者：空白葛

客戶最近有這樣的需求，想通過(guò)統(tǒng)計(jì)Oracle數(shù)據(jù)庫(kù)活躍會(huì)話數(shù)，并記錄在案，利用比對(duì)歷史的活躍會(huì)話的方式，實(shí)現(xiàn)對(duì)系統(tǒng)整體用戶并發(fā)量有大概的預(yù)估，本文給大家分享具體實(shí)現(xiàn)方法，感興趣的朋友一起看看吧

背景

客戶最近有這樣的需求，想通過(guò)統(tǒng)計(jì)Oracle數(shù)據(jù)庫(kù)活躍會(huì)話數(shù)，并記錄在案，利用比對(duì)歷史的活躍會(huì)話的方式，實(shí)現(xiàn)對(duì)系統(tǒng)整體用戶并發(fā)量有大概的預(yù)估。

功能分析

客戶現(xiàn)場(chǎng)有不少Oracle數(shù)據(jù)庫(kù)，如果每一套都進(jìn)行查詢，效率太慢，而且數(shù)據(jù)也不能保留，所以需要通過(guò)腳本批量查詢的方式實(shí)現(xiàn)。具體功能要點(diǎn)如下：

編寫統(tǒng)計(jì)會(huì)話的SQL腳本
通過(guò)shell 批量管理眾多Oracle實(shí)例，for實(shí)現(xiàn)
輸出每次查詢的記錄留存，根據(jù)實(shí)例分別保存
在有一次初始數(shù)據(jù)之后，以后的每天查出增量數(shù)據(jù)

眾多Oracle實(shí)例統(tǒng)計(jì)完成之后，統(tǒng)一輸出到一個(gè)文件便于查看

具體實(shí)現(xiàn)

統(tǒng)計(jì)會(huì)話SQL腳本

根據(jù)客戶要求，統(tǒng)計(jì)活躍會(huì)話數(shù)，一天運(yùn)行一次即可，沒(méi)必要實(shí)時(shí)數(shù)據(jù)，數(shù)據(jù)可以從數(shù)據(jù)庫(kù)V$SESSION，V$ACTIVE_SESSION_HISTORY，DBA_HIST_ACTIVE_SESS_HISTORY三個(gè)視圖中獲取，V$SESSION視圖為當(dāng)前實(shí)時(shí)活躍會(huì)話信息（內(nèi)存中保存，實(shí)時(shí)信息），V$ACTIVE_SESSION_HISTORY視圖為1s從V$SESSION獲取的活躍會(huì)話信息（內(nèi)存中保存，保存時(shí)長(zhǎng)依賴內(nèi)存ash_buffers參數(shù)），DBA_HIST_ACTIVE_SESS_HISTORY視圖每10s從V$ACTIVE_SESSION_HISTORY視圖中獲取一份（持久化保存，一般為7天），經(jīng)過(guò)以上分析，SQL所需數(shù)據(jù)理應(yīng)通過(guò)DBA_HIST_ACTIVE_SESS_HISTORY視圖獲取。
以下是具體的SQL查詢語(yǔ)句，參考redo log切換統(tǒng)計(jì)語(yǔ)句格式。

prompt
prompt Session cnt
prompt ~~~~~~~~~~~~~~~~
set linesize 200
set pages 2000
col 00 for 9999
col 01 for 9999
col 02 for 9999
col 03 for 9999
col 04 for 9999
col 05 for 9999
col 06 for 9999
col 07 for 9999
col 08 for 9999
col 09 for 9999
col 10 for 9999
col 11 for 9999
col 12 for 9999
col 13 for 9999
col 14 for 9999
col 15 for 9999
col 16 for 9999
col 17 for 9999
col 18 for 9999
col 19 for 9999
col 20 for 9999
col 21 for 9999
col 22 for 9999
col 23 for 9999
select INST_ID,
       a.ttime,
       sum(c0) "00",
       sum(c1) "01",
       sum(c2) "02",
       sum(c3) "03",
       sum(c4) "04",
       sum(c5) "05",
       sum(c6) "06",
       sum(c7) "07",
       sum(c8) "08",
       sum(c9) "09",
       sum(c10) "10",
       sum(c11) "11",
       sum(c12) "12",
       sum(c13) "13",
       sum(c14) "14",
       sum(c15) "15",
       sum(c16) "16",
       sum(c17) "17",
       sum(c18) "18",
       sum(c19) "19",
       sum(c20) "20",
       sum(c21) "21",
       sum(c22) "22",
       sum(c23) "23"
  from (select INST_ID,
               ttime,
               decode(tthour, '00', c_cnt, 0) c0,
               decode(tthour, '01', c_cnt, 0) c1,
               decode(tthour, '02', c_cnt, 0) c2,
               decode(tthour, '03', c_cnt, 0) c3,
               decode(tthour, '04', c_cnt, 0) c4,
               decode(tthour, '05', c_cnt, 0) c5,
               decode(tthour, '06', c_cnt, 0) c6,
               decode(tthour, '07', c_cnt, 0) c7,
               decode(tthour, '08', c_cnt, 0) c8,
               decode(tthour, '09', c_cnt, 0) c9,
               decode(tthour, '10', c_cnt, 0) c10,
               decode(tthour, '11', c_cnt, 0) c11,
               decode(tthour, '12', c_cnt, 0) c12,
               decode(tthour, '13', c_cnt, 0) c13,
               decode(tthour, '14', c_cnt, 0) c14,
               decode(tthour, '15', c_cnt, 0) c15,
               decode(tthour, '16', c_cnt, 0) c16,
               decode(tthour, '17', c_cnt, 0) c17,
               decode(tthour, '18', c_cnt, 0) c18,
               decode(tthour, '19', c_cnt, 0) c19,
               decode(tthour, '20', c_cnt, 0) c20,
               decode(tthour, '21', c_cnt, 0) c21,
               decode(tthour, '22', c_cnt, 0) c22,
               decode(tthour, '23', c_cnt, 0) c23
          from (select instance_number INST_ID,
                       to_char(sample_time, 'YYYY-MM-DD') ttime,
                       to_char(sample_time, 'HH24') tthour,
                       count(1) c_cnt
                  from dba_hist_active_sess_history
                 where sample_time >= trunc(sysdate) - 7
                   and sample_time < trunc(sysdate)  - 1
                 group by instance_number,
                          to_char(sample_time, 'YYYY-MM-DD'),
                          to_char(sample_time, 'HH24'))) a
 group by INST_ID, ttime
 order by ttime desc;

語(yǔ)句執(zhí)行完成之后，效果如下

批量Oracle實(shí)例 for實(shí)現(xiàn)

根據(jù)客戶現(xiàn)場(chǎng)環(huán)境，有多臺(tái)服務(wù)器，每套服務(wù)器上部署多個(gè)實(shí)例，為了避免重復(fù)的代碼，此處大概需要2個(gè)嵌套for循環(huán)，外層負(fù)責(zé)服務(wù)器，內(nèi)存負(fù)責(zé)實(shí)例，外層實(shí)現(xiàn)邏輯如下：

function Startmain
{
 #machines=(Xx)
 machines=(Xx Xx Xx xX Xx)
 v_flag=$1
 for i in ${!machines[*]}
 do
  local v_machine="${machines[$i]}"
  if [[ $v_machine == "Xx" ]];then
    v_args=("XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521")
    v_ip="xxx.xxx.xxx.xxx"
  elif [[ $v_machine == "Xx" ]];then
    v_args=("XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521")
    v_ip="xxx.xxx.xxx.xxx"
  elif [[ $v_machine == "Xx" ]];then
    v_args=("XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521")
    v_ip="xxx.xxx.xxx.xxx"
  elif [[ $v_machine == "xx" ]];then
    v_args=("XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "XXX,1521" "xxxx,1527")
    v_ip="xxx.xxx.xxx.xxx"
  elif [[ $v_machine == "Xx" ]];then
    v_args=("XXX,1521" "XXX,1521" "xxxx,1526")
    v_ip="xxx.xxx.xxx.xxx"
  else
    echo "Error"
  fi
  echo "$v_machine------------------------------------"|tee -a $newfilename
  GetSesscnt  $v_machine "${v_args[*]}" $v_ip $v_flag
 done
}

外層for通過(guò)調(diào)用GetSesscnt函數(shù)，實(shí)現(xiàn)對(duì)服務(wù)器內(nèi)部實(shí)例的解析，（注：在增量查詢之后調(diào)用了日志轉(zhuǎn)存函數(shù)，把數(shù)據(jù)統(tǒng)一輸出到一個(gè)文件）內(nèi)層實(shí)現(xiàn)邏輯如下：

function GetSesscnt
{
 if [[ $# -ne 4 ]];then
   echo "GetSesscnt XX (XXX XXX XXX XXX XXX) xxx.xxx.xxx.xxx flag"
 fi
 machine=$1
 args=($2)
 s_ip=$3
 v_flag=$4
 for i in ${!args[*]} 
 do
 local v_arg="${args[$i]}"
 v_name=${v_arg%,*}
 v_port=${v_arg#*,}
 recho "$machine $v_name start.." |tee -a $newfilename
 if [[ $v_flag == "incr" ]];then
 $ORACLE_HOME/bin/sqlplus -S "system/password@$s_ip:$v_port/$v_name" <<EOF > $pathpwd/$1_$v_name
@$pathpwd/sessioncntincr.sql
exit;
EOF
 LogConvert $pathpwd $1_$v_name
 else
 $ORACLE_HOME/bin/sqlplus -S "system/password@$s_ip:$v_port/$v_name" <<EOF > $pathpwd/$1_$v_name".log"
@$pathpwd/sessioncntinit.sql
exit;
EOF
 fi
 recho "$machine $v_name end"|tee -a $newfilename
 done
}

上述shell腳本中，同時(shí)包含了對(duì)不同服務(wù)器不同實(shí)例的結(jié)果留存，根據(jù)傳輸?shù)氖窃隽窟€是全量參數(shù)，實(shí)現(xiàn)對(duì)不同數(shù)據(jù)的生成保存，全量數(shù)據(jù)為.log后綴，增量數(shù)據(jù)沒(méi)有l(wèi)og后綴保存記錄如下

全量數(shù)據(jù)格式如下：

增量數(shù)據(jù)格式如下：

數(shù)據(jù)統(tǒng)一匯總

通過(guò)以上2步基本把所需的數(shù)據(jù)全部查詢出來(lái)，有全量數(shù)據(jù)之后，每天跑增量即可，剩下工作就把每天跑的增量數(shù)據(jù)結(jié)合全量數(shù)據(jù)匯總和，統(tǒng)一輸出到一個(gè)文件中，實(shí)現(xiàn)代碼如下：

function LogConvert
{
 v_log_path=$1
 v_log_name=$2
 grep -v '^$' $v_log_path/$v_log_name > $v_log_path/log_temp
 v_date=(`cat $v_log_path/log_temp | awk '{print $2}'`)
 v_cnt=(`grep $v_date $v_log_path/$v_log_name".log"|wc -l`)
 if [[ $v_cnt == 0 ]]; then
  sed -i "10 r $v_log_path/log_temp" $v_log_path/$v_log_name".log"
 fi 
 head -40 $v_log_path/$v_log_name".log" >> $newfilename
}

至此，session 統(tǒng)計(jì)腳本工作完成，文章中只羅列了部分代碼實(shí)現(xiàn)邏輯。

到此這篇關(guān)于Oracle Session每日統(tǒng)計(jì)的文章就介紹到這了,更多相關(guān)Oracle Session每日統(tǒng)計(jì)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: