当Oracle查询出现效率问题时,我们往往须要了解问题所在,这样才干针对问题给出解决方式。Oracle提供了SQL运行的trace信息,当中包括了SQL语句的文本信息。一些运行统计,处理过程中的等待,以及解析阶段(如生成运行计划)产生的信息。
这些信息有助于你分解sql语句的服务时间和等待时间,并了解所用资源和同步点的具体信息,从而帮助你诊断存在的性能问题。
这篇文章介绍了怎么开启SQL跟踪和获取trace文件,具体信息例如以下。开启SQL跟踪
从内部技术细节看,SQL跟踪是基于10046调试事件的,以下是支持的等级:
0
禁止调试事件 1 调试事件是激活的。针对每一个被处理的数据库调用。给定例如以下信息:SQL语句、响应时间、服务时间
处理的行数、处理的行数、逻辑读数量、物理读与写的数量、运行计划以及一些额外信息 4 假设等级1。包含绑定变量的额外信息。主要是数据类型、精度以及每次运行时所用的值
8 同等级1。加上关于等待时间的细节信息。为了处理过程中的每一个等待,提供例如以下信息:等待时间的名字、持续时间,以及一些额外的參数,可标明所等待的资源 12 同一时候启动等级4和等级8 在Oracle 9i或者之前,以下SQL语句针对所在会话激活SQL跟踪:alter session set sql_trace = true
还可通过dbms_session包中的set_sql_trace存储过程,或者通过dbms_system包的set_sql_tarce_in_session存储过程方法,但这些都仅仅是在等级1激活SQL跟踪,在实践中用处不大,就不详述了。
更实用的是指定级别的方式,以下是对所在会话開始等级12的SQL跟踪:alter session set events '10046 trace name context forever, level 12'
相应的对所在会话禁止SQL跟踪的语句例如以下:
alter session set events '10046 trace name context off'
也能够通过dbms_system包中的set_ev存储过程。这里也不详述了,我以下重点讲讲Oracle 10g之后提供的方法。
Oracle 10g之后提供了dbms_monitor包来开启或关闭SQL跟踪。提供了在会话、client、组件以及数据库层级开启SQL跟踪方法。注意仅仅有dba角色的用户才同意使用。会话级
以下的PL/SQL为ID为122。序列号为6734的会话开启第8级的SQL跟踪:
begin dbms_monitor.session_trace_enable(session_id => 122, serial_num => 6734, waits => true, binds => false);end;
session_id
session标识。相应v$session视图中的SID列。以下是获取当前会话id的方法:select userenv('sid') from dual
serial_num
相应v$session视图中的SERIAL#列,因为SID会重用。当SID被重用时,SERIAL#添加。获取方法例如以下:select serial# from v$session where sid = 122
waits
相应v$session视图中的SQL_TRACE_WAITS,表示等待事件跟踪是否被激活,默觉得true。binds
相应v$session视图中的SQL_TRACE_BINDS,表示绑定跟踪是否被激活。默认false。当运行成功后。v$session视图中的SQL_TRACE被改动为ENABLED,SQL_TRACE_WAITS和SQL_TRACE_BINDS为你设置的相应值。
以下的PL/SQL用于关闭SQL跟踪:begin dbms_monitor.session_trace_disable(session_id => 122, serial_num => 6734);end;
client级
以下的PL/SQL调用为全部具有指定client标记的会话开启第8级的SQL跟踪:
begin dbms_monitor.client_id_trace_enable(client_id => 'test', waits => true, binds => false);end;
须要注意client标记区分大写和小写,能够通过以下的方法看是否设置成功:
select primary_id as client_id, waits, binds from dba_enabled_traces where trace_type = 'CLIENT_ID'
当设置成功后,每次查询前指定相应的client标记就能够开启SQL跟踪,指定client标记的方法例如以下:
begin DBMS_SESSION.SET_IDENTIFIER('test');end;//该会话的SQL跟踪已经开启
当你为一个session设置了标记后,能够在v$session的client_identifier列查看该标记。
以下的PL/SQL用于关闭SQL跟踪:begin dbms_monitor.client_id_trace_disable(client_id => 'test');end;
组件级
以下的PL/SQL调用为全部具有指定client标记的会话开启第8级的SQL跟踪:
begin dbms_monitor.serv_mod_act_trace_enable(service_name => 'ly', module_name => 'PL/SQL Developer', action_name => 'SQL 窗体 - 新建', waits => true, binds => false, instance_name => null);end;
參数中的service_name相应v$session视图的service_name,module_name相应v$session视图的的module,action_name相应v$session视图的action。查询方式例如以下:
SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION
设置之后能够通过例如以下方法查看设置:
select primary_id as service_name, qualifier_id1 as module_name, qualifier_id2 as action_name, waits, binds from dba_enabled_traces where trace_type = 'SERVICE_MODULE_ACTION'
以下的PL/SQL用于关闭SQL跟踪:
begin dbms_monitor.serv_mod_act_trace_disable(service_name => 'ly', module_name => 'PL/SQL Developer', action_name => 'SQL 窗体 - 新建', instance_name => null);end;
数据库级
以下的PL/SQL调用开启了数据库的12级SQL跟踪:
begin dbms_monitor.database_trace_enable(waits => true, binds => true, instance_name => null);end;
以下的方法查看设置是否成功:
select instance_name, waits, binds from dba_enabled_traces where trace_type = 'DATABASE'
以下的PL/SQL用于关闭SQL跟踪:
begin dbms_monitor.database_trace_disable(instance_name => null);end;
trace文件里的计时信息
以下的语句用于为trace文件提供计时信息:
alter session set timed_statistics = true
普通情况下默认都为true,假设不提供计时信息。trace文件就没有什么用了,因此开启SQL跟踪之前,最好确认一下參数被设置为true。
获取生成的trace文件
开启SQL跟踪后。会生成一个trace文件,通过初始化參数user_dump_dest配置其所在文件夹,该參数的值能够通过以下方法获取到:
select name, value from v$parameter where name = 'user_dump_dest'
但假设我们须要定位到详细的文件。则须要了解trace文件的命名。trace文件的名字是独立于版本号和平台的,在大部分常见的平台下。命名结构例如以下:
{instance name}_{process name}_{process id}.trc
1)instance name
初始化參数instance_name的小写值。通过v$instance视图的instance_name列能够得到这个值。 2)process name 产生跟踪文件进程的名字的小写值。对于专有服务器进程。使用ora,对于共享服务器进程,能够通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程,能够通过v$px_process视图server_name列获得。对于其它多数后台进程来说。能够通过v$bgprocess视图的name列获得。 3)process id 操作系统层面的进程标记。这个值能够通过v$process视图的spid列获取。
依据这些信息,能够通过以下的方式获取trace文件名称:select s.SID, s.SERVER, lower(case when s.SERVER in ('DEDICATED', 'SHARED') then i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' || p.SPID || '.trc' else null end) as trace_file_name from v$instance i, v$session s, v$process p, v$px_process pp, v$shared_server ss where s.PADDR = p.ADDR and s.SID = pp.SID(+) and s.PADDR = ss.PADDR(+) and s.TYPE = 'USER' and s.SID = 'your sid' order by s.SID
将上面的'your sid'替换为你的session的sid就能够查出指定session生成的trace文件的名字,session的sid在v$session视图中得到,或者直接查询当前session的sid:
select userenv('sid') from dual
将路径(user_dump_dest)和文件名称结合在一起。我们就得到了trace文件的完整路径。
而在Oracel 11g中。查询当前会话生成的trace文件则很easy:
select value from v$diag_info where name = 'Default Trace File'