oracle如何获得客户端sql执行计划以便优化sql（三）

oracle如何获得客户端sql执行计划以便优化sql

   今天是2013-09-25日，继续学习sql优化这一部分，在之前写过怎么获得sql的执行计划两篇笔记。虽然笔记有点粗糙，但是如果耐心看，还是发现点东西的。

http://blog.csdn.net/xiaohai20102010/article/details/11694355

http://blog.csdn.net/xiaohai20102010/article/details/11953127

现在在来学习第三种，

  也就是刚刚学习的一个系统包dbms_system中几个procedure的使用：

见：http://blog.csdn.net/xiaohai20102010/article/details/12027793

  一） 获取某个客户端执行的sql要进行如下步骤：

1）获取客户端信息，如sid，SERIAL#等等。

2）设置该会话统计时间等参数，以便对sql执行进行时间统计以及限制trace大小等等。

3）开启session  sql  trace功能

4）收集sql trace信息

5）关闭session  sql 跟踪功能

6）格式化sql 执行计划分析sql性能以便优化。

介绍如下：

1、获取客户session信息：

select s.sid,s.serial#,s.username,s.logon_time,s.osuser,s.machine,p.username,p.program,p.pid
   from v$session s,v$process p
    where s.paddr=p.addr;

当然如果知道一个用户的话可以加入该username

eg：

select s.sid,s.serial#,s.username,s.logon_time,s.osuser,(select sys_context('userenv','ip_address') from dual) as ipad,s.machine,p.username,p.program,p.spid
  2     from v$session s,v$process p
  3      where s.paddr=p.addr and s.username='SCOTT';
 
       SID    SERIAL# USERNAME                       LOGON_TIME  OSUSER                         IPAD                                                                             MACHINE                                                          USERNAME        PROGRAM                                          SPID
---------- ---------- ------------------------------ ----------- ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------------------------------- --------------- ------------------------------------------------ ------------------------
        41        207 SCOTT                          2013/9/25 2 Administrator                  192.168.56.1                                                                     WORKGROUP\RHYS-PC                                                oracle         oracle@oracle-one                                3191

可知：用户为scott，os用户为oracle，sid为：41，seral#为：207，client 主机用户为：administrator，ip地址为：192.168.56.1 ，进程号为：3191等等，这都是关键信息

2、设置参数：

如果启用对会话执行的sql语句时间的统计需要设置timed_statistics参数，跟踪日志文件输出11g之前受user_dump_dest参数控制，但是到了11g该 参数失效，另外由于跟踪的是一个会话，可能会产生非常大的trace，我们可以根据需要设置trace文件大小：max_dump_file_size参数

好了现在可以使用刚刚学习的dbms_system包中几个过程进行设置了。参见：http://blog.csdn.net/xiaohai20102010/article/details/12027793

eg：

SQL> begin                                               
  2  dbms_system.set_int_param_in_session(
  3  sid=>41,
  4  serial#=>207,
  5  parnam=>'max_dump_file_size',
  6  intval=>20971520);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

SQL> get p3.sql
  1  begin
  2  dbms_system.set_bool_param_in_session(
  3  sid=>41,
  4  serial#=>207,
  5  parnam=>'timed_statistics',
  6  bval=>true);
  7* end;
SQL> r
  1  begin
  2  dbms_system.set_bool_param_in_session(
  3  sid=>41,
  4  serial#=>207,
  5  parnam=>'timed_statistics',
  6  bval=>true);
  7* end;

PL/SQL procedure successfully completed.

SQL>

这次设置完了相关参数。

3、开启会话trace功能

eg：

 

SQL> execute dbms_system.set_sql_trace_in_session(41,207,true);

PL/SQL procedure successfully completed.

SQL>

4、等待一段时间收集语句：

SQL> conn cott/root@rhys
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott@rhys
 
SQL> select * from v$mystat where rownum<3;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
        41          0          0
        41          1         18
 
SQL> select * from rhys.amy_dept;
 
select * from rhys.amy_dept
 
ORA-00942: ？？？？？？？
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select * from emp where rownum<5;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 
SQL>

5、关闭session跟踪：

eg：

SQL> exec dbms_system.set_sql_trace_in_session(41,207,false);

PL/SQL procedure successfully completed.

SQL>

6、收集该session语句trace文件：

[oracle@oracle-one trace]$ ls -ltr *3191.trc
-rw-r-----. 1 oracle oinstall 42278 Sep 25 21:52 RHYS_ora_3191.trc
[oracle@oracle-one trace]$

好了。至此，就可以找到会话的所有sql语句了

查看trace文件：

为了查看方便，我们使用tkprof工具进行查看，这样更加易懂。详见我的blog中《oracle tkprof工具使用详解》