Oracle DBA日常維護工作手冊參考3

語法模版

SELECT * FROM

(SELECT hash_value,address,substr(sql_text,1,40) sql,

[list of columns], [list of derived values]

FROM [V$SQL or V$SQLXS or V$SQLAREA]

WHERE [list of threshold conditions for columns]

ORDER BY [list of ordering columns] DESC)

WHERE rownum <= [number of top SQL statements];

實際舉例

SELECT * FROM

(SELECT hash_value,address,substr(sql_text,1,40) sql,

buffer_gets, executions, buffer_gets/executions “Gets/Exec”

FROM V$SQLAREA

WHERE buffer_gets > 100000 AND executions > 10

ORDER BY buffer_gets DESC)

WHERE rownum <= 10;

獲取要跟蹤的用戶進程

SQL> select sid,serial#,username from v$session;

開始跟蹤－結束跟蹤

Exec dbms_system.set_ev(9,437,10046,8,‘用戶名‘);

Exec dbms_system.set_ev(9,437,10046,0,‘用戶名‘);

生成的跟蹤文件在user_dump_dest目錄下

tkprof工具輸出跟蹤報表信息

tkprof /opt/oracle/admin/ytcw/udump/ytcw_ora_1026.trc

/opt/oracle/admin/ytcw/udump/ytcw_ora_1026.prf

aggregate=yes sys=no sort=fchela

Linux、Unix系列平台：

vi alertsid.log

輸入：”/ORA－”
回車進行查找

Windows 平台下使用常用的文本編輯工具即可查看搜索警告日誌文件中Oracle錯誤信息

自動執行statspack快照腳本：statspack_auto_exec.sh

#!/bin/sh

# creator: james_jiang

# function: produce statpack snapshot

echo “Auto Execute Statspack”

$ORACLE_HOME/bin/sqlplus /nolog <<EOF

connect perfstat/perfstat

exec statspack.snap

echo “Auto execute statspack successfully!”

exit

EOF

自動產生最近兩個快照時間點統計信息快照腳本：statspack_auto_report.sh

#!/bin/sh

# creator: james_jiang

# function:get statpack report

echo ” Auto create statspack snapshot!”

SQLPLUS=$ORACLE_HOME/bin/sqlplus

LOGFILE=$ORACLE_HOME/spreport.log

REPFILE=$ORACLE_HOME/spreport.lst

$ORACLE_HOME/bin/sqlplus -S perfstat/perfstat <<EOF

SET ECHO OFF

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SET LINESIZE 1000

SET TRIMSPOOL ON

SPOOL $LOGFILE

select SNAP_ID from (select SNAP_ID from stats\$snapshot where INSTANCE_NUMBER=1 order by SNAP_TIME desc) where rownum<3;

SPOOL OFF;

set echo on

set feedback on

set heading on

exit

EOF

line1=`tail -1 $LOGFILE`

line2=`head -1 $LOGFILE`

echo “line1 is”$line1

echo “line2 is”$line2

$ORACLE_HOME/bin/sqlplus -S perfstat/perfstat <<EOF

define begin_snap=$line1

define end_snap=$line2

define report_name=$REPFILE

@?/rdbms/admin/spreport.sql

echo “Auto create statspack snapshot successfully!”

exit

EOF