oracle傻瓜手册--分析session中SQL执行情况

oracle用户或与oracle同组用户

$svrmgrl

SVRMGR>connect internal

/*开始监控此session一段内的SQL执行状况*/

监控Oracle服务进程，记录需分析的进程号

SVRMGR>select s.sid, s.serial# from v$session s, v$process p where p.spid=’进程号’ and s.paddr=p.addr;

/*SVRMGR>select sid, serial# from v$session where status=’ACTIVE’; 此为任选一个session进行分析*/

SVRMGR>alter system set timed_statistics=true;

SVRMGR>execute dbms_system.set_sql_trace_in_session( sid, serial#, TRUE);

SVRMGR>execute dbms_session.set_sql_trace(TRUE)或alter session set sql_trace=true 对当前session做trace分析

/*分析文件生成于 $ADMIN/udump（根据 initora.ora中 user_dump_dest）下*/

/*一段时间后……*/

SVRMGR>alter system set timed_statistics=false;

SVRMGR> execute dbms_system.set_sql_trace_in_session( sid, serial#, FALSE);

/*利用工具tkprof将report.trc文件转化为可读形式*/

$tkprof report.trc report.txt sort=EXECPU explain=system/manager /*选择CPU占用时间进行排序*/

分析结果：

1. cpu与elapse相差很大说明I/O繁忙，请求等待时间长，有可能进行了表扫描，如：

call	count	cpu	elapsed
parse	5	0.15	2.08
execute	12	6.30	41.04
fetch	0	0.00	0.00

 

2. 如 TABLE ACCESS出现FULL，说明未使用index；使用了index而未用首列；使用了index，而由于其检索信息（主要是B+树）过于陈旧，使oracle在决定检索策略时，错误地选择了表扫描，如：

update work_user_skill set work_level=:b0,skill_value=:b1

where (mb_loginname=:b2 and work_type=:b3) /*mb_loginname和work_type恰好为主键）

Rows Execution Plan

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

0 UPDATE STATEMENT GOAL: CHOOSE

0 UPDATE OF ‘WORK_USER_SKILL’

0 TABLE ACCESS GOAL: ANALYZED (FULL) OF ‘WORK_USER_SKILL’

解决方法：

1． 根据应用的实际需要酌情建立index

2． 更新index陈旧的检索信息

sqlplus dbuser/passwd

SQL>analyze index emp_x01 validate structure;

或者简单些，找到进行全表扫描的table

SQL>analyze table emp validate structure cascade; /*不仅重新检查了table的存储情况，还更新了它的所有index的检索信息*/

另外可重新组织index

alter index emp_x01 rebuild; Oracle8i中增加online参数，避免在重新组织时锁住记录，使其它访问失败

参考命令：

生成 table的统计信息至dba_tables,user_tables

analyze table emp compute|estimate statistics

for table

all columns

columns col1,col2

all indexed columns

生成 index的统计信息至dba_indexes,user_indexes

analyze index emp_x01 compute|estimate statistics

参考命令：

/*选出 session正执行的SQL语句在缓冲池中的编码*/

SVRMGR>select sid, sql_hash_value from v$session where status=’ACTIVE

SVRMGR>select sql_text from v$sqlarea where hash_value={sql_hash_value};

/*查看接受服务方信息 */

SVRMGR>select osuser,process,machine,terminal,program from v$session;