1.SQL TRACE

1.1SQL跟踪流程

1.1.1开启跟踪

alter session set sql_trace true

1.1.2运行需要跟踪的SQL语句

select /*+ no_index(tb test_bitmap_n1 )*/
count(*)
  from test_bitmap tb
 where 1 = 1
      and age between 18 and 22
   and sex = 'M'

1.1.3关闭跟踪

alter session set sql_trace false

如果不关闭跟踪，那么后续该session的操作都会写入跟踪文件中

1.1.4查询跟踪文件所在路径

select * from v$diag_info di where di.name='Diag Trace'

也可以使用

select value
  from v$parameter
 where name = 'user_dump_dest';

1.1.5查询跟踪文件名

我们进入到跟踪文件所在目录下，执行ls -alt查找最新修改的文件

怎么判断这个文件是由当前会话产生的呢？

执行下列查询语句

select spid, s.sid, s.serial#, p.username, p.program
  from v$process p, v$session s
 where p.ADDR = s.PADDR
   and s.SID = (select sid from v$mystat where rownum = 1)

文件VIS_ora_7275.trc就是我们产生的跟踪文件

当然我们可以直接使用下面SQL

select b.username,
       a.SPID,
       b.SID,
       b.SERIAL#,
       c.value || '\' || lower(d.value) || '_ora_' ||
       to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"
  from v$process a, v$session b, v$parameter c, v$parameter d
 where a.addr = b.paddr
   and c.name = 'user_dump_dest'
   and d.name = 'db_name'
   and b.username is not null;

第一行SPID=7275所在路径对应解析文件，如果你不确定SPID为7275的就是我们打开的session，我们可以使用

select userenv('sid') from dual;

1.1.6解析语法

tkprof tracefile output_file [sort=parameters] [print=number] [explain=username/password] [wait=yes|no] [aggregate=yes|no] [insert=filename] [sys=yes|no] [table=schema.table] [record=filename] [width=number]

给一个例子

tkprof VIS_ora_7275.trc VIS_ora_7275_1.prf sys=no sort=(fchela,fchcpu)

tkprof 是一个命令，我们需要查询环境变量，看看tkprof在哪个路径下能被识别，如果你直接在当前路径下执行，会提示

-bash: tkprof: command not found

我们执行一个命令，系统首先会去path查找该命令所在磁盘的位置，去该位置执行该命令。如果找不到，再去当前目录下找，如果也找不到，就会报错：-bash: xxxxx: command not found

我们先找到这个tkprof到底在哪

因为有得文件会加上前缀./进行隐藏，因此我们用正则表达式去匹配

find / -iregex '.*tkprof'

我们可以自己添加tkprof到我们的path路径里

编辑/etc/profile或者~/.bashrc

前者对应所有的用户，是系统级的环境变量，需要重启服务器，后者适用当前用户，也不需要重启。

我们试一试后面这种

vi ~/.bashrc打开文件

最后面加上export PATH命令，等号后面是我们刚才查找到的路径

编辑完后ESC然后:wq保存退出。

我们再执行一下source ~/.bashrc使之生效

我们切换到跟踪文件所在路径，执行一下tkprof命令

这是怎么一回事？我们看看PATH环境变量

原来是路径写错了，我们重新编辑一下，把/tkprof删掉，这个是文件，不是目录

成功产生解析文件

当然不用那么麻烦，我们可以直接使用applmgr或者oracle用户，因为trace目录所有者为oracle,建议用oracle用户操作

也是吻合的

具体如何编辑环境变量参考如何在Linux中修改环境变量PATH

1.1.7打开解析文件

成功找到了我们的当前会话执行的SQL

可以看到总共有78次读操作，77次是磁盘读

1.1.8产生其它session的跟踪文件

如果是非当前session,我们怎么样得到跟踪文件呢？

首先必须要有需要跟踪会话的sid和serial#,为了测试我新开了一个窗口，当前的sid和serial#如下

开启和关闭跟踪代码如下

begin
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(359,737,true);
--DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(359,737,false);
end;

去trace路径下查看，发现生成了tra文件

1.1.9分析tra文件

tkprof命令见http://www.dba-oracle.com/t_tkprof_analysis.htm

