数据库的性能调优，需要基于操作系统的性能指标，如果操作系统级发生了一些状况，那么会潜移默化的影响到数据库层面。而数据库中对应的进程和操作系统级也有一定的映射关系，在专有服务器模式下大体如此。有时候如果你注意到操作系统级有一些进程消耗资源高，那么很可能这个进程对应的数据库进程存在潜在的问题，这种方法在平时的性能诊断调优中屡试不爽，基本能够很快的定位出问题所在。一方面可以通过数据库的视图映射来分析排查问题，但是很可能等你sql语句准备好了的时候，进程的某些任务也执行完成了，无法同步的抓取到一些很关键的信息。以下的shell脚本对操作系统级的进程和数据库层的进程进行了映射，能够找到对应的session，然后得到当前活最近执行的sql语句。if [ -z "$1" ]; then echo "no process has provided!" exit 0fish_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<ENDset pagesize 0 feedback off verify off heading on echo offselect addr from v\\$process where spid=$1;exit;END`if [ -z "$sh_tmp_process" ]; then echo "no process exists or session is not from a DB account" echo echo "####### Process Information from OS level as below ########" ps -ef|grep $1|grep -v "grep"|grep ora echo "##############################################" exit 0else echo '*******************************************' echo "Process has found, pid: $1 , addr: $sh_tmp_process " echo echo "####### Process Information from OS level as below ########" ps -ef|grep $1|grep -v grep|grep ora echo "##############################################" sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOFcol machine format a20col terminal format a15col osuser format a15col process format a15col username format a15set linesize 150select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v\$sessionwhere paddr='$sh_tmp_process';prompt .col sql_id format a30col prev_sql_id format a30col sql_text format a60set linesize 150set pages 50select sql_id,sql_text from v\$sql where sql_id in (select sql_id from v\$session where paddr='$sh_tmp_process' and sql_id is not null ) and rownum<2;select sql_id prev_sql_id ,sql_text from v\$sql where sql_id in (select prev_sql_id sql_id from v\$session where paddr='$sh_tmp_process' ) and rownum<2;EOFfi假设脚本名为showpid.sh 则运行方式如下，我们碰到了一个进程异常，pid为29291> ksh showpid.sh 29291*******************************************Process has found, pid: 29291 , addr: 000000089837FF20####### Process Information from OS level as below ########oraccbs1 21784 31648 0 20:05 pts/3 00:00:00 ksh showpid.sh 29291oraccbs1 29291 1 90 20:00 ? 00:04:30 oracleCUST01 (LOCAL=NO)############################################## SID SERIAL# USERNAME OSUSER MACHINE PROCESS TERMINAL TYPE LOGIN_TIME---------- ---------- --------------- --------------- -------------------- --------------- --------------- ---------- ------------------- 4779 10699 PRODTEST PRODOSUSER xxxxxxxxxx 4956:5500 TIT_C15-xxxx USER 2015-01-31 20:00:29SQL_ID SQL_TEXT------------------------------ ------------------------------------------------------------248xkdahtdb2q UPDATECOMM_ACTIVITY SET COMM_ACTIVITY.EXTRACT_STATUS = NVL(:1 ,EXTRACT_STATUS), COMM_ACTIVITY.SOURCE_TYPE = NVL(:2 , SOURCE_TYPE),OPERATOR_ID = :3 , APPLICATION_ID = :4 , DL_SERVICE_CODE = :5 ,DL_UPDATE_STAMP = :6 , SYS_UPDATE_DATE = SYSDATE whereCOMM_ACTIVITY.ACTIVITY_CODE=:7 ANDCOMM_ACTIVITY.EXTRACT_STATUS=:8通过如上的命令能够很快的定位到进程和session对应的sql语句，对于排查问题来说更加直观。现在只需要分析一下这条语句即可，看看是什么原因导致资源消耗异常。最后发现这条看似简单的update语句走了全表扫描。其实可以进行一定的优化的。对于这条语句的分析详情请参见http://blog.itpub.net/23718752/viewspace-1422310/