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