查詢Tuxedo積壓的Oracle診斷指令碼

前臺系統出現問題，在後臺往往最早表現為中介軟體佇列積壓，比如tuxedo。

Tuxedo積壓的原因也許是多種多樣的：

1）有可能是資料庫效能不行

2）也有可能是tuxedo服務中封裝的業務處理邏輯效率太低

3）也有可能是回撥WTC出問題了

4）也有可能是主機資源不足了，CPU或者記憶體消耗光了

5）也有可能是Tuxedo的bug

……

原因很多，診斷的方法也很多。我這裡提供的指令碼主要是針對問題1）的，通過tuxedo堵塞的ID，抓取資料庫對應session資訊、語句資訊、執行計劃資訊、等待事件資訊，幫助快速定位堵塞的原因。

 

------------------------------------------------------------------------------------------------------------------------

#!/sbin/sh

# 載入profile
. $HOME/.profile

# 對指令碼的使用方法做一個簡單的提示
# 指令碼帶2個引數，第一個引數為tuxedo id，第二個引數為所連線的資料庫例項
# 在我們的系統中，tuxedo需要連線的例項有8個，所以需要單獨指定才行
if [ $# -ne 2 ]; then 
    echo "Error, you had enter wrong parameters. "
    echo "USAGE: $(basename $0) tuxedo_processid dbname "
    exit 1
fi

# Get tuxedo process id
tuxedo_id=$1

# 根據第二個引數，連線到對應的例項
if [ $2 = sid1 ]; then
    CONN_STR="username/password@tns1 "
elif [ $2 = sid2 ]; then
    CONN_STR="username/password@tns2 "
elif [ $2 = sid3 ]; then
    CONN_STR="username/password@tns3 "
elif [ $2 = sid4 ]; then
    CONN_STR="username/password@tns4 "
fi

# 根據tuxedo id查詢到對應的程式號
ora_cli_process_id=$(ps -ef | grep "\-i $tuxedo_id" | grep -v grep | awk '{print $2}')

# 根據查詢到的程式號，生成獲取所有session相關資訊的sql指令碼
cat > get_session_info.sql <-- Set up binds for pid,sql_id
variable p_sid          varchar2(30);
variable p_process      varchar2(30);
variable p_sql_id       varchar2(30);
variable p_child_num    varchar2(30);

-- Get the value of pid
begin
    :p_process := &cli_process_id;
end;
/

-- Get the value of p_sid,p_sql_id
begin
select sid,
       decode(sql_id,null, prev_sql_id,sql_id),
       decode(sql_child_number,null,prev_child_number,sql_child_number)
into   :p_sid,
       :p_sql_id,
       :p_child_num
from v\$session s
where s.process = :p_process ;
end;
/

--set sqlplus environment
clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 200 linesize 120 newpage 1 recsep off;
set trimspool on trimout on;
set heading on;
Prompt
Prompt ######################################################################
Prompt Output session information :
set linesize 120
col sid for 99999
col serial# for 999999
col "login" for a35
col "client machine" for a20
col program for a30
select s.sid,s.serial#,s.username||' '||s.client_info "LOGIN",s.machine "CLIENT MACHINE",s.status,s.program
from v\$session s
where s.process = :p_process ;

Prompt ######################################################################
Prompt Output session wait event :
col event for a40
col p1text for a10
col p2text for a10
col p3text for a10
select event,p1text,p1,p2text,p2,p3text,p3
from v\$session_wait where sid=:p_sid ;

Prompt
Prompt ######################################################################
Prompt Output execute SQL statement and execute plan.
select * from table(dbms_xplan.display_cursor(:p_sql_id, :p_child_num));

!SC

sqlplus -s $CONN_STR <clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 80 linesize 80 newpage 1 recsep off;
set trimspool on trimout on;
set heading on;
set linesize 100 ;
Prompt ######################################################################
Prompt Output client operation system process identifies.
Prompt OS pid:$ora_cli_process_id
Prompt
@get_session_info.sql
$ora_cli_process_id
!EOF

# remove the temp file
rm -f get_session_info.sql

 

 

-------------------------------------------------------------------------------------------------------------------------------

給一個輸出的樣例：

[/ntuxapp]$gethangsql 4301 sid2
######################################################################
Output client operation system process identifies.
OS pid:20078

Enter value for cli_process_id:
######################################################################
Output session information :

   SID SERIAL# LOGIN                               CLIENT MACHINE       STATUS   PROGRAM
------ ------- ----------------------------------- -------------------- -------- ------------------------------
  1211   18312 TEST    192.168.1.166               tuxe1                INACTIVE tcsl1serve@tuxe1 (TNS V
                                                                                 1-V3)
######################################################################
Output session wait event :

EVENT                                    P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
---------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SQL*Net message from client              driver id  1413697536 #bytes              1                     0

######################################################################
Output execute SQL statement and execute plan.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  127257tmstapm, child number 0
-------------------------------------
SELECT PARTITION_ID,to_char(USER_ID) USER_ID,to_char(CUST_ID) CUST_ID,to_char(USECUST_ID)
USECUST_ID,BRAND_CODE,PRODUCT_ID,EPARCHY_CODE,CITY_CODE,USER_PASSWD,USER_DIFF_CODE,USER_TYPE_CODE,SERIAL_NU
MBER,NET_TYPE_CODE,to_char(SCORE_VALUE) SCORE_VALUE,CREDIT_CLASS,to_char(BASIC_CREDIT_VALUE)
BASIC_CREDIT_VALUE,to_char(CREDIT_VALUE) CREDIT_VALUE,ACCT_TAG,PREPAY_TAG,to_char(IN_DATE,'yyyy-mm-dd
hh24:mi:ss') IN_DATE,to_char(OPEN_DATE,'yyyy-mm-dd hh24:mi:ss')
OPEN_DATE,OPEN_MODE,OPEN_DEPART_ID,OPEN_STAFF_ID,IN_DEPART_ID,IN_STAFF_ID,REMOVE_TAG,to_char(DESTROY_TIME,'
yyyy-mm-dd hh24:mi:ss') DESTROY_TIME,REMOVE_EPARCHY_CODE,REMOVE_CITY_CODE,REMOVE_DEPART_ID,REMOVE_REASON_CO
DE,to_char(PRE_DESTROY_TIME,'yyyy-mm-dd hh24:mi:ss') PRE_DESTROY_TIME,to_char(FIRST_CALL_TIME,'yyyy-mm-dd
hh24:mi:ss') FIRST_CALL_TIME,to_char(LAST_STOP_TIME,'yyyy-mm-dd hh24:mi:ss')
LAST_STOP_TIME,USER_STATE_CODESET,MPUTE_MONTH_FEE,to_char(MPUTE_DATE,'yyyy-mm-dd hh24:mi:ss')
MPUTE_DATE,to_char(UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss

Plan hash value: 1177735237

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |       |       |     3 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE            |              |     1 |   225 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TF_F_UID     |     1 |   225 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX UNIQUE SCAN               | PK_TF_F_UID  |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("USER_ID"=TO_NUMBER(:VUSER_ID) AND "PARTITION_ID"=MOD(TO_NUMBER(:VUSER_ID),10000))