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

标签: oracle 客户端 sql | 发表时间:2013-09-26 06:08 | 作者:xiaohai20102010
出处:http://blog.csdn.net

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          [email protected]                                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/[email protected]
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as [email protected]
 
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文件:

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

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

查看trace文件:

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

 

作者:xiaohai20102010 发表于2013-9-25 22:08:08 原文链接
阅读:84 评论:0 查看评论

相关 [oracle 客户端 sql] 推荐:

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

- - CSDN博客数据库推荐文章
oracle如何获得客户端sql执行计划以便优化sql.    今天是2013-09-25日,继续学习sql优化这一部分,在之前写过怎么获得sql的执行计划两篇笔记. 虽然笔记有点粗糙,但是如果耐心看,还是发现点东西的.   也就是刚刚学习的一个系统包dbms_system中几个procedure的使用:.

不安装Oracle客户端情况下用PL/SQL连接远程服务器

- - 数据库 - ITeye博客
首先下载免安装的客户端instantclient_12_1,. 解压缩 instantclient_12_1 到 D:\Oracle\instantclient_12_1(解压到任意目录即可). 在文件夹内建立目录, /NETWORK/ADMIN. 在该目录下,新建文件tnsnames.ora. 在thsnames.ora中写入:.

oracle sql 优化大全

- - Oracle - 数据库 - ITeye博客
最近遇到了oracle sql优化的问题,找了一下,发现这文章实在不错,跟大家分享一下,如果以后有什么新的改进也会继续补充的. 1     前言… 2 . 2     总纲… 2 . 3     降龙十八掌… 3 . 第一掌 避免对列的操作… 3 . 第二掌 避免不必要的类型转换… 4 . 第三掌 增加查询的范围限制… 4 .

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

ORACLE SQL Performance Analyzer的使用

- - CSDN博客数据库推荐文章
通过 SPA,您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的. SQL 或整个 SQL 负载,然后生成比较报告,帮助您评估它们的影响.. 在 Oracle Database 11g 之前的版本中,我必须捕获所有 SQL 语句,通过跟踪运行这些语句,. 然后得到执行计划 — 这是一项极其耗时又极易出错的任务.

查询Oracle 耗资源sql

- - 非技术 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

oracle各类型SQL的操作流程

- - 数据库 - ITeye博客
•Select * from test  where  object_id=200在体系中是如何运转的. 在PGA中把此条SQL语句hash成一个值;. 接下来根据此hash值到SGA的共享池中去匹配,如果没有,首先查询自己的语句语法是否正确,语义是否正确,是否有权限. 如果都通过则通过CBO解析生成执行计划(如走索引还是全表).

Oracle PL/SQL 编程基础 实例 2

- - CSDN博客数据库推荐文章
if  循环  控制语句 . --编写一个过程,可以 输入一个雇员名,如果该雇员的工资低于2000就给他增加10%.           --判断. --======####案例s33 编写一个过程,可以 输入一个雇员名,如果该雇员的补助不是0就在原基础上增加100,如果是0就加200.           --判断.

Oracle PL/SQL 编程基础 实例

- - CSDN博客数据库推荐文章
1, exec 过程名 (参数,.  call 过程名 (参数  ).   dbms_output.put_line('姓名:'||v_ename);.                           --执行部分.  --------------函数 -------.    -------包------------由包规范和包体组成的.