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工具使用詳解》