Oracle 性能优化求生指南-学习笔记1

一、跟踪 Oracle 的执行
主要由两部分组成
1. SQL 跟踪 在当前会话或其他会话中启动跟踪
2. Tkprof 工具 格式化跟踪文件得到比较容易理解的方式

1. SQL 跟踪的方法
   1). 在应用代码中发出一条指令
   2). 发出一条指令来跟踪其他会话
   3). 调用 DBMA_SESSION 包来指定应该自动跟踪的会话
   4). 根据个人需要创建登陆触发器（logon trigger）来进行跟踪

2. 在当前会话中跟踪

alter session set sql_trace=true;

此条语句创建基本的跟踪，包含sql语句的执行统计信息和执行计划
要获得更高级的跟踪（绑定变量的值，各种事件的等待花费时间），可以使用 DBMS_SESSION 包
DBMS_SESSION.session_trace_enable(
waits IN BOOLEAN DEFAULT TRUE
binds IN BOOLEAN DEFAULT TRUE
plan_stat IN VARCHAR2 DEFAULT NULL – 11g Only never,first_execution,all_execution
);

BEGIN
DBMS_SESSION.session_trace_enable(
true, false, ‘all_executions’
);
END;
/

1. 识别跟踪文件
   为会话知道你给一个跟踪文件标识符。
   alter session set tracefile_identifier=guy;

这样当我们查看跟踪文件的目录是，就可以通过后缀的GUY来识别此跟踪文件。

1. 获取跟踪状态
   在 v$session 表中，列 sql_trace, sql_trace_waits, sql_trace_binds 展示了一个会话的跟踪状态。 在 v$process 表中，列 traceid, tracefile（只对 Oracle 11g 适用）展示了跟踪文件的名称。其中的 tracefile_identifier 对给定的服务器进程有效

select s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, p.TRACEID, p.TRACEFILE from v$session s join v$process p on (p.ADDR = s.PADDR)
where audsid = userenv(‘SESSIONID’);

1. 在其他会话中调用跟踪
   DBMS_MONITOR.session_trace_enable (
   session_id IN BINARY_INTEGER DEFAULT NULL,
   serial_num IN BINARY_INTEGER DEFAULT NULL,
   watis IN BOOLEAN DEFAULT TRUE,
   binds IN BOOLEAN DEFAULT FALSE,
   plan_stat IN VARCHAR2 DEFAULT NULL –11g Only
   );

下面的例程为每一个 sqlplus 会话打开了一个包含绑定变量信息的基本跟踪
BEGIN
FOR ROW IN (select sid, serial# from v$session where lower(program) like ‘%sqlplus%’)
LOOP
DBMS_MONITOR.session_trace_enable (
row.sid, row.serial#, false, true
);
END LOOP;
END;

5.通过 MODULE, ACTION 或 SERVICE 进行跟踪
很多 Oracle 相关应用都会通过 DBMS_APPLICATION_INFO 包来设置模块的动作属性。
可以在 v$session 中查看这些属性，这样就可以明白会话数与那个应用程序，以及它现在正在做什么。
使用服务来管理故无资源并将会话分配到最合适的集群实力，已经变得越来越普遍，特别是真正应用程序集群环境（RAC）中。

此方法可以匹配特定服务，模块，动作以及实例标识符（RAC集群特有）的会话启动跟踪
dbms_monitor.serv_mod_act_trace_enable (
service_name
module_name
action_name
watis
binds
instance_name
plan_stat
);

6.使用登陆触发器来启用跟踪

当用户‘credit’ 登陆时，会为用户登录的所有会话设置一个适当的tracefile——identifier并启动跟踪。
create or replace trigger trace_login_trigger
after logon on database
begin
if user = ‘credit’
then
execute immediate ‘alter session set tracefile_identifier=credit’;

    dbms_session.session_trace_enable(true, false, 'all_executions');
end if;

end;

7.定位跟踪文件
跟踪文件会被写到由Oracle配置参数 user_dump_dest 定义的位置。
select value from v$parameter where name = ‘user_dump_dest’;
跟踪文件的名字为：sid_procid_pid[_traceid].trc

通过确定文件时间戳和文件名称来确定跟踪文件，
或者执行 select ‘catherines trace file’ from dual， 接着就在跟踪文件中搜索字符串 ‘catherines trace file’。