tkprof 命令行选项 tkprof提供了许多有用的命令行选项，这些选项为DBA提供了附加功能。 ·print-仅列出第一行在输出文件中的SQL语句。如果未指定任何内容，将列出所有语句。当列表需要限于“ Top n ”语句时，请使用此选项。与排序选项结合使用时，此功能很有用，以按CPU，磁盘读取或解析等方式启用前n条语句。 ·aggregate -如果选择“是”，则tkprof 将合并来自同一SQL语句的多个用户执行的统计信息。如果为“否”，则每次执行该语句时都会列出统计信息。 ·insert-创建一个文件，该文件将统计信息加载到数据库中的表中以进行进一步处理。如果要对 tkprof输出执行任何高级分析，请选择此选项。 ·sys-启用或禁用由SYS用户执行的SQL语句（包括递归SQL语句）的包含。默认为启用。 ·table -在解释计划使用命令（如果指定）为Oracle到负载数据暂时到Oracle表。用户必须为计划表指定架构和表名。如果该表存在，则将删除所有行，否则tkprof将创建该表并使用它。 ·record -创建具有指定文件名的SQL脚本，该文件名包含跟踪文件中的所有非递归SQL语句。对于想要将SQL语句记录在单独文件中的DBA，这是可以使用的选项。在前面的示例中， Allsql.sql 文件的内容包括： alter session set sql_trace=true ; select * from employee where emp_id = 87933 ; alter session set sql_trace=false ; ·explain -对跟踪文件中的每个语句执行一个解释计划，并显示输出。与tkprof结合使用时，解释计划的用处不如单独使用时有用。解释计划提供了预测的优化器执行路径，而无需实际执行该语句。tkprof 向您显示语句执行后的实际执行路径和统计信息。此外，鉴于数据库环境中的依赖关系和更改，针对捕获和保存的SQL语句运行Explain Plan总是有问题的。 ·sort -排序由标准的跟踪文件中的SQL语句被视为最重要的由DBA。使用此选项，DBA可以在文件顶部查看消耗最多资源的SQL语句，而不是在整个文件内容中搜索性能不佳的人。以下是可用于排序的数据元素： ·prscnt- 解析SQL的次数。 ·prscpu- 解析所花费的CPU时间。 ·prsela- 解析SQL所花费的时间。 ·prsdsk- 解析所需的物理读取数。 ·prsmis- 解析所需的一致块读取数。 ·prscu- 解析所需的当前块读取数。 ·execnt- 执行SQL语句的次数。 ·execpu- 执行SQL所花费的CPU时间。 ·exeela- 执行SQL所花费的时间。 ·exedsk- 执行期间的物理读取数。 ·exeqry- 执行期间一致的块读取数。 ·execu- 执行期间当前读取的块数。 ·exerow- 执行期间处理的行数。 ·exemis- 执行期间库高速缓存未命中的数量。 ·fchcnt- 执行的访存次数。 ·fchcpu- 提取行所花费的CPU时间。 ·fchela- 读取行所花费的时间。 ·fchdsk-访存期间读取的物理磁盘数。 ·fchqry-访存期间读取的一致块数。 ·fchcu-访存期间读取的当前块数。 ·fchrow- 为查询获取的行数。存在许多排序选项，但是某些选项比其他选项更有用。在使用tkprof格式化跟踪输出时，Execnt，execpu，exedsk和prscnt是最有用的排序参数，因为它们更能指示大多数SQL性能问题。执行次数最能说明性能问题，因此应该冒顶。对于使用最多CPU- execpu的SQL语句，尤其如此。该prscnt 参数非常重要，因为它显示了解析大部分，通常不使用绑定变量的结果的SQL语句。

1.2DBMS_MONITOR

DBMS_MONITOR程序包可以跟踪用户和服务

我们可以像DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION一样传入sid和serial#，也可以传入用户标识符，跟踪用户

1.2.1基于会话

还是用之前查询session的sql

select spid,
       s.sid,
       s.serial#,
       s.username,
       s.client_identifier,
       p.username,
       p.program
  from v$process p, v$session s
 where p.addr = s.paddr
   and s.sid = (select sid from v$mystat where rownum = 1)

系统管理员账户执行下列程序开启跟踪

begin
    dbms_monitor.session_trace_enable(351,1067,true,false);
end;

关闭跟踪

begin
   dbms_monitor.session_trace_disable(351,1067);
end;

前两个参数分别为sid和serial#

1.2.2.基于用户

为当前用户设置标识符，比如我设置为123

   begin
    dbms_monitor.session_trace_enable(351,1067,true,false);
   end;

这时我们再查询上面的SQL

接着跟踪该标识符，管理员用户下执行

begin
dbms_monitor.client_id_trace_enable('123');
end;

查看跟踪是否打开

select * from dba_enabled_traces