前台系统出现问题，在后台往往最早表现为中间件队列积压，比如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))

ITPub博客

查找Tuxedo积压的Oracle诊断脚本

最新文